When moving from an ERP system to another ERP system such as Microsoft Dynamics NAV, a lot of companies want to import their historical data into the new ERP. For example, companies may want to import all inventory entries made for the previous year for statistical purposes; or, if they start working with Microsoft Dynamics NAV in the middle of a fiscal year, they may want to import all G/L entries made in the old system for the current fiscal year.
In Microsoft Dynamics NAV, this kind of data is stored in ledger entry tables. If you have to conduct a migration of such data, never import it directly into ledger entry tables. Use journals instead, and post the data. That way, Microsoft Dynamics NAV will create the ledger entries for you in a consistent way.
For item ledger entries, for instance, not only is the item ledger entry created, but the value, item register, item application entries and other entries are created as well. If a journal is used, all those entries will consistently be created for us and we won't have to worry about anything.
Several journals exist in Microsoft Dynamics NAV. Choose the right journal for the ledger entries that have to be imported. If item ledger entries have to be imported, use the item journal. If G/L entries have to be imported, use the general journal. Some journals use the same underlying table but have specific values in some fields or use specific fields. General Journals and Recurring Journals use the same Gen. Journal Line table, and item journals and revaluation journals use the same Item Journal Line table.
If you have to import data into those tables, make sure the right fields are being filled and that the right options are used.
A good idea would be to create some journal lines manually, through the interface provided by Microsoft Dynamics NAV, and compare those lines with the ones created through an import process. That way, we will know whether we are missing something in our import process code and will be able to correct it. Let's see all of this in a step-by-step example.
Item number 70061 has previously been created in the master data migration process. The item will start to be used in Microsoft Dynamics NAV on 01/01/2013. We want to import all inventory movements done for this item in 2012, which are:
|
Date |
Type |
Quantity |
Unit of Measure |
Unit Cost |
Location |
|---|---|---|---|---|---|
|
05/01/2012 |
Sale |
20 |
PCS |
BLUE | |
|
01/02/2012 |
Purchase |
1 |
BOX |
40 |
BLUE |
|
23/04/2012 |
Sale |
10 |
PCS |
BLUE | |
|
13/06/2012 |
Sale |
5 |
PCS |
BLUE | |
|
07/09/2012 |
Sale |
15 |
PCS |
BLUE |
We will use the item journal:
- Using RapidStart Services, create a package including table 83, Item Journal Line, and all the fields shown in the following screenshot. Change the Processing Order column for the Unit Cost field so that it is the last one to be processed.
We need to change the Processing Order column because, after the Location Code field is entered, the Unit Cost field resets to 0. Why is that happening? Well, Dynamics NAV acts like this in many places. As item unit costs are maintained at location level, when the location is entered, the unit amount is updated. In this case, it resets to
0because this is a new item that (still) has no associated costs.
- Use the Export to Excel option to create the Excel template.
- Fill in the template as shown in the following screenshot:
- Import the Excel template and apply it.
- Open the item journal and post it.
Now let's check the Item card for item 70061 and create the Item Ledger Entries.
It looks relatively good so far, but we are still missing something. The entries have been correctly posted, but the Item Card shows an inventory of -10.
What we have actually missed is creating a first entry for the initial inventory. Do you remember which scenario that was? We wanted to start working with Dynamics NAV on 01/01/2013 and also wanted to import all movements done for the item in 2012.
We should have imported the initial stock on 31/12/2011 and then the movements for year 2012. With all this, we will not have to conduct any extra import to get item open entries as open entries for the item will actually be the result of posting the initial stock on 31/12/2011 and all the movements done in 2012.
Note
All of this does also apply to any other kind of historical data you may want to import. Import the initial value one day before the beginning of the period for which you are importing historical data. You don't have to import data about open entries now as open entries will already be in the system as the result of the previous actions.