Did you ever wonder, why there is no real inventory table in NAV?
Of course, I know, I can still remember
We have the Item Ledger Entries and the Warehouse Entries, and we have the flowfilters and we can always find out everything.
In “heavily used” environments, after a few years, this will be slow. And if you start to play around with the availability calculation of items, you will sooner or later face the situation when it’s just not fast enough.
Our customer called the other day and told us, that they experience this kind of performance problems. It also important to mention, that we work a lot with mobile devices in warehouses, and the server part of the mobile application calculates the inventory on the flight (we are not using interfaces at all, we are integrating the NAV with the mobile warehousing application, and the integration involves SQL queries).
The solution is like this: we created a new table (Location Code, Bin Code, Item No., Variant Code, Serial No., Lot No., Quantity (Base)). This should include alway only one line per combination of all these attributes.
If once new item ledger entries of warehouse entries are created, this table will be maintained. In older versions of NAV, you have to know exactly how to change the posting codeunits for this to work. In the newer versions (from 2016) you have the chance to work with events.
This customer is on 2016 – so we created two events (this is, in my opinion, one of the best new things that could happen to NAV in the last few years). These events sign up for the creation of entries in the item ledger entry and the warehouse entry tables, and create/change lines in our new table (respectively adding new lines, or increasing/decreasing the quantities).
I know, we just reinvented the wheel. But still,
- the customer is happy as they received a solution that works inside NAV,
- we didn’t change the posting codeunits for this to work
- the queries of the inventory are extremely fast
- this opens the doors for some “quasi reservations” but more about it in a later post.
Let me know if you need actual downloadable sources – with the events, it’s just an “import and works” solution.
Hej, NAV! 