How do I set a lookup field on an imported entity record when I only have a non-primary (text) field as reference to the lookup entity?
CRM 2013 Data Import Wizard and Data Map
This is perhaps not a new feature in CRM 2013 but I got it wrong so often I thought I’ll go through it here in this post so I can look back at this when (not if) I forget again.
In CRM 2013 you can do a data import pretty much in the same way as you do in CRM 2011. You start with a csv file, which hopefully has the data in a good state. Go to Settings > Data Management > Imports, and create a new Data Import job. The Data Import Wizard should pop up and you can upload your csv file.
In the example I am considering here, the csv file contains new invoices to be imported into CRM 2013. It has a column called “Project ID (legacy) On Opportunity” which is a text field on the opportunity record that holds a unique reference to that opportunity. I want to define a data map that would set the correct opportunity lookup on the invoice record to be imported based on matching of this “Project ID” field. However, and this is the issue I want to discuss here, the “Project ID” field is not the primary field for the Opportunity entity.
When you go through the data import wizard, if you haven’t created a data map for the csv file already, you will need to manually map the source fields to the CRM entity attributes.
To make sure opportunity lookup on the invoice is set to match the opportunity’s “Project ID”, first set the source field to map to “Opportunity Lookup” in the right-hand column. A new window will pop up. You have to tick “Opportunity” and choose to match the source field against three opportunity attributes: “Opportunity” (Primary key), “Opportunity Name” (which is the Primary Field), and “Project ID”. Do not forget the first two as they will be used to populate the opportunity look-up (think EntityReference in SDK); the third attribute is the value that is matched against.
Once that is set, you should now see the mapping below:
Submit the data import job and away you go!