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 / Blogs / Jesús Almaraz blog / Keeping invoiced orders wit...

Keeping invoiced orders without delete them. Not the best choice

Views (221)
Jalmaraz Profile Picture Jalmaraz 669

Disclaimer: This post should need further revision, performance questions are complex, I should need feedback if you are the same issues. In the other hand if you read this post carefully you will notice I don´t blame Microsoft, they did their work.

More and more customers ask for keep invoiced orders without delete them. They want to have a sales order historical. And more and more times we answer “Ok. Not problem” as easy as avoid “Delete Invoiced Orders” process execution and avoid invoice posting from order.
And at the beginning of the database life all is ok. But some years and NV/BC releases later we noticed application opening not is ok. Specially when you come to Business Central 140. The app entrance is overcharged in Role center “Sales Processor” and related cues. This performance problem in the initial page, causes slow performance in the rest of the app, because takes a lot of machine resources.

Performance “gem”.

The source of the problems, IMO, is that NAV/BC not ready to manage a lot of rows in “Sales Header” and “Sales Line” tables. Some hints told us that NAV/BC design is made for keep only “live” orders. The main hints is the performance “gem” we can find in of “Sales Header” flowfield table:
        field(5752; "Completely Shipped"; Boolean)
        {
            CalcFormula = Min("Sales Line"."Completely Shipped" WHERE("Document Type" = FIELD("Document Type"),
Max, Min, Average, the evils of SQL performance specialists. This field is used in cue table “Sales Cue” and some page views when initial “Sales Processor” page is open.
        field(8; "Partially Shipped"; Integer)
        {
            CalcFormula = Count("Sales Header" WHERE("Document Type" = FILTER(Order),
                                                      Status = FILTER(Released),
                                                      Shipped = FILTER(true),
                                                      "Completely Shipped" = FILTER(false),
                                                      "Shipment Date" = FIELD("Date Filter2"),
                                                      "Responsibility Center" = FIELD("Responsibility Center Filter")));
This field of “Sales Cue” table uses a previous flowfield with “Min” in “Sales Header” table as filter!!
                                                      "Completely Shipped" = FILTER(false),
But let´s be fair: Here we are several works to improve performance, in C/AL 140 was far worse, in 140 release we had a lot of performance “gems” that now are removed, and it looks like dev team are fixing lots of problems.
Bad design? No, I´d rather say nobody tells us order was also made as historical table information. All suggest the opposite, a batch work to delete them and deletion when we post invoicing from order. The message is “Delete invoiced orders”.

Sales order historical approach.

We already have the order information in Posted Shipments and related Invoices, but not the same. To get all the information of original sales order was, we have to do a lot of merges and derivations from posted documents info. We can get a sales order historical with other approach. Instead keeping the orders, save then in  archive tables (“Sales Header Archive” and “Sales Line Archive”) before delete them, and mark the last archive version with a Boolean field to get a view of order historical, filtering only these last versions.

Comments

*This post is locked for comments