Look Up Values in other Entities

This tutorial runs through how to Look 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:

Data Source A

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.

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 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'

Connection Tree

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:

Mapping

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.

Synchronise

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:

Mapping

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.

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?

Lookup Connection

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.

Lookup Columns

We can now use the associated accountid from the account lookup to fill the parentcustomerid in the contact record.

Mapping

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

Preview A

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:

Outlook
Outlook

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


Can't find the answers you're looking for? Contact our support team and we'll personally get back to you as quickly as we can.