Small and medium business | Business Central, N...

Sales Order List (Archived or not) Report filter by a date range

(0) Share

Report

Posted on by B@houston

Hi everyone,

Is it possible to generate a sales order list report filtered by a date range? I am asking because some sales order might become archived sales order, which is separate from the active sales order list.

How I can produce a report of Sales Order list, archived or not archived? Do I have to use the Power BI, or any idea how to create such report?

Thank you.

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (9)

Answers (0)

Guy-JM 95 on at

Like (1)

Report

RE: Sales Order List (Archived or not) Report filter by a date range

In order to show both in a single list, you will need to use a reporting tool such as power bi or jet reports.

It is simple enough to do but you need to consider version numbers when extracting the archive data.

I guess the bigger question is what are you trying to accomplish?

Was this reply helpful? Yes No
Suggested answer

JAngle 127 on at

Like (0)

Report

RE: Sales Order List (Archived or not) Report filter by a date range

Sales header has a flowfield called “No. of Archived Versions”. If you calcfields with this in a report or use a saved view you can look at orders in a certain date range which are and are not archived. Filter <>0 on the mentioned field for archived and 0 for those which are archived

Was this reply helpful? Yes No
B@houston on at

Like (0)

Report

RE: Sales Order List (Archived or not) Report filter by a date range

Hi Guy-JM,

I would like to create a SO list report, when I filtered the report by a date range, then I can view all the sales orders and their shipment status or invoiced status.

The difficulties are:

* some SO displayed on SO list but not on SO Archived list: is seems that open SO (not released) will not show on archived list, but this is not always correct if user manual archived the SO

* Invoiced SO are no longer display on the SO list if I am correct

* SO not show on SO list but on archived list should all be invoiced, is this correct?

* Overlap, SO that not fully shipped or invoiced, exist in both table, but with multiple versions SO archived list

Is it possible to consolidate such a SO list with correct invoice status or shipment status by extracting from SO list and SO Archived list? Thank you.

Was this reply helpful? Yes No
B@houston on at

Like (0)

Report

RE: Sales Order List (Archived or not) Report filter by a date range

Thanks Josh.

Refer to below screen print, I am not sure if this is always true. And still i am not sure how this filter will help to deliver the report requirements.

Regards,

Was this reply helpful? Yes No
Guy-JM 95 on at

Like (0)

Report

RE: Sales Order List (Archived or not) Report filter by a date range

Hi,

So, if I were doing this in Jet or Power Bi, I would be appending the Sales invoice and the Sales line table together.

Anything still open will still exist in the Sales Line table and any SO completely shipped and invoiced will be in the sales invoice line table and these can be cross referenced by sales order number.

You then have all the required data and can see exactly what has been invoiced, shipped or remains open if you do some filtering on the data.

You can also do this in excel using power query which avoids the license costs of the other two products.

You have much more scope and flexibility to build exactly what you wish to see in Jet, Power Bi or power query rather than BC itself.

Was this reply helpful? Yes No
Guy-JM 95 on at

Like (0)

Report

RE: Sales Order List (Archived or not) Report filter by a date range

To clarify further, only extract lines from the sales invoice line table where the sales order number does not appear in the sales order line table (field =document no.)

Note the the sales line table also needs to be filtered by document type = order.

Hopefully that makes sense to you, if not I can explain in more detail.

Was this reply helpful? Yes No
B@houston on at

Like (0)

Report

RE: Sales Order List (Archived or not) Report filter by a date range

Thanks Guy-JM for the clarification. I had a look at the Sale Invoice Line (113), Sales line (37) and Sales Header (36)I found out that that the sales invoice line and sales line, their date is very different from the SO document date.

If I need a simple report of all sales order entered in a date range, whether SO is delivered or not for example. are there any easier way to do this? Thanks for your help.

Was this reply helpful? Yes No
Guy-JM 95 on at

Like (0)

Report

RE: Sales Order List (Archived or not) Report filter by a date range

If you need to track the order entry date, you will need to use the archive.

Was this reply helpful? Yes No
MarisolC 103 on at

Like (0)

Report

RE: Sales Order List (Archived or not) Report filter by a date range

Did you find a way to solve this? Im at same point with purchase orders :S

Thanks in advance...

Was this reply helpful? Yes No