Import Data Behaviour for Lookup Fields in Dynamics 365/Microsoft Dataverse

There are several ways to import the data into Dynamics 365 or Common Data Service and one of them is using out-of-the-box Data Import Wizard. When the Import Data functionality is used to create the records, the system handles the lookup fields differently from the other types of data.

• If the lookup field requirement option is Business Required (Mandatory), the system resolves the lookup and set the value upon record creation
• Otherwise, the system creates all the records with an empty value in Optional lookup fields. Only after the whole import file is processed, the system resolves the Optional lookup fields and populate with data

This behaviour is to avoid record import failure because of the lookup data dependency between each other. Let's go through the following examples of importing data for Account entity to understand better.

Scenario	Outcome
Field Requirement for Parent Account: Optional  Account Name Parent Account   Adventure Works    Fourth Coffee  Adventure Works	Adventure Works record is created. Fourth Coffee record is created with no value in Parent Account field. Fourth Coffee record is updated and Parent Account field is populated.
Let's update the field requirement in this scenario Field Requirement for Parent Account: Business Required  Account Name Parent Account   Adventure Works    Fourth Coffee  Adventure Works	Adventure Works record is created. Fourth Coffee record is created with a value in Parent Account field.
In this scenario, the Fourth Coffee is the first row in the import file. Field Requirement for Parent Account: Business Required  Account Name Parent Account   Fourth Coffee  Adventure Works   Adventure Works	Import is failed for Fourth Coffee record because the lookup value for Parent Account field cannot be resolved. (because Adventure Works record is not in the system yet) Adventure Works record is created.
The order of the record does not matter anymore if the lookup field requirement is Optional. Field Requirement for Parent Account: Optional  Account Name Parent Account   Fourth Coffee  Adventure Works   Adventure Works	Fourth Coffee record is created with no value in Parent Account field. Adventure Works record is created. Fourth Coffee record is updated and Parent Account field is populated.
It works even if the two records are interrelated each other via Parent Account lookup Field Requirement for Parent Account: Optional  Account Name Parent Account   Fourth Coffee  Adventure Works   Adventure Works  Fourth Coffee	Fourth Coffee record is created with no value in Parent Account field. Adventure Works record is created with no value in Parent Account field. Fourth Coffee record is updated and Parent Account field is populated. Adventure Works record is updated and Parent Account field is populated.
The system can also handle multiple file import. E.g. .zip file which includes the following two .csv files is uploaded Account.csv mapped to Account entity Field Requirement for Parent Account: Optional  Account Name Primary Contact   Adventure Works  Nancy Anderson Contact.csv mapped to Contact entity Field Requirement for Company Name: Optional  Last Name Company Name   Nancy Anderson  Adventure Works	Adventure Works Account record is created with no value in Primary Contact field. Nancy Anderson Contact record is created with no value in Company Name field. Adventure Works record is updated and Primary Contact field is populated. Nancy Anderson record is updated and Company Name field is populated.

Now that we know how it works, we can set the lookup field requirement accordingly if there is any issue with the Data Import Wizard.

e.g. If the import file contains records with interrelated to each other in the mandatory lookup field, the lookup field can be set to Optional temporarily before the import and revert afterwards.

The way how Import Data functionality handles the lookup fields with Optional field requirement is safer if the imported data has a dependency on the other records in the same import. But there are times where this behaviour may cause an issue.

Scenario: The field requirement of the lookup field is Optional. When the record is imported, the value of the optional lookup field is empty on Create. At the end of the imported records' creation, the record is updated by the system and the lookup field is populated.

Problem 1: If there is a synchronous Workflows/Plugin which triggers on Create of the record and it is looking for the lookup value on Create event, the value will be empty. There might be some complications (e.g. NullReferenceException) if those processes do not handle empty value for the lookup field.

Problem 2: Naturally, we might think that importing records would trigger the process configured for Create event only. But in this case, it will also trigger the plugin/workflows/flows for Update event with filtering attribute for the lookups included in the data import. That may cause unnecessary outcome if those processes are not only supposed to run for a manual update of the lookup field. (e.g. creating a record in Approver history entity)

This is the real-life problem that I encountered which made me understand more about this behaviour of the Import Data functionality in Dynamics 365 / Common Data Service.

Background: The business units need to be copied across different environments (Dev > Test > UAT > Production) as configuration data. The approach was to export the Business Units except for the root BU using Advanced Find. Exported .csv file of Business Units was imported to the target environment using Data Import Wizard.

🛈 Bonus Tip 1

If the Business Unit record is referenced in various components (e.g. workflow), it is better not to use the root BU and create a child BU even if only one single BU is required for the project. The root BU is automatically created by the system and the GUID is different for each environment. If any of the components is referencing the root BU by record/GUID, it will stop working when it is deployed to the new environment.

Initial Problem: Before the Business Units .csv file was imported into the target environment, the name of the root BU had to change to match with the name in Parent Business column of the .csv file. The name of the root BU cannot be changed easily since the Parent Business field is mandatory and the system shows an error "You must provide a value for Parent Business" whenever the form is saved after any change to the root BU. This method was used to change the field requirement of Parent Business to update the name of the root BU which led to the main problem.

🛈 Bonus Tip 2

Use the God Mode in Level up extension to update the name of the root Business Unit more easily. The root Business Unit record needs to be opened in Unified Interface for the extension to work. If the record is opened from Classic setting security area, Business Unit form will load in CRM 2011 and the extension will not work. SQL 4 CDS tool or instant flow or even web resource can be used to tackle this problem.

Update: Gus Gonzalez also posted 2 Minute Tuesday video on how to do it using excel export/import.

Main Problem: This issue would not happen if the field requirement of Parent Business was reverted to Business Required after updating the name of the root BU. But when the child Business Unit records were imported using Data Import Wizard, field requirement of Parent Business was Optional and the import of the records failed with the following error.

Only one organization and one root business are allowed.

It turned out to be that the Data Import mechanism attempted to create the Business Unit record with no value in Parent Business field and populate it only after all records are created. As mentioned at the beginning of this post, this was caused by the field requirement of Parent Business being Optional.

Summary: If the lookup field is Mandatory, the Data Import mechanism populates the value in the Create step and if the lookup field is Optional, the Data Import mechanism creates the record with an empty value for lookup and populates with another Update step.