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.

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.

Amik_0-1692268582941.png

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

Amik_1-1692234251891.png

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.

Amik_1-1692267961556.png

Import Steps

1. Prepare your import data as a CSV file.

2. Navigate to Advanced settings.

Amik_3-1692234430382.png

3. Select Settings > Data Management

Amik_4-1692234450728.png

4. Select Imports

 

Amik_5-1692234480972.png

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

 

Amik_7-1692234546627.png

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 ( ” )

Amik_8-1692234571925.png

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.

Amik_9-1692234635222.png

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.

Amik_2-1692268186795.png

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

Amik_3-1692268309449.png

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)

Amik_10-1692234660007.png

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

Amik_11-1692234680226.png

 

Amik_12-1692234693485.png

10. Select your columns mappings:

Amik_4-1692268431522.png

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.

Amik_5-1692268449374.png

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.

Amik_6-1692268454858.png

13. Select Next

Amik_16-1692234806763.png

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

Amik_17-1692234825599.png

15. Click the Import link to view progress

Amik_18-1692234849347.png

16. Once completed, navigate back to your Table

Amik_19-1692234868749.png

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

Amik_20-1692234893412.png

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

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