03 December 2021

How to Lookup Values in Other Dynamics Entities

Managing relationships between entities in Dynamics CRM is a key requirement. Relationships between entities can be found throughout Dynamics, one example is defining an account in a contact record. To have the ability to set the parent of an account we have to set the parentaccountid field in the contact entity to the accountid of the account.

The following page will cover how to lookup the accountid in the account entity based on a value in the source data. We are going to import values into the account entity and then update the contact entity setting its parent to an account from the account entity.

We are using the source data from the first project tutorial so reviewing this tutorial is a good idea.

The Data

Our data file contains contact data but we are more interested in the company_name which is a value we would prefer to import into the account entity.

Data Source A

Synchronize the Account data into the Account Entity.

Open Data Sync and set the source to the us-500.csv file (refer to the importing csv tutorial). The fastest way to do this is to drag the csv file from the file system onto the data source A window.

Drag and Drop

If you have already saved a connection to your Dynamics CRM environment you can select the entity in the connections window. Right click onto the account entity and select on Connect to Target B. If you have not created a Dynamics CRM Connection please see our documentation here.

Connection Tree

The schema map tab will show all of the fields from the source csv. Delete all of the mapping links you don't need by selecting them and click the delete button.

For this example we want to make sure that the company_name and web columns are included and mapped to the account name and websiteurl fields in dynamics.

Mapping

Remember to select the company_name and web as the key columns because neither is unique by themselves.

Click the Compare A->B to and view the results to see what is going to be Updated/Deleted/Inserted into the Dynamics 365 Account entity. Then synchronize the results to Dynamics.

Synchronise

Synchronise the Contact data into the Contact Entity

Using the same CSV File as we used in the last project, create a Data Sync project with the CSV as your source and the target set to the contact entity from Dynamics.

Configure the schema map so that the fields you want to synchronise are added. Remove the company name column as we will add this using a lookup in the next step.

Mapping

Create a lookup based on the Company Name

Now we need to create the lookup to find the account value based on the company name column in the source.

TO do this select the account entity from the connections tree and drag it onto the company_name column in the source window.

Drag and Drop

You will be presented with a dialog asking how the company_name column relates to the account entity dragged from the connections treeview. To define this set the Lookup Target Column to name.

Lookup Connection

We have essentially told Data Sync that if you need the find details on the parent record of the contact, look for the account record with the name the same as the company_name.

Data Sync will now display a list of all the columns in the account entity you can bring into the schema map.

Lookup Columns

We can now use the associated accountid from the account lookup to fill the parentcustomerid in the contact record. Add Lookup1_accountid to the schema map and map this to parentcustomerid from the target.

Mapping

Preview the data by pressing Preview A and the Lookup1_accountid column will display the associated account id's.

Preview A

You can now synchronize this data into the contact record and the contact and account will be associated. Just click Compare A>B, Synchronise and then Start to run the project.

To remove a lookup, select it in the columns list and click the icon:

Outlook
Outlook

For really advanced lookups you might want to consider using the LOOKUP functions inside the Calculated Columns.