Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

CUSTPACKINGSLIPTRANS link with CUSTINVOICETRANS

Posted on by 220

I need to create a report that shows me all the open orders, all the shipped orders and all the invoiced orders.  I am having difficulty when there are multiple shipments and multiple invoices.  In one case I have 4 packing slips for the same line and 3 invoices as two packing slips got invoiced together and I get 12 records.  I want 4 - one for each packing slip for the line item as I can hide the duplicate invoice information.

I am using select distinct so that is not the answer.

Any assistance would be appreciated.

*This post is locked for comments

  • makdax2012r3 Profile Picture
    makdax2012r3 50 on at
    RE: CUSTPACKINGSLIPTRANS link with CUSTINVOICETRANS

    CustPackingSlipTrans\Relations\CustInvoiceTrans\CustPackingSlipTrans.InvoiceTransRefRecId == CustInvoiceTrans.RecId

  • Suggested answer
    Abdul Wahab Profile Picture
    Abdul Wahab 12,070 Super User 2024 Season 1 on at
    RE: CUSTPACKINGSLIPTRANS link with CUSTINVOICETRANS in AX2012

    Hi CarolSanguinetti

    You select custInvoiceTrans and then write following query

    while select custPackingSlipTrans

               group by PackingSlipId

           //join salesLineLocal1

               where custPackingSlipTrans.InventTransId == _custInvoiceTrans.InventTransId//salesLineLocal1.InventTransId

    {}

    This will solve your problem

    Thank you

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: CUSTPACKINGSLIPTRANS link with CUSTINVOICETRANS in AX2012

    I believe that Nitesh is offering the correct answer, but it might not necessarily be spelled out in enough detail.

    You need two different datasets.

    When you are querying shipments you select:

    CustPackingSlipTrans

    inner join CustPackingSlipJour

    Most of the information you need is on those two tables, but if for some reason you need more joining the SalesLine to the trans or the SalesTable to the jour is simple.

    When you are querying the invoices you select:

    CustInvoiceTrans

    inner join CustInvoiceJour

    Again joining to the SalesLine or SalesTable if the situation requires.

    If you want only one record per document you should not use a single query:

    As you have already stated, the relationship between the packing slip and the invoice is not 'one-to-one' or even 'onto'. A packing slip can relate to many invoices or none and an invoice can relate to many packing slips. Trying to force them into the dataset requires that you either store records with sparse data or duplicate data just so that it can be fixed in your report design.

    You will actually save time and deliver a better dataset by writing a specific query for each of your use cases than you would by trying to make one query that can meet all of them.

  • cujamison Profile Picture
    cujamison 5 on at
    RE: CUSTPACKINGSLIPTRANS link with CUSTINVOICETRANS in AX2012

    Try using the CustInvoicePackingSlipQuantityMatch table to relate packing slip to invoice.

  • CarolSanguinetti Profile Picture
    CarolSanguinetti 220 on at
    RE: CUSTPACKINGSLIPTRANS link with CUSTINVOICETRANS in AX2012

    I received another response indicating that there is a hidden field on the CUSTPACKINGSLIPTRANS table InvoiceTransRefRecId which might link to the CustInvoiceTrans.RecID field.  Unfortunately it did not work either. Probably have to get someone to change the field property to not be hidden.

    I am at a loss as to how to link these two tables successfully without creating duplicate lines.

  • CarolSanguinetti Profile Picture
    CarolSanguinetti 220 on at
    RE: CUSTPACKINGSLIPTRANS link with CUSTINVOICETRANS in AX2012

    Thanks for your response,  That is exactly what I am combining to get 12 lines instead of 4.  

    The sales order has a single item with multiple shipments and invoices.  a quantity of 6 was ordered on a single line. A quantity of one shipped three separate times with 3 shipping as the final shipment.  Two of these shipments were combined on a single invoice.  Therefore 4 packing slip records and 3 invoice records.  12 records were returned instead of 4.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: CUSTPACKINGSLIPTRANS link with CUSTINVOICETRANS in AX2012

    For you report, you need to link SalesTable, SalesLine, CustPackingSlipTrans, and CustInvoiceTrans

    using SalesId and InventTransId field

    Please verify and update if this information helps you solve you requirement.

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,902 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,336 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans