Changing the Lookup Reference When Importing Related Data

Standard

This is a handy new feature for the import wizard where matching off the name of a parent is not sufficient to link records you are importing to other records in CRM, such as where the primary name of the parent record is not unique. We used to be able to do this with the old server-side migration manager in v4 but not in the v4 import wizard.

Step 1: Getting a Template For Our Import

A really nice feature of Dynamics CRM 2011 is the ability to generate templates for all the CRM entities straight from the client. To do this, click the drop-down arrow on the Import button in the ribbon for the entity you want to import records for.

image

This generates an Excel-compatible XML file which has nice features like drop-downs for the pick list fields you need to fill in and tooltips to let you know the kind of information that needs to go into a field.

image

You will notice in the above image of the Contact import sheet that I have set the Parent Customer to ‘12345’. This is the Account Number for my parent customer, not their name, as would be traditionally used.

image

Setting the Lookup in the Import Wizard

Importing the data into CRM is very easy with the Dynamics CRM 2011 Import Wizard but, by default, the wizard will try to link our Contact to an Account with the name, ‘12345’, not the Account Number ‘12345’.

image

Therefore we have to adjust our mapping so it does not match on the Account Name but the Account Number. To do this we run the Import Wizard as usual by clicking the ‘Import Data’ button on the ribbon but when we get to the ‘Review Mapping Summary’ screen we click ‘Edit’.

image

Skip setting the entity you are writing the records to and click ‘Next’. This will bring you to the mapping screen where we link the columns in our data source to fields in CRM. In our case we are interested in the ‘Parent Customer’ field.

image

The magnifying glass means this is a lookup field and by clicking on the magnifying glass we can set the matching fields.

image

The Parent Customer field is an interesting one because it can point to either an Account or Contact. In our case we are only interested in modifying the Account referred field to be the Account Number. We modify it by clicking on the magnifying glass and setting Account fields to match on. In our case we might also untick the Contact as an option for the Parent Customer as we will only bringing in Contacts with a Parent Account.

image

The good news is now when we import everything goes to plan.

image

image

Enjoy.

Advertisements

21 thoughts on “Changing the Lookup Reference When Importing Related Data

  1. This is a great feature, but I'm unclear on why we can have multiple lookup references. If my lookup contains both Account Number and Account Name, would the lookup succeed if the “12345” either a number of name? Or would both fields have to be “12345” for a match?

    Like

  2. My understanding is the ability to select multiple lookups is that it tries to match on all of them. If more than one 'hit' comes up, the import fails for that record.

    To answer your question, if I had ticked both 'Account Number' and 'Account Name' for the lookup, in my case, it would have still worked as there were no Accounts with the name '12345'. If, however there was another Account with that name, as well as an Account with the Account Number '12345' it would have failed.

    Like

  3. Is there a way to set the lookup reference to a different field when using the Export with Reimport option? I haven't been able to come up with a solution to that? Was wondering if you have any clever workaround there?

    Like

  4. Mapping the columns in the datafile to the Lookup field is mandatory?
    Is it not possible to create the datamap with default values for Lookup field if the mapped column is not present in the source data file?

    Like

  5. I want to change owner of all the Lead records i have around 5000 records. Shall i filter it in excel as unique and import it? after import all unique then i can manually change owner for remaining records, Is it ok?

    Thanks

    Like

  6. Hi Leon,
    How to restrict the creation of duplicate Accounts,Contacts, Leads etc. Is there any validation for this issue? In my CRM product most of the records are having with same name , mail id and Primary contact. Please let me know how to restrict the duplicate creation while creation.

    Thanks,
    Bujjibabu T

    Like

  7. You go to the CRM Settings area and go to data management. There you can set up your data rules and enable them. Once this is done, set the import to not import duplicates and it should restrict them.

    Like

  8. Hi Leon,

    I am getting the below error while changing the owner of the Lead record, not for all the Lead records for only few records it is showing like this “Insufficient Permission” You do not have permission to access these records. Contact your Microsoft Dynamics CRM administrator. I am the Administrator but it is showing like this. could you let me know the solution for this? I have tried my best but I am not able to solve this one.

    Regards,
    Bujjibabu T

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s