Microsoft Dynamics AX (Archived)

Sales Orders with Delivery and Invoice Dates repeats when partial deliveries on a report?

(0) Share

Report

Posted on by Rana Anees

1,813

Hii,

I have an AOT query that shows Sales Id, Delivery Date, Invoice Date.

If SO has one Packing Slip and Invoice, then report shows correct Sales Id, Delivery Date and Invoice Date.

If SO have partial deliveries and Invoices, in this case this report don't show Delivery Dates and Invoice Dates respectively.

I am trying to show on this report all Sales Orders either, Open, Delivered, Invoiced. Its a header level report.

My AOT Query have joins with SalesTable, CustPackingSlipJour and CustInvoiceJour tables.

I created a display method on SalesTable to get Delivery Date from CustPackingSlipJour but it also returns the first date in case of Partial Deliveries.

Thanks for the help.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: Sales Orders with Delivery and Invoice Dates repeats when partial deliveries on a report?

Could you illustrate with some sample case what is the actual vs expected outcome?

You say you want to create a "header level report". What does it mean in case there are multiple packing slips or invoices? Do you want to have on report line for each SO, packing slip and invoice? Or just one line for each SO?

Could you share screenshots of the query and your display method?

Was this reply helpful? Yes No
Rana Anees 1,813 on at

Like (0)

Report

RE: Sales Orders with Delivery and Invoice Dates repeats when partial deliveries on a report?

Here is the complete detail.

AOT Query:

Form

This is open SO, having no delivery and Invoice

This is Invoiced SO, having one delivery and one invoice. Its showing Ok.

This is also Invoiced SO. This SO have multiple/partial deliveries and invoice against each delivery.

There are three deliveries and three invoices. There should be three rows only, but there are repeating and showing 9 rows. That's the problem I am struggling to solve.

In case of partial deliveries and invoices, how to stop repeating row like above. Means it should show Ship/Delivery date and its Invoice date and should not repeat.

Thanks

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: Sales Orders with Delivery and Invoice Dates repeats when partial deliveries on a report?

If you change the FetchMode to 1:n and JoinMode to InnerJoin, your query will return one record for each record in the child data sources.

But even then you will get 6 records since there are 3 packing slips and 3 invoices.

Also there is no guarantee that there is an equal number of packing slips and invoices. You might have 6 packing slips and 3 invoices. So how would you want your query to work in such case? You should somehow be able to determine which invoices were related to which packing slip. Then you could put the CustInvoiceJour as a child data source for the CustPackingSlipJour data source. This could be achieved by adding a custom field for PackingSlipId in the CustInvoiceJour table, and populating it when posting the invoice. This would work only for some invoicing scenarios (that's why such field doesn't exist in the standard solution), but maybe in your case it would be sufficient.

Was this reply helpful? Yes No
Rana Anees 1,813 on at

Like (0)

Report

RE: Sales Orders with Delivery and Invoice Dates repeats when partial deliveries on a report?

Hii Nikolaos Mäenpää,

a) I changed the FetchMode to 1:n and JoinMode to InnerJoin. In this case it is not showing Open Sales Orders. Its ok.

b) I put the CustInvoiceJour under CustPackingSlipJour data source and Relation based on CustPackingSlipJour.SalesId = CustInvoiceJour.SalesId but result is the same. What else field to add in this Relation?

c) If there are 6 packing slips and 3 invoices, in this case there should be related invoice date if any.

d) I am wonder how the world is dealing with this scenario.

e) If above not work, then how to fetch records based on Last Delivery Date and Invoice Date in the same AOT Query?

Thanks,

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: Sales Orders with Delivery and Invoice Dates repeats when partial deliveries on a report?

b) That won't work. Like I wrote: "You should somehow be able to determine which invoices were related to which packing slip. Then you could put the CustInvoiceJour as a child data source for the CustPackingSlipJour data source. This could be achieved by adding a custom field for PackingSlipId in the CustInvoiceJour table, and populating it when posting the invoice. This would work only for some invoicing scenarios (that's why such field doesn't exist in the standard solution), but maybe in your case it would be sufficient."

d) Most likely they have a bit different solution than what you are trying to implement. What is your business requirement? I think your problem is that you are trying to add data from many tables with 1-n relations to one simple grid. It's not going to work. Usually you would have only 1:1 related data sources on one grid.

Possible solutions:

1) Use display fields / display methods to show the (latest) packing slip and invoice information for the sales order. Then your query would include only SalesTable. Or SalesTable and CustPackingSlipJour if you wish (then you would add only the invoice dates with display fields. Assuming 1:1 relationship between packing slip and invoice). The downside is that you can't sort or filter by display fields.

2) Make your form a bit more fitting to your data model. Have 1 grid for sales orders (on the left), where user can select a sales order. Then show the related packing slips and invoices in two separate grids on the right.

3) Use your current form layout but instead of this query, create a temporary table where you can put any number of records and dates that you want. Of course this will be too slow if you fetch tens of thousands of records, so you should put only such orders there that you need (such as orders from last 30 days or something).

4) Create a report, instead of a form. Show all packing slips and all invoices as "line data" under each sales order ("header").

Was this reply helpful? Yes No
Rana Anees 1,813 on at

Like (0)

Report

RE: Sales Orders with Delivery and Invoice Dates repeats when partial deliveries on a report?

Thanks Nikolaos Mäenpää,

All are good solutions you provided. I will try all.

Thanks,

Was this reply helpful? Yes No
Rana Anees 1,813 on at

Like (0)

Report

RE: Sales Orders with Delivery and Invoice Dates repeats when partial deliveries on a report?

Hii,

Following your first solution..

"1) Use display fields / display methods to show the (latest) packing slip and invoice information for the sales order. Then your query would include only SalesTable. Or SalesTable and CustPackingSlipJour if you wish (then you would add only the invoice dates with display fields. Assuming 1:1 relationship between packing slip and invoice). The downside is that you can't sort or filter by display fields."

Now I have only SalesTable and CustPackingSlipJour in my Query. I am trying to write a display method in CustPackingSlipJour table to get InvoiceDate from CustInvoiceJour table. I join it by SalesId but need to add some more fields to make it unique in case of multiple invoice records. What other field to add to join both tables?

Thanks,

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: Sales Orders with Delivery and Invoice Dates repeats when partial deliveries on a report?

Check if you have any value in your CustInvoiceTrans.InventTransId. If you have, there's your link to a sales line. Packing slip lines are also linked to sales line.

But invoice header is not linked to a packing slip header. If you need such link, and you know that in your process they always correspond to each other, please consider my previous suggestion of adding a custom field for PackingSlipId in the CustInvoiceJour table.

Was this reply helpful? Yes No