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.
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
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.
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.
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
web columns are included and mapped to the account
websiteurl fields in dynamics.
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.
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.
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.
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
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
Data Sync will now display a list of all the columns in the account entity you can bring into the schema map.
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.
Preview the data by pressing Preview A and the
Lookup1_accountid column will display the associated account id's.
You can now synchronize this data into the contact record and the contact and account will be associated. Just click
Synchronise and then
Start to run the project.
To remove a lookup, 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.