There was a question on Twitter that I wanted to answer in a lot more than a Tweet or few.
#BCALHelp #msdyn365bc How would you solve this issue…
I understand it is NOT possible to do calculation within a CalcField other than Sum on a column but I need to know the Qty Available and I would like to keep it current. I have scheduled this function to run every 10 min
“What is available for this item?” is a simple question.
Dangerously simple! Why? Because the definition of “Available” varies significantly. Locations, variants, units of measure, variants, reservations, bins, serials, lots (with or without expiration dates)… When you try to delve into the why is something “available”, you’ll find there’s a decision tree that is a nightmare to design. This is why even back to 2.6 (shown), the Item Card only shows you the basis for what is available:
(Sorry for that only being Swedish, it’s what I had available at the moment)
Even to BC 17, you still only get the values that you might want to judge availability:
So, this leads to the natural request we have to answer all the time: I want to filter my item list on what’s available.
Oof.
I can’t tell you how to answer that case for all the many different scenarios. You have to delve into every possible meaning and scenario the client imagines impacts the magical word “available”.
But, I can tell you how I answered that question for one client. This client had a simple available math:
Available = Inventory/Stock + Qty. on Purch Order – Qty. on Sales Order – Qty. on Sales Quote
For them, that was a straightforward formula, and easy to implement. In early versions of their solution, that was on the Item List and Item Card as that formula. But – filtering on a formula result was not great, if possible. There are versions where you cannot. Plus, this was a formula against four Sum FlowFields, which has huge performance impact. Especially if the *default* for their Item List needed to be to filter on Availability. So filtering on an intensely expensive, non-keyed field – a recipe for disaster – especially against tens of thousands of items.
Skipping ahead – what they have now is an extension driven solution where we store and maintain the “available” value. This is used non-stop by the users, it’s used by integrations, it’s used by web shop, etc. It needed to be flat, fast, and filterable.
Item Queue Table
To prevent performance degradation using items and to minimize locking, a new table was created, a simple Item Queue table.
Over time, we mapped all business cases that would affect the calculation – such as adding an item to a sales order, etc. (more below)
This table is pretty simple, data wise:
PK is “No.”
It also has a Trigger and a Function:
When Items are inserted into the table, the absolute soonest I would want the availability calculation was 5 seconds out. This ensured that the write transaction triggering this event was most likely over with. It’s a fuzzy number, but let me fiddle with a delay. Not optimal, but sometimes what works isn’t optimal.
Item Update Handler Codeunit
Due to OnPrem licensing, this codeunit is one of the old school Management Codeunits, with many methods, but still, the parts would be easy to break into the discrete parts.
Inserting to the Update Queue
Note: This function mentions a couple debug fields I didn’t include above you don’t need, but can choose to add if you want (Source Event / Source User).
This tiny function just does a quick check to see if an Item is already in the Queue1. If it is not, it’s inserted. If it is, the delay of when to calculate is moved further into the future.
This is called from a long list of events we identified as ‘key moments’:
This client had about 12 events that all functionally do the same as above.
The Job Queue Aspect
This all gets the Item Queue system populated and maintained. To actually do the work, there’s a Job Queue Entry that calls this codeunit:
This next bit, the Process, is a little odd, but it’s served us well:
- It takes the Item Queue entries that are scheduled to run up to now, pops them into a temp table for reference (so users can still change the table while the first parts are running)
- Locking the Item table for only the time it takes to run the Calculate
- It Calculates and modifies the item2.
- It commits to release the lock on the Item table as early as possible
- Then the Item Queues that have been processed are removed from the Item Queue table
- It commits to release the lock on the Item Queue table (as it will do a clean up step next)
The cleanup job is just to make sure our Job Queue Entry Log table doesn’t get overwhelmed:
This just purges any Success entries over a week old.
The CalculateAvailable function is honestly the smallest part of the job, just a quick bit of CALCFIELDS and math, per above.
The Job Queue Entry has been so performance/locking safe for them, we’ve got this job set to run every 5 minutes with no issue. They are aware of this time and found this to be an acceptable ‘window’ that the filtering might be approximate within.
For this client, they now at least have a flat decimal field as part of their table (with performance TableExtension field caveats, of course). But we could also extend info easily, as we later did, such as adding fields (a DateTime when an item became unavailable to hide it on the web site, for example).
Is this best practice? No, gosh no. Did we put best effort to make sure it was good enough for this particular client? Yes.
Hopefully this helps!
Footnotes:
- Because we’re modifying the result of this check, a GET is fine rather than a straight ISEMPTY check, as it’s a single heavier call vs a lighter, then second call.
- One of the triggers that puts an Item into the Queue is Item.MODIFY calls, so we do a non-triggering MODIFY call here to stay out of the Sea of Recursion