The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Look-up Data and the Microsoft Dynamics CRM Import Data Wizard

Look-up Data and the Microsoft Dynamics CRM Import Data Wizard

  • Comments 5

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:

         clip_image002

This is my Account.csv data:

        clip_image004

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.

clip_image006

For Account.csv, map Name to Account Name. Proceed till the end of the wizard as explained here. 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.

clip_image008

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.

              image

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 is covered here. 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:

clip_image016

Two main points to note while creating a new look up type field are:

  1. 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.
  2. 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.

Cheers,

Khushboo



  • This is a really powerful functionality!  Thank you for releasing the November 2009 Import Data Wizard release.  The November 2009 IDW release continues to amaze me at how much power it has under the engine.

    Two questions:

    1. Is this Lookup Type XML editing supported for production use?  I mean after we tested the modified XML with the lookup change and release it?  Microsoft CRM Support will help trouble-shoot this if it errors out during production?

    2. Is there a document that details the various syntax/format that the XML supports?  Other supported but "undocumented" functions?  Or just check the blogs like this one?

    Thanks,

    Frank

  • Hello is it possible to change the lookup during an update import?

  • I like your blog & enjoy reading your posts!

    Thanks for writing such an interesting article.

  • Hi Frank,

    Thanks for your comments.Regarding your first question- Yes,Microsoft support should be able to help you trouble shoot the issues that you might face in production.

    Regarding second,you can refer to the Data Migration Manager Help released with CRM V4 which contains exclusive sections on "Using a Data Map to Map Data". It can be found at http://www.microsoft.com/downloads/details.aspx?familyid=A3504FF1-F2E8-423B-8CD8-AA712CEA02F1&displaylang=en. The xml schema for lookups that is supported by platform in R4 is same as CRM V4.And yes, old blogs on lookup xml schema can also be referred.

    Also, you can refer to Online resources like http://rc.crm.dynamics.com/rc/regcont/en_us/live/help/ug_idw_type_relationships.htm for more information.

    I hope this helps.

  • Hi Michael,

    Update using Import wizard is not possible in this service update.

Page 1 of 1 (5 items)
Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post