web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / CUSTPACKINGSLIPTRANS l...
Microsoft Dynamics AX (Archived)

CUSTPACKINGSLIPTRANS link with CUSTINVOICETRANS

(0) ShareShare
ReportReport
Posted on by CarolSanguinetti 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

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    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.

  • CarolSanguinetti Profile Picture
    CarolSanguinetti 220 on at

    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.

  • CarolSanguinetti Profile Picture
    CarolSanguinetti 220 on at

    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.

  • cujamison Profile Picture
    cujamison 5 on at

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

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    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.

  • Suggested answer
    Abdul Wahab Profile Picture
    Abdul Wahab 12,119 Moderator on at

    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

  • makdax2012r3 Profile Picture
    makdax2012r3 50 on at

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

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans