Table of Contents

Data Mapping

In this article, you will learn how to set up a data mapping in the Aident SQL Migration app in Business Central.

What's the purpose of the function?

In order to successfully execute a data mapping, a table assignment with a corresponding field assignment must be created for the destination table. The field assignment must be assigned to a source field, and the field assignment type must be set to Data Mapping.

The data is retrieved from the source table by executing a generated SQL query for each company. The companies from the company assignment are taken into account. If there is no company assignment, the default company of the respective database is used. The data of the source table is temporarily written to a table and can then be transferred to the data assignment. The new destination data can then be adjusted. This checks that the field lengths aren't exceeded and that no duplicate keys are used. When the migration is executed, the customized new destination data is then used for all field mappings that have the corresponding data mapping code and the data mapping field assignment type.

Prerequisites:

In order to create a data mapping, table assignments with corresponding field assignments must already have been created. Furthermore, only companies that have been created in the company assignment are taken into account. If no company assignment exists, the default companies of the respective databases are used.

Create Data Mapping and update field assignments

Follow these steps to create a new data mapping:

  1. Navigate to Database Assignments
    • In Business Central, search for and go to the Database Assignments page.
  2. Open Database Assignment
    • Open the database assignment for which a date mapping should be created. The Database Assignment Card page opens.
  3. Open Data Mappings
    • Click Data Mappings in the Home action menu. The Data Mappings page opens.
  4. Create New Data Mapping
    • Click New in the action bar to create a new data mapping. The Data Mapping Card page opens.
    • Enter a Code and a Description in the General FastTab.
    • Enter the Related Table ID and the Related Field No. in the Relation FastTab for which the data mapping is to be created.
  5. Update Field Mappings
    • Click Update Field Mapping in the action bar of the Data Mapping Card page.
    • If there are no relations yet, they must first be updated. Confirm the message with Yes
    • Click OK to close the message that the field assignments have been updated.
    • The updated table assignments are then displayed in the Data Mapping Field Assignments SubPage.
  6. Check field assignments
    • Click Show Table Assignment in the action bar of the Data Mapping Field Assignments SubPage. The Table Assignment Card page opens.
    • Check in the Field Assignments SubPage the records with the Field Assignment Type = Data Mapping and the data mapping code from step 4.

Check Source Table Data for Data Mapping

Follow these steps to check the source table data for a data mapping:

  1. Navigate to Database Assignments
    • In Business Central, search for and go to the Database Assignments page.
  2. Open Database Assignment
    • Open the database assignment for which a date mapping should be created. The Database Assignment Card page opens.
  3. Open Data Mappings
    • Click Data Mappings in the Home action menu. The Data Mappings page opens.
    • Click Edit in the action bar to open the selected data mapping. The Data Mapping Card page opens.
  4. Check Source Values
    • Click Check Source Values in the action bar to read the data of the respective source tables. A SQL query is then created and executed. This retrieves the data of the assigned source field of the field assignment from the assigned source table (per source company). The retrieved data is then displayed in the New Values page.
    • Click Transfer Selected Values or Transfer All Values in the action bar to transfer the retrieved values to the data mapping. Confirm the dialog with Yes.
    • Close the New Values page.
    • The adopted values are then displayed on the Data Mapping Field Values SubPage of the Data Mapping Card page.
  5. Check New Values
    • The adopted values can then be checked and adjusted in the Data Mapping Field Values SubPage. It's checked that the length of the new value doesn't exceed the field length of the assigned source field. Incorrect records are highlighted in color.
    • Furthermore, it is checked that the new destination values per destination company are unique and that no duplicate destination values are used. The number of destination values used per company is displayed in the No. of New Values field. Duplicate values are also highlighted in color.
    • Click Unique New Value in the action bar to replace duplicate values per company with unique new values.
  6. Create and run transfer queries
    • When creating and running the transfer queries, the new values from the data mapping are now taken into account.

Next step

Table Part Assignments