Sunday, August 14, 2011

Changing the Lookup Reference When Importing Related Data

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.

21 comments:

wjs said...

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?

Leon Tribe said...

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.

Katie said...

Thank you so much! Great post!!

CRMFrenzy said...

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?

Leon Tribe said...

Hi CRMFrenzy,

It just so happens I posted an article last week which may be of assistance ;)

http://leontribe.blogspot.com.au/2012/10/an-alternative-method-for-data.html

Leon

Ajay said...

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?


Leon Tribe said...

Hi Ajay,

If you are importing to a lookup field it is mandatory to specify how the import tool is going to identify a unique match i.e. what field to use.

Unknown said...

Hi Leon,

I am getting "A duplicate lookup reference was found" issue when i am importing the Lead entity records. could you give me a solution for this issue

Thanks

Leon Tribe said...

Bujji,

It means whatever you are using as the reference in the lookup is getting 2 or more hits as possible records.

Leon

Unknown said...

Yes I have duplicate records in the list so what i have to do to solve this issue? Is there any solution to fix this issue?



Thanks

Leon Tribe said...

If there is more than one match, the import will not know the correct record. Change the data to make the match unique or enter the record manually.

Unknown said...

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

Leon Tribe said...

I'd highlight them in CRM, 250 at a time, and hit the Assign button.

Unknown said...

It is taking so much time to assign. If it is by import option we can make it easy that is why i am asking solution for this.

Thanks

Leon Tribe said...

Another option would be to export as a static worksheet and tick the enrichment box. This way the GUID will be kept with the records and you should not get the duplicate match error.

Unknown said...

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

Leon Tribe said...

I'd probably filter them out pre-import but you can restrict duplicate creation by setting up the right duplication rule and setting the no duplicates option for the import.

Unknown said...

Hi Leon,
Could you explain the steps to setup restriction of duplicate creation of Accounts and contacts while creating the new records.


Thanks,
Bujjibabu T

Leon Tribe said...

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.

Unknown said...

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

Leon Tribe said...

Bujji,

Using the forums is probably a better option for this kind of thing. My guess is some of the records are inactive.

Leon