Look-up Data and the Microsoft Dynamics CRM Import Data Wizard
I am sure you are following the series of blogs introducing you to Import Data Wizard of Microsoft Dynamics CRM Online November 2009 Service update. This blog will help you with importing records having references to other records. These “other records” can be the records in same file, other files present in the same ZIP file, existing records in CRM, or a combination of any of these.
This kind of data is called look-up data because basically the record referencing the other record is looking-up to the record being referenced. The mapping defining which record type and field will be used to identify the referenced record is called “look-up mapping”; this whole process of identifying the referenced record based on the look-up mapping is called “look-up resolution”.
I’ll walk you through different scenarios in which you would want to import look up data.
Importing data in an existing field of look up data type
Let us understand this with the help of an example- the Parent Customer field of Contact record type. This is a look-up type field in MSCRM and is basically an Account or a Contact record.
This is my Contact.csv data:
This is my Account.csv data:
I have following existing contacts in the MSCRM:
Full Name Guid
Marco Tanara C8D29FEF-7234-4F71-9C3B-2FB7DCD23FE1
Darren Parker CA9DD98D-ED8B-4A94-831A-170B2A155290
You can zip these files and upload for import in the Import Data Wizard. Map the files to corresponding record types in CRM. Map columns for Contact.csv as shown below.
For Account.csv, map Name to Account Name. As you’ll see, all contact records will get imported successfully and all look up references will get resolved either to the account records in other file or the existing contact records in CRM.
This was the simplest case of look-up resolution where you map your column to the look-up field of the CRM. In this case, Import Wizard generates lookup mappings which are used during import. These mappings are generated for the Primary field as well as Primary Id of the referenced entity.
In our example, the referenced record types were Account and Contact (Since the Parent Customer of a Contact can only be an Account or Contact record). So in this case look-up mappings are generated for Account Name as well as Account Id for Account entity(since Account Name is the Primary field for the Account record type). Similarly, look-up mappings for Contact include Full Name and Contact Id. Following is the xml node showing the attribute mapping generated by the import wizard including the look up mappings.
– <AttributeMap>
<SourceAttributeName>Customer</SourceAttributeName>
<TargetAttributeName>parentcustomerid</TargetAttributeName>
<ProcessCode>Process</ProcessCode>
– <LookupMaps>
– <LookupMap>
<LookupType>System</LookupType>
<LookupEntityName>contact</LookupEntityName>
<LookupAttributeName>contactid</LookupAttributeName>
<ProcessCode>Process</ProcessCode>
</LookupMap>
– <LookupMap>
<LookupType>System</LookupType>
<LookupEntityName>account</LookupEntityName>
<LookupAttributeName>name</LookupAttributeName>
<ProcessCode>Process</ProcessCode>
</LookupMap>
– <LookupMap>
<LookupType>System</LookupType>
<LookupEntityName>contact</LookupEntityName>
<LookupAttributeName>fullname</LookupAttributeName>
<ProcessCode>Process</ProcessCode>
</LookupMap>
– <LookupMap>
<LookupType>Source</LookupType>
<LookupEntityName>Contact</LookupEntityName>
<LookupAttributeName>Name</LookupAttributeName>
<ProcessCode>Internal</ProcessCode>
</LookupMap>
– <LookupMap>
<LookupType>System</LookupType>
<LookupEntityName>account</LookupEntityName>
<LookupAttributeName>accountid</LookupAttributeName>
<ProcessCode>Process</ProcessCode>
</LookupMap>
– <LookupMap>
<LookupType>Source</LookupType> This node is explained below.
<LookupEntityName>Account</LookupEntityName>
<LookupAttributeName>Name</LookupAttributeName>
<ProcessCode>Process</ProcessCode>
</LookupMap>
</LookupMaps>
</AttributeMap>
Since look-up mappings are generated for accountid and contactid too, our last Contact Forrest Chand got successfully imported with Parent Customer Darren Parker.
Now I’ll talk about a more complicated scenario where you want the look-up resolution to happen on a field other than the Primary field or Id. Currently, it is not possible to achieve this using the wizard UI. But, you can provide a data map to the Import Data Wizard defining the look up mapping you want. These mapping are retained by the Import Data Wizard and used for look-up resolution.
A look-up mapping can be of two types – Source and System as specified by the LookupType node of the xml.
<LookupType>System</LookupType>
When LookupType is specified as Source in map XML, look up resolution happens only against the data in source files. But when LookupType is specified as System, look up resolution happens both against data in source files as well as data in CRM. This happens because Import Data Wizard automatically generates Source look-up mapping for every System look-up mapping present in the map XML if a mapping for a mapping for that System attribute exists in the map. (You would have noticed Source type look-up node for Name field of Account in the map XML given for Customer column above!)
I’ll modify the above example to explain this scenario now.
Here is how the node for Customer mapping should look like if Email column is used for look up resolution.
– <AttributeMap>
<SourceAttributeName>Customer</SourceAttributeName>
<TargetAttributeName>parentcustomerid</TargetAttributeName>
<ProcessCode>Process</ProcessCode>
– <LookupMaps>
– <LookupMap>
<LookupType>Source</LookupType>
<LookupEntityName>Account</LookupEntityName>
<LookupAttributeName>Email</LookupAttributeName>
<ProcessCode>Process</ProcessCode>
</LookupMap>
</LookupMaps>
</AttributeMap>
When these 2 files are imported with a map containing above mappings, end result would still be the same as the previous case. The Parent Customer for Cat Francis would be A Store while for Eva Corets, it will be Grand Store after import.
Please notice that when specifying LookupType as Source look-up mapping, the values given in LookupEntityName and LookupAttributeName nodes should correspond to the file name of the source entity and column name in the file. Similarly, for System type nodes, these values should be CRM record type name and field name.
Creating a new look up type field during import itself
Creating new record types and fields of different types using Import Wizard. See section “Creating a relationship with new record type” for details on how to create a new look up type attribute and import data in it.
For our old example, creating a new Customer field of look up type will look like following in Import Wizard:
Two main points to note while creating a new look up type field are:
- Related Record Type can be an existing CRM Record type as well as a new custom record type being created in this import session itself.
- In this case also, look-up mapping are generated for the Primary field and Id of the Related Record type as shown below.
<AttributeMap>
<SourceAttributeName>Customer</SourceAttributeName>
<TargetAttributeName>new_customer</TargetAttributeName>
<ProcessCode>Process</ProcessCode>
– <LookupMaps>
– <LookupMap>
<LookupType>System</LookupType>
<LookupEntityName>account</LookupEntityName>
<LookupAttributeName>name</LookupAttributeName>
<ProcessCode>Process</ProcessCode>
</LookupMap>
– <LookupMap>
<LookupType>System</LookupType>
<LookupEntityName>account</LookupEntityName>
<LookupAttributeName>accountid</LookupAttributeName>
<ProcessCode>Process</ProcessCode>
</LookupMap>
</LookupMaps>
</AttributeMap>
I hope this blog helps with all your look up related queries.
