Looking Up Values in other Entities

Managing relationships between entities in Dynamics CRM is a key requirement. In its simplest form the ability to set the parent of an account requires setting the parentcustomerid field in the contact to the accountid of the account. The question is how to lookup the accountid in the account based on some values in the source data.

We will be using the source data from the Connecting to a CSV Data Source tutorial.

For this tutorial we are going to import values into the account entity and then update the contact entity setting its parent to an account.

Review the Data being Imported

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

Synchronise the account data into the account entity

Open Data Sync and set Source A to the us-500.csv file. The quickest way to do this is to drag the csv file from the file system onto Data Source A.

If you have already saved a connection to your Dynamics CRM environment you can select the entity in the connections tree view. Right click on the entity and select 'Connect to Target (B)'.

If you have not created a Dynamics CRM Connection then please refer to the documentation here.

The Schema Map tab will show all of the fields from the source csv file. Delete the mapping links by selecting them all and clicking the Delete button in the toolbar.

In the data source select the company_name and web columns. Map them to the account name and websiteurl fields in the destination.

Remeber to select the company_name and web as a key column as neither is unique by themselves.

Click the Compare A -> B button to review the results and see what is going to be updated/deleted/inserted into the Dynamics CRM account entity.

Then synchronise the results to CRM.

Connect to the Contact Entity

Connect to the contact entity on the target and map the fields as shown below.

Create a LookUp based on the Company Name

Select the account entity from within the connections tree view and drag it onto the company_name column in Data Source A. This will open a Lookup Connection window asking how the company_name relates to the account entity. Set the Lookup Target Column to 'name'.
With this we have essentially told Data Sync that if you need to find the details on the parent record of the contact, then look for the account with the name the same as the company_name. Data Sync will now display a list of all of the columns in the account entity that you can bring into the mapping.

We can now use the associated accountid from the account lookup to fill the parentcustomerid in the contact record.
Preview the data by selecting 'Preview A' and the 'Lookup1_accountid' column will display the associated account IDs.
You can now synchronise this data into the contact record and the contact and account will be associated.

The drag and drop method used in this tutorial can also be completed by using the LOOKUPB function:

LOOKUPB("accountid","account", WHEN("name", company_name))

More information can be found on the Calculated Columns page.