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
CustPackingSlipTrans\Relations\CustInvoiceTrans\CustPackingSlipTrans.InvoiceTransRefRecId == CustInvoiceTrans.RecId
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
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.
Try using the CustInvoicePackingSlipQuantityMatch table to relate packing slip to invoice.
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.
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.
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,902 Super User 2024 Season 2
Martin Dráb 229,336 Most Valuable Professional
nmaenpaa 101,156