Import Related Entity Records using Data Import in Dynamics 365 CE (Importing Lookup fields)

Data Import in Dynamics 365 is a very robust feature that we can use to import data from a .CSV file for any entity record such as Accounts, Contacts, Cases and many more. When it is single entity record with out lookup fields then its very straight forward process to import but when it requires to import multiple related entity records with Lookup fields then its not straight forward. For this scenario we have to take care of the import process properly.

Lets see my below data that I want to import.

NOTE : Always use .CSV file to import.

I have a .CSV file which stores Contact records with First Name, Last Name and Company Information as given below. Company Field is a lookup field which relates to Account Record. Note that there are two contacts for company ESS Inc.

CONTACTS CSV : Contains one lookup to related Account

I have another .CSV file which stores company information with Account Name, Phone Number and Primary Contact. This Primary Contact is the lookup field which relates to Contacts record. Also as Accounts can be self referential i.e. one account can be a child of another account so this Account CSV also holds lookup to Account entity which is self referential.

Account CSV : Contains one lookup to relate Contact and another to relate Account(Self Referential)

So Contact records is related to Account records as Company Name and Accounts records are related to Contact as Primary Contact.

So we have both 1:N and N:1 relation exist in both the Contact and Account. Also we have 1:N exist between Account and Account (Self Referential).

Now we want to import inside Dynamics 365 Account and Contact Entities. So let us see how we can configure the data import.

Step: 1 – Prepare Data

Prepare two files : one for Accounts and another for Contact and specify the records and field information correctly.

Important Please Note: Company Lookup field value in Contact CSV must match with Account name field of Account CSV. Primary Contact field value of Account CSV must match with the First name and Last Name of the Contact CSV as the references are resolved by Names. The Parent Account field value of Account CSV must with Account Name in Account CSV.

You can download my sample files below.

Now ZIP both the CSV file and keep ready for import.

Step: 2 – Import Data

Go to Advanced Settings in Dynamics 365 and Navigate to Data Management and click Imports.

In the Next screen click on Import Data. It will prompt to upload the ZIP file you have prepared. Now click on Choose File and select the ZIP file you have prepared.

Now click Next for providing more information. In the next screen just validate your CSV files and the Delimiter settings. Make sure Field delimiter is Comma selected aa e have saved our CSV files as Comma delimited CSV file.

Click Next and in the next screen choose Default mapping option and click NEXT.

Now Click Next and Select and Map correct Entities for CSV Files names as below. Form Accounts CSV choose Account Record Type and for Contacts SCV choose Contacts record Type and Click Next.

In the Next screen you have to map corresponding fields of CSV file entities with Dynamics 365 Records Types Fields. Select Accounts from left and at right side choose corresponding fields.

before proceeding further you can observe the lookup field here. for the parent Account field click on the magnifying glass icon to check the lookup property. You can see that the lookup is pointing to Related Entity Account and in the textbox its mentioned as – Account (primary Key);Account name. This means in import the system will associate the parent account field from the Account records Name and primary key.

Same as if you validate Primary Contact field you can see the lookup field properties as below. Here it is pointing to Contact entity and the textbox holds value as – Contact (primary key); Full Name. That’s why you have to keep the referencing names as exact otherwise import will fail.

Next repeat the above steps for Contact entity. For Contact Company field choose Company Name (Lookup) to map it. As soon as you select it will prompt to select the reference field properties. Here you will see two entities Account and Contact. Why? Because the Company field on Contact Entity is a Customer data type field which related to either a Contact or an Account. Now for our case we have to select Account as the company name will associate to an Account. and in the text field you can see Account name is selected. The Account will be resolved by Account name. So keep the NAME fields exact.

After all these steps click NEXT.

Next screen will give you a review stage so that you can review and click Next.

Next you have to choose whether you allow duplicate record or not. and also to mention the owner of the import. Also you can give a map name so that next time when you import these CSV files you do not need to remap fields rather it will automatically map as per previous mapping which will save time.

after given all values click Submit and then click Finish. Now you will see two batch import process will be queued to submit records. after 5 minutes click the refresh icon on the same screen. The initial status is Submitted and gradually the Status Reason, Success, Failure and details will be updated.

After the import the status gets completed with success counts. Now all records are imported successfully.

Step: 3 – Validate Data in the System

Now we can validate Contacts and Accounts in the system.

lets go to Sales Hub and navigate to Contacts. All Contacts are created successfully and with lookup fields to Company.

Open Account View and Lets check. The records are created with lookups successfully.

Let us check the Account Hierarchy. Click the Hierarchy icon besides the Account Name you can see the Account hierarchy between accounts.

NOW EVERY DATA IMPORTED Successfully. Cheers. I hope it will help you in your assignment.