Data Migration from NAV to Business Central

Data migration involves preserving data between different versions of NAV / Business Central. It is the simplest part of a migration process since the steps are predefined and just need to be followed.

Although it’s a straightforward process, there are some strategies that can save us time depending on the task at hand. In this post, I will explain two approaches for migration to extensions. The first one is the method currently offered by Microsoft for migrating data to the new versions of Business Central, and the second is the one that was used before knowing or having the first method as a necessity when passing through multiple versions.

First Method of Data Migration to Extensions in Business Central

Microsoft provides us with a straightforward way to migrate data from C/AL to AL, i.e., from modified standard tables to TableExtensions and from 50k tables to new 50k tables in extensions.

The Microsoft guide provides a step-by-step process of what needs to be done. Nonetheless, I will explain the basics of the process schematically to facilitate understanding and clarify the guide’s steps.

The main idea is as follows:

La idea principal es:

• Start with everything as it is in BC14 C/AL.
• Create an extension for the data, meaning only tables and their fields, without any code, and migrate the data from C/AL to the tables of that extension, keeping them as they are in C/AL. (Task 5 in Microsoft)
• Create three empty extensions to be able to later run the upgrade code to BC22. (2 by Microsoft and then as many as there will be custom extensions).(Task 4 in Microsoft)
• Create an extension with the same appId and name as the tables from step 2, and there, specify that the data from that extension needs to be migrated to the v22 extensions (which will be Microsoft’s standard app and any custom extensions). (Task 5 second part in Microsoft)
• Use the final extensions along with Microsoft’s own extensions for the BC22 version (Task 13 onwards in Microsoft)

To obtain the extension from step 2, an extension with all the tables as they are in BC14 but in AL, follow the steps for Extension Migration (Code), Dynamics NAV to Business Central, specifically:

Development Shell (BC14):

Export-NAVApplicationObject -DatabaseServer .\BCDEMO -DatabaseName "BC14" -ExportToNewSyntax -Path "C:\export2al\bc14tablesonly\exportedbc14-tables.txt" -Filter 'Type=Table;Id=1..1999999999' -ExportTxtSkipUnlicensed 

CMD:

Cd "C:\Program Files (x86)\Microsoft Dynamics 365 Business Central\140\RoleTailored Client"

txt2al --source="C:\export2al\bc14tablesonly" --target="C:\export2al\bc14tablesonly\al" --tableDataOnly

With this, you will get all the tables in AL, and now you can create the extension from step 2. The important thing is that you must import the “System.app” file from the path “C:\Program Files (x86)\Microsoft Dynamics 365 Business Central\220\AL Development Environment” into the “.alpackages” folder:

If this is done with a Premium license, there won’t be any problem. However, if it’s done with an Essentials license, there will be errors in all those fields related to tables that have not been exported because they were outside the license. To address this, you can use a regex search to remove many entries like:

Buscar y eliminar por:
TableRelation = IF\s*\(.*?\)[^;]*;
 TableRelation = .*?;     (Tiene espacio al inicio)
ValidateTableRelation = false;
ValidateTableRelation = true;
AccessByPermission = TableData .*?;
field\(\d+;[^}]*FieldClass = FlowField;[^}]*\}   (Hay que darle un salto de carro al final)

Once this is done, the few remaining errors can be handled manually, and all TableRelations, AccessByPermission, and calculated fields will have been removed, and the extension will be ready.

The following is an image of the empty extension of Microsoft’s base application from step 3:

Next, the extension from step 4, where the first two “id” are Microsoft’s, and the third would be the extension with customizations:

In this last app, it is specified to which extensions the data from the extension with only tables created in the first step should be transferred.

Second Method of Data Migration to Extensions in Business Central

I have used this method before knowing about the existence of the first method (or before it existed) and after knowing about the first method to, in my opinion, reduce potential errors and shorten data migration times. The suitability of using this method is based on the following:

• There are as many free license objects as new tables and standard tables with new fields in the database. (Or there is a partner license, which has no limitation on object usage).
• Passing through many versions of NAV before reaching BC, which would require manually creating fields in standard tables for each version.

Given a migration with the following steps:

Creating new fields in tables for each version can be time-consuming and prone to human errors, making it challenging to identify the problem afterwards.

With this method, the approach is to transfer everything to new tables, meaning duplicating the 50k tables and creating new tables with the primary key of the modified standard table and its new fields. In the previous example, it would mean having a 70,000 table identical to the 50,000 and a 70,001 table with the fields “Document Type,” “Document No.,” and the new field.

Now, the challenging task of transferring data to the 70k tables arises. For this transfer, I have used a program, NimbleText. This program allows repeating a text structure based on variables. This way, once the first data transfer function is done, you can use that structure for the rest of the tables as follows:

1º Perform a process with the first data transfer function and export it to txt:

Copy the function in NimbleText and note the structure of the internal numbering that must be assigned manually in .txt and other issues:

This way, $0 will be replaced by the first element above, and $6, for example, will be replaced by the last one. Once the program’s result is generated, you get a string that you can copy and paste into the .txt file, and thus all the data transfer functions to the 70k tables will be completed:

Once this is done, the migration between versions will be like that of a standard version because all objects will be standard except the 70K tables.

At the point of BC14, a process must be carried out to return the data from the 70K tables to the new tables and TableExtensions. For this, the extensions with the corresponding table objects and TableExtensions must have been created, and another process should be prepared to transfer the information. For this process, the NimbleText program will also be used.

In this case, there is a slightly more laborious part since, although with the 50k tables, a simple TransferFields and Insert can be done, with the standard tables, a Get operation will be required, and that Get depends on the primary key. Nonetheless, the procedure is the same:

Now all the information is structured according to the final table structure, and the database conversions between versions can be performed, and the various Microsoft APPs can be installed to complete the migration.