Mapping the Tables and Fields to Synchronize

Article
04/03/2023

The basis of synchronizing data is mapping the tables and fields in Business Central with tables and columns in Dataverse, so they can exchange the data. Mapping happens through integration tables.

Mapping integration tables

An integration table is a table in the Business Central database that represents an table, such as an account, in Dataverse. Integration tables include fields that correspond to columns in the Dataverse table. For example, the Account integration table connects to the Accounts table in Dataverse. There must be a integration table mapping for each table in CDS that you want to synchronize with data in Business Central.

When you create the connection between the apps, Business Central sets up some default mappings. You can change the table mappings if you want. For more information, see Standard Table Mapping for Synchronization. If you have changed the default mappings and want to revert your changes, on the Integration Table Mappings page, choose Use Default Synchronization Setup.

Note

If you are using an on-premises version of Business Central, the integration table mappings are stored in table 5335 Integration Table Mappings, where you can view and edit the mappings. Complex mappings and synchronization rules are defined in codeunit 5341.

Tip

When a coupled record changes, Business Central automatically synchronizes the data with Dataverse. Automatic synchronization is great in most cases. However, frequent changes to large amounts of coupled records in a table can slow down data synchronization.

To avoid slow performance, on the Integration Table Mappings page, you can enable or disable event-based data synchronization for any table. By default, event-based synchronization is turned on so that existing integrations aren’t affected. Your administrator can turn it on or off for specific tables.

Additional mappings

Payment terms, shipment methods, and shipping agents can change, and it can be important to be able to adjust them. If you enable the Feature Update: Map to option sets in Dataverse without code feature on the Feature Management page, you can manually add integration table mappings for payment terms (PAYMENT TERMS), shipment methods (SHIPMENT METHOD), and shipping agents (SHIPPING AGENT). This mapping can help ensure that your policies are the same for these setups in Dataverse and Dataverse.

Synchronization rules

An integration table mapping also includes rules that control how integration synchronization jobs synchronize records in a Business Central table and an table in Dataverse. For examples of rules for an integration with Sales, go to Synchronization Rules.

Strategies for auto-resolving conflicts

Data conflicts can easily occur when business applications exchange data on an ongoing basis. For example, someone might delete or change a row in one of the applications, or both. To reduce the number of conflicts that you will have to manually resolve, you can specify resolution strategies and Business Central will automatically resolve conflicts according to the rules in the strategies.

Integration table mappings include rules that control how synchronization jobs synchronize records. On the Integration Table Mapping page, in the Resolve Deletion Conflicts and Resolve Update Conflicts columns, you can specify how Business Central will resolve conflicts that occur because records were deleted in tables in one or the other business application, or updated in both.

In the Resolve Deletion Conflicts column, you can choose to have Business Central automatically restore deleted records, remove the coupling between the records, or do nothing. If you do nothing, you must manually resolve conflicts.

In the Resolve Update Conflicts column, you can choose to have Business Central automatically send a data update to the integration table when sending data to Dataverse, or to get a data update from the integration table when getting data from Dataverse, or do nothing. If you do nothing, you must manually resolve conflicts.

After you specify the strategy, on the Coupled Data Synchronization Errors page, you can choose the Retry All action to automatically resolve conflicts.

Mapping integration fields

Mapping tables is only the first step. You must also map the fields on the tables. Integration field mappings link fields in Business Central tables with corresponding columns in Dataverse, and determine whether to synchronize data in each table. The standard table mapping that Business Central provides includes field mappings, but you can change those if you want. For more information, see Viewing Table Mappings.

Note

If you are using an on-premises version of Business Central, integration field mappings are defined in table 5336 Integration Field Mapping.

You can manually map the fields, or you can automate the process by mapping multiple fields at the same time based on criteria for matching their values. For more information, see To couple multiple records based on field value matching.

Handle differences in field values

Sometimes the values in the fields that you want to map are different. For example, in Dynamics 365 Sales the language code for the United States is "U.S.," but in Business Central it's "US." That means you must transform the value when you synchronize data. This happens through transformation rules that you define for the fields. You define transformation rules on the Integration Table Mappings page by choosing Mapping, and then Fields. Predefined rules are provided, but you can also create your own. For more information, see Transformation Rules.

Handle missing option values

Dataverse contains option set columns that provide values that you can map to Business Central fields of the type Option for automatic synchronization. During synchronization, non-mapped options are ignored and the missing options are appended to the related Business Central table and added to the CDS Option Mapping system table to be handled manually later. For example, by adding the missing options in either product and then updating the mapping. For more information, see Handling Missing Option Values.

Couple records

Coupling links rows in Dataverse to records in Business Central. For example, accounts in Dataverse are typically coupled with customers in Business Central. Coupling records offers the following benefits:

It makes synchronization possible.
Users can open records or rows in one business app from the other. This requires that the apps are already integrated.

Couplings can be set up automatically by using the synchronization jobs, or manually by editing the record in Business Central. For more information, see Synchronizing Data in Business Central and Dataverse and Couple and Synchronize Records Manually.

Filter records and rows

If you don't want to synchronize all rows for a specific table in Dataverse or table in Business Central, you can set up filters to limit the data that is synchronized. You set up filters on the Integration Table Mappings page.

Choose the icon, enter Integration Table Mappings, and then choose the related link.
To filter the Business Central records, set the Table Filter field.
To filter the Dataverse rows, set the Integration Table Filter field.

Create new records

By default, only records in Business Central and rows in Dataverse that are coupled will be synchronized by the integration synchronization jobs. You can set up table mappings so that new records or rows will be created in the destination (for example, Business Central) for each row in the source (for example, Dataverse) that is not already coupled.

For example, the SALESPEOPLE - Dynamics 365 Sales synchronization job uses the table mapping SALESPEOPLE. The synchronization job copies data from users in Dataverse to salespersons in Business Central. If you set up the table mapping to create new records, for every user in Dataverse that is not already coupled to a salesperson in Business Central, a new salesperson row is created in Business Central.

To create new records during synchronization

Choose the icon, enter Integration Table Mappings, and then choose the related link.
In the table mapping entry in the list, clear the Synch. Only Coupled Records field.

Use configuration templates on table mappings

You can assign configuration templates to table mappings to use for new records or rows that are created in Business Central or Dataverse. For each table mapping, you can specify a configuration template to use for new Business Central records and another template to use new Dataverse rows.

If you install the default synchronization setup, most of the time, two configuration templates will be automatically created and used on the table mapping for Business Central customers and Dynamics 365 Sales accounts: CDSCUST and CDSACCOUNT.

CDSCUST creates and synchronizes new customers in Business Central based on accounts in Dynamics 365 Sales.

Create this template by copying an existing configuration template for customers. The CDSCUST is created only if there is an existing configuration template and the Currency Code field in the template is blank. If a field in the configuration template contains a value, the value will be used instead of the value in the mapped column for the Dataverse account. For example, if the Country/Region column in an account in Dataverse contains U.S. and the Country/Region field in the configuration template is GB, then GB is used as the Country/Region for the customer in Business Central.
CDSACCOUNT creates and synchronizes new accounts in Dataverse based on an account in Business Central.

To specify configuration templates on a table mapping

Choose the icon, enter Integration Table Mappings, and then choose the related link.
In the table mapping entry in the list, in the Table Config Template Code field, choose to the configuration template to use for new records in Business Central.
Set the Int. Tbl. Config Template Code field to the configuration template to use for new records in Dataverse.