Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Relation between purch...
Microsoft Dynamics AX (Archived)

Relation between purchline,VENDINVOICETRANS VENDPACKINGSLIPTRANS

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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

  • Muddassirahmed035 Profile Picture
    Muddassirahmed035 5 on at
    RE: Relation between purchline,VENDINVOICETRANS VENDPACKINGSLIPTRANS

    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!

  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: Relation between purchline,VENDINVOICETRANS VENDPACKINGSLIPTRANS

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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Relation between purchline,VENDINVOICETRANS VENDPACKINGSLIPTRANS

    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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Relation between purchline,VENDINVOICETRANS VENDPACKINGSLIPTRANS

    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.

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,735 Super User 2024 Season 2 on at
    RE: Relation between purchline,VENDINVOICETRANS VENDPACKINGSLIPTRANS

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,735 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,466 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans