Bulk update Dataverse LookUp column using Dynamics

[Amik]

There are many ways to bulk update a Dataverse table with relationship columns. This blog specifically outlines the steps for achieving this using Dynamics, which I think is the most scalable and mature.

Scenario

Suppose we have the following Table called "Employees". This table has a mix of column data types but importantly, it includes a Lookup column called “Role ID”. This column is related to another Table called "Roles". The relationship cardinality is One-To-Many / Many-To-One.

The Roles table displays a list of Role Titles together with a unique Role ID.

We want to update the Role ID LookUp column in our Employee table with the Role ID from the Roles table.

To do this, we prepare a CSV file with the columns we want to update into the Employees table. For the purposes of this example, we have included the Employee ID, Employee Name and Role ID fields. Note we have hard coded the Role ID reference as a text value.

Please note it is important to include all the columns you need. Failure to do so will result in any data for any column not included in the import file to be cleared from the target table.

Import Steps

1. Prepare your import data as a CSV file.

2. Navigate to Advanced settings.

3. Select Settings > Data Management

4. Select Imports

5. Select Import Data, then select your CSV, and then click Next

6. This is an important part. Depending on how your csv data is structured, you will need to select the correct delimiter settings. The default settings are:

• Field delimiter: Comma ( , )
• Data delimiter: Quotation mark ( " )

7. If your import is structured as a table in Excel as per our example, select “Tab character” for Field delimiter, and None for Data delimiter. Check “First row contains column headings” if your import file includes headers in the first row.

You may receive the following pop-up notification, even if your file is structured as a Table. Click OK and then proceed until you get to the Map fields screen. From there you can see how Dynamics is reading your file.

In the example below, the delimiter we need to use is a comma. 

To resolve this, go back and then upload your file again, and then select the default Delimiter settings.

8. Click Next and select Default (Automatic Mapping)

9. Select the Table you want to import the data into under Microsoft Dynamics 365 Record Types.

10. Select your columns mappings:

11. Upon selecting a LookUp column, a small pop-up window will appear beneath the Lookup field. Tick the check box against the related Table Name. Notice the referred field is Role ID.

12. If we click the magnifying icon next to this field, we can include additional fields. Note due to the cluttered UI, it is not clear how to exit this window. To do this, select the OK or Cancel button.

13. Select Next

14. Configure any additional settings (e.g. Allow Duplicates) and then click Submit

15. Click the Import link to view progress

16. Once completed, navigate back to your Table

17. We can see the relationship column has been successfully imported.