March 14, 2018
Compress Item Ledger Entries in Dynamics NAV
There are different ways to reduce the size and speed of a Dynamics NAV upgrade. This blog will cover one component, compressing item ledger entries that may prove useful. However, it is important to note that with any major change such as this, it requires thorough testing of the solution and mapping out a blue print for the plan. To begin, it is important ask yourself:
- Is this part of an upgrade plan to a new version of Dynamics NAV, or are is the client trying to do this in an existing database?
- Is the client planning to try to keep some of the historical data in the system for historical purposes for reporting and analysis? Alternatively, are they planning to clean the slate and go back to the historical data in a copy of the historical database prior to the process being run if any historical analysis is required?
Summary of Compressing Item Ledger Entries through an Upgrade ScenarioThe following is a summary of what I have discussed with partners who have done the process through an upgrade scenario. In particular, this was completed often when moving from 2.X to 3.X Versions due to the significant change in data structure, which was a result of the introduction of the Value Entries in Version 3.X and prior. As a result, the process below requires modifications depending upon what data exists – i.e. Manufacturing, Assembly (2013 and later), Kit Data (2009 and earlier), open Orders – Sales/Purchase/Production/Assembly, Warehouse Entries, Location Setup, etc.
In the DatabaseMake sure all SO’s and PO’s are invoiced (no received/shipped not invoiced) and run the Adjust Cost Item Entries and Post Inventory Cost to G/L. This may entail having to use the Undo Receipt Shipment or Undo Receipt if the orders cannot be invoiced. This is key so that you do not delete Item Ledger Data that has dependencies on existing outstanding order status as well as any unprocessed adjustments for quantity or value. NOTE: Further challenges come into play with Manufacturing Production Orders. The best practice is not to have partially processed transactions prior to deleting Item data, which can get to be quite challenging.
- Run Inventory calculation to find out what is on hand and export this to a file. (E.g., Dataport or XML Port would be required to export Quantity by Location/Variant/Lot No. /Serial No.)
- Make a backup of the base ILE object and Value entry Objects.
- Renumber the ILE and Value Entry Table to a table in the 50000 range with a new name if history is going to be retained in the database for reporting.
- Import the ILE and VE object again. Do this because there are usually too many records in the ILE to start copying them to another table. This is fast and easy. (It is also easier to write Reports and Forms of these Tables with pre-upgrade data. As a result, the upgrade will take hours instead of days.)
- Delete the Item Ledger (T-32), Item Register (T-46), Item Application Entries (T-339), Value Entries (T-5802), Average Cost Adjustment Entry Point (T-5804), Post Value Entry to G/L (T-5811 – should be cleared with Post cost in step 1), G/L - Item Ledger Relation (Table-5823 includes link of T-5802 to T-17- G/L Entries. Other possible Tables include Inventory Adjmt. Entry (Order) (T-5896) if NAV 203 or later, Reservations Entries (T-337), Capacity Ledger Entries (T-5832), Warehouse Entries (T7312).
- Complete the upgrade without Inventory, then import the qty. on hand (with serial and lot) and post it. This also makes a great opportunity to cleanup items not used anymore.
- Run a Dataport and Import into an Item Journal after the upgrade together with the Quantity on Hand with Unit Cost ($) desired)
- Login Error: Communication protocol mismatch between client and server
- Creating a Date Table in Power BI
- How to Make Measures Total Correctly in Power BI Tables
- The Microsoft Technology Stack – What It Is and Why You Should Care
- The Top Eight KPIs Retailers Should Be Tracking (with Formulas) for Your Retail KPI Dashboard