Helpful features
In this article, you will learn about helpful features of the Aident SQL Migration app in Business Central.
Fixed Name Conversion
For extensions in Business Central, it is necessary that they're provided with a prefix or a suffix. This leads to necessary adjustments to the table and field names. It's also possible that a table receives a new ID. These changes ensure that the automatic assignment of tables and fields doesn't work correctly, since these assignments are made based on the table ID (for table assignments) or the field name (for field assignments).
By setting up Fixed Name Conversions the automatic assignment can still take place using the Aident SQL Migration app.
Disable Indexes
For tables with many records (e.g. entry tables), migrating can take a very long time if other keys are enabled in addition to the primary key. The reason for this is that after each inserted record, all activated indexes are updated. To increase speed during migration, you can Disable Indexes for a destination table before migration. Only secondary indexes are disabled. Only after the records have been migrated should the previously deactivated indexes be reactivated.
Data Mapping
By creating a Data Mapping, the data of a field from a source table can be recoded to a new value in the destination table when the migration is performed. If other fields have a relation to the destination field, they're also rekeyed. This allows data to be migrated without any loss of information when a field changes.
Table Part Assignments
Sometimes it is necessary to migrate data from a table in several data packages, e.g. because a table contains a particularly large number of data records or because only a certain range of data records should be migrated before commissioning. This is to be made possible by the use of Table Part Assignments. These can be narrowed down or selected by setting filter values to primary key fields. For example, data from an entry table can be migrated for a specific number range by entering the Entry No. as a filter.
SQL Expressions
By integrating SQL Expressions, complex statements can be executed during migration. For example, additional conditions can be created or values from other table fields can be fetched or checked.
The ability to set SQL expressions is possible in the table mappings (source table conditions only), table submappings (source table conditions only), and field mappings.
Check systemIDs
In some tables, there are ID fields in the number range 8000 that have a table relation to a SystemID field in another master data table. This link is in addition to the link to the primary key field and is used for integration scenarios.
After migrating records, it is important to ensure that the correct values exist in the appropriate fields for the respective record in the respective company. This means that not only must the code fields be identical, but the values of the systemIDs of the linked records must also match.
For this purpose, there is a the Check systemIDs feature in the Aident SQL Migration App that searches for the corresponding table relations, checks the values of the linked systemID fields and, if necessary, corrects them in the destination table.
Filter Categories
In some cases, it can be helpful to create or execute table mappings for specific tables (for example, table mappings for a specific module). To better filter these table assignments, Filter Categories can be created and used.
Import/Export
The Import/Export functionality makes it possible to import and export the settings of a database assignment including the company assignments, table assignments, table part assignments and field assignments.