CRM 2013: How To Set A Lookup Value With Non-Primary Field As Reference When Importing Csv Data?

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.

DataImportLookup-01

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.

DataImportLookup-02

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.

DataImportLookup-03

Once that is set, you should now see the mapping below:

DataImportLookup-04

Submit the data import job and away you go!

DataImportLookup-05

Advertisements

9 thoughts on “CRM 2013: How To Set A Lookup Value With Non-Primary Field As Reference When Importing Csv Data?

  1. Hi Priscilla,

    I am new to MS Dynamics CRM (2013). would you be able to point me to any articles which discuss primary keys vs primary fields? I am not entirely certain what the primary field is used for. Thanks Gordon MacDonald

    1. Hi Gordon,

      Sorry for the late reply. Primary key of an entity in CRM refers to a field that holds the GUID of the record (which is unique). Primary field of an entity refers to a field that is shown as the record title, views and in reports. For example, if you look at the contact entity in CRM, you will see a field called “contactid” which is primary key of this entity holding the GUID of a contact record. At the same time, contact has “fullname” field as the Primary Field, which is used to display the fullname of a contact as the record title.

      Hope that helps.

      Regards,
      Priscilla.

  2. Can we define two primary fields in CRM (like compound keys in SQL Tables), and during import, map two fields in csv to perform lookup on these primary fields?

  3. Thank you for this post. Can you explain a little deeper why you have to map three opportunity fields? I thought I only needed to map it to the one I wanted to match on. What happens if you don’t do all three?

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