Answered

Relation between purchline,VENDINVOICETRANS VENDPACKINGSLIPTRANS

(0) Share

Report

Posted on by Community Member

I am not able to determine the relationship between these tables. I am getting duplicates. please help

if i can join VENDINVOICETRANS and VENDPACKINGSLIPTRANS . That would be great as well.

FROM dbo.PURCHLINE pl
LEFT OUTER JOIN dbo.VENDINVOICETRANS vt
on pl.PURCHID = vt.ORIGPURCHID
AND pl.ITEMID = vt.ITEMID
AND pl.INVENTDIMID = vt.INVENTDIMID
LEFT OUTER JOIN dbo.VENDPACKINGSLIPTRANS vp
AND pl.PURCHID = vp.ORIGPURCHID
AND pl.ITEMID = vp.ITEMID
AND pl.INVENTDIMID = vp.INVENTDIMID

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

André Arnaud de Cal... 301,030 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

The best link would be the field InventTransId. This is unique per line combination. It is possible that you have one or more packing slip lines or invoice lines for a single purchase order line as it is possible to work with partial deliveries and invoicing.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

thanks for your reply. DO you think I can join VENDINVOICETRANS and VENDPACKINGSLIPTRANS on quantities? because for each purchaseline and inventransid combination there are two records each in VENDINVOICETRANS and VENDPACKINGSLIPTRANS and i have no way of knowing which record in VENDINVOICETRANS should be joined to VENDPACKINGSLIPTRANS as the only think common i see is the qty. I appreciate your help. I am just getting started on AX.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

while select vendPackingSlipTrans

index hint TransIdIdx

join inventTrans

where vendPackingSlipTrans.InventTransId == vendInvoiceTrans.InventTransId &&

inventTrans.InventTransId == vendPackingSlipTrans.InventTransId &&

inventTrans.PackingSlipId == vendPackingSlipTrans.PackingSlipId &&

inventTrans.InvoiceId == vendInvoiceJour.InvoiceId

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

You likely want the table VENDINVOICEPACKINGSLIPQUANTITYMATCH, which is used by three way matching to link VENDINVOICETRANS to VENDPACKINGSLIPTRANS with respect to quantity.

Was this reply helpful? Yes No
Muddassirahmed035 5 on at

Like (0)

Report

Hi memphisguy,

VENDINVOICEPACKINGSLIPQUANTITYMATCH is the system defined table which contains the relation for VENDINVOICETRANS and VENDPACKINGSLIPTRANS and it works.

Please give your response if it helps you.

Thanks!

Was this reply helpful? Yes No