Power Community

Power Community

Bulk update Dataverse LookUp column using Dynamics

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.


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.


This post was originally published on this site

- Advertisement -spot_img


Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Announcing SharePoint Embedded General Availability

Authored by: Zach Rosenfield Today we’re pleased to announce the general availability of SharePoint Embedded, a new way to build...

More Articles Like This

- Advertisement -spot_img