Mapping the Tables and Fields to Synchronize

Note

Effective November 2020:

  • Common Data Service has been renamed to Microsoft Dataverse. Learn more
  • Some terminology in Microsoft Dataverse has been updated. For example, entity is now table and field is now column. Learn more

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.

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.

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.

Handling 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.

Handling Missing Option Values in Mapping

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.

Coupling 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.

Filtering Records and Rows

If you do not 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.

To filter records or rows for synchronization

  1. Choose the Lightbulb that opens the Tell Me feature icon, enter Integration Table Mappings, and then choose the related link.

  2. To filter the Business Central records, set the Table Filter field.

  3. To filter the Dataverse rows, set the Integration Table Filter field.

Creating 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

  1. Choose the Lightbulb that opens the Tell Me feature icon, enter Integration Table Mappings, and then choose the related link.

  2. In the table mapping entry in the list, clear the Synch. Only Coupled Records field.

Using 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 is used to create and synchronize new customers in Business Central based on an account in Dynamics 365 Sales.

    This template is created by copying an existing configuration template for customers in the application. 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

  1. Choose the Lightbulb that opens the Tell Me feature icon, enter Integration Table Mappings, and then choose the related link.

  2. 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.

  3. Set the Int. Tbl. Config Template Code field to the configuration template to use for new records in Dataverse.

See Also

About Integrating Dynamics 365 Business Central with Dataverse
Synchronizing Business Central and Dataverse
Schedule a Synchronization

Note

Can you tell us about your documentation language preferences? Take a short survey.

The survey will take about seven minutes. No personal data is collected (privacy statement).