Recently our customers feed back us a pb when they searching on "Completely Shipped" in Sales order list page, is very slowly , nearly 30 Seconds. I checked the count of records just have 138701。How to fixed it ?
This field is a flowfield , which formula :
Min("Sales Line"."Completely Shipped" WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(No.),Type=FILTER(<>' '),Location Code=FIELD(Location Filter)))
I add a key "Document Type, Document No., Type,Location code " into sales line , seems not work.
And I tried add a SIFT, "Completely Shipped" based on "Document Type, Document No., Type,Location code " , not work neither.
Any suggestion will be warmly appreciated.
BTW, My Version is Navision 2016.
This kind of filter will always be rather slow, as it needs to lookup the Sales Line for each Sales Header, but that doesn't mean it can't be optimized.
SIFT are not used for MIN flowfieds, only for SUM flowfields, so you should not consider that here. Instead you could try with this key: "Document Type, Document No., Completely Shipped, Type,Location Code
If that doesn't improve the performance enough, then you could experiment with a new flowfield with a different calcformula using EXIST instead of MIN. This one almost gives the same result, unless the document is without any lines, but if you only look at Released sales documents, then I doubt anyone will notice:
-Exist("Sales Line" WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(No.),Type=FILTER(<>' '),Location Code=FIELD(Location Filter),Completely Shipped=CONST(No)))
(please note the "-" in front to change the "sign" of the result)
If the customer is still not happy, I guess you will have to make a non-flowfield and make sure it gets updated whenever the salesline gets modified. You will have to do it using either events or OnDatabaseModify in Codeunit 1. I'l go the latter, as events are very buggy in NAV2016.
My two final suggestion would be re-writing the list to use a query instead or disable SmartSQL on the service tier, but they both bring up a lot of new issues.
Please let me know the results if you try any of my suggestions.
Hi PDJ, Thanks for your reply, i just tried , if i search on this new field, 6754 records spent 50secs, i think the customer can not accept this, if use the real field save its value, the history records can not use this filter.
So, i think i add some filters more on this issue, the customer just need all not completely shipped data, i just need search on this point for them, I write the codes on the open page triiger, and just filter out the order No. which completely shipped = no.