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 parentccountid field in the contact to the accountid of the account. The question is how to lookup the accountid in the account based on some value in the source data.

We are using the source data from the 'CONNECTING TO A DATASOURCE (CSV FILE)' tutorial so reviewing this tutorial is a good idea.

In 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 we are importing:

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.

Synchronize the account data into the account entity.

Open DS3 and set the Source A 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.

If you have already saved a connection to your Dynamics CRM environment you can select the entity in the connections window, right click the entity and click on 'Connect to Target B'. If you have not created a Dynamics CRM Connection please see tutorial 'CONNECTING TO DYNAMICS CRM'

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

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

Remember to select the company_name AND web as a key column 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 CRM Account entity.

Synchronize the results to CRM.

Create a CSV File to Contact Project using the same file

1. Connect the csv files on the source
2. Connect the contact entity on the target and map the fields as below:

Create a lookup based on the company name

Select the account entity from within the connections treeview and drag it onto the company_name column in the Data Source.

You will be presented with a dialog asking how the company_name column relates to the account entity dragged from the connections treeview?

In the dialog set the lookup Target column to 'name'.

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

DS3 will now display a list of all the columns in the account entity 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 pressing Preview A and the Lookup1_accountid column will display the associated accountid's.

You can now synchronize this data into the contact record and the contact and account will be associated.

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

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