Blog

Subscribe to our RSS data feed.

Using the Native CRM 4.0 Import for Lookup and Picklist Values

Posted by Jim Steger on March 18, 2008  |  commentsComments (13)

I have recently been asked if you could use the native Tools-Import functionality of Microsoft Dynamics CRM 4.0 when importing records with lookup and picklist values. My answer was "of course", as I use it all the time with our production CRM system internally.

However, I never really thought about what I was doing, since it just happen to work for me the first time. I suspect that one of the reasons for my apparent success was that I started by exporting from a view (or advanced find) and used that as my data source template, which CRM in turn used an automatic data map for me. I decided to do some quick research and detail my findings in hopes that it is useful for others trying this.

Here are some key things to consider when importing with the native Import functionality:

  1. You need to have CRM automatically map the data map for your import. To do this, ensure your import columns match EXACTLY the attribute display name. The easiest way to do this is to export from a view or advanced find. Ensure the related records or picklist values exist in the system prior to import.
  2. You can use the display value or the actual value of the lookup or picklist record. For lookup relationships, the display name will be the primary attribute and the record GUID will be its value. For picklist attributes, the display name will be the name shown to users and its value will be an integer.
  3. Ensure you have all required fields in your source data file.
  4. Save your source data file in the CSV format and then run through the native import process.
  5. All records imported through the native Import functionality will be owned by you. If you need the records owned by someone else, you should either use an alternate method of import or simply reassign the records after you get them into the system (manually or with a workflow rule).
  6. If your name (for either the lookup or the picklist) has duplicates, the record will not be imported. You will receive an error similar to A duplicate lookup reference was found. You can avoid this by specifying the GUID (record id of the referenced value) or integer (identifier for the picklist value) instead of the name. Keep in mind that only the record with the duplicate name will fail, not the entire import.

Here is a quick example of importing contacts associated to different parent account records. Let's say that the following two accounts, Contoso and Fabrikam, already exist in your Dynamics CRM system. Fabrikam's record id is 072AA102-11F5-DC11-8C5D-0003FF1FCD52. You now want to import the following 5 contact records from Excel to CRM, and each belongs to one of those two accounts. The Excel file is shown below. This file will show you that you can use either the Parent account's name or identifier and the import will still work fine.

Save your file as a CSV file and then back in CRM, click Tools, then Import Data. Select the CSV file and then click Next. Select the record type (contact in this case) and you should see Automatic dynamically listed for the Map. If you do not see this, then one of your columns does not exactly match an attribute for that record type. Go back and correct your source data file.

Finish the import wizard steps, and then from the Workplace click Imports and you can then monitor your import status from here. The next image shows the actual import record. This record tracks your settings from the Import wizard, as well as what records failed or were created successfully.

Once complete, you will now see your new records in the contact grid.

Of course, the native Import functionality wasn't meant to solve all import challenges. When you find that you have stretched its capabilities, consider the free Microsoft Dynamics CRM Data Migration Manager (DMM) tool, a third party tool like Scribe, or write your own import (using the supported CRM API's of course).

Comments

  1. this works great for views that you can customize but what about entities such as order product. You cannot change or create a new view. I am having trouble uploading order product info to an existing order because the order id is not mapping correctly.

    Thanks

    Posted by: stacy  |  Apr 15, 2008 10:46:50 AM

  2. Hi Jim!!

    About the key number 6 "If your name (for either the lookup or the picklist) has duplicates, the record will not be imported. You will receive an error similar to A duplicate lookup reference was found. You can avoid this by specifying the GUID (record id of the referenced value) or integer (identifier for the picklist value) instead of the name. Keep in mind that only the record with the duplicate name will fail, not the entire import.
    Here is a quick example" , I have in my aplication on CRM 3.0 an attribute named "ID" because is the ID of the student at the campus, if i need that this attibute isn't duplicate in Microsoft CRM 4.0 using the same logic that the example that you give about the name, is it possible with the tool-import?...
    Thanks for your attention;
    Cheers!
    Gaby

    Posted by: Gaby  |  May 15, 2008 4:07:40 PM

  3. Hi, Jim
    I have a question about your very first statement about exporting an existing view or Advanced Find to create your csv file that you eventually import back in. Are you saying that when you export from CRM your csv looks like the first picture? When I export I always have xml. I have saved it as csv during the export: where am I going wrong?

    Posted by: Judy  |  Jul 15, 2008 1:35:48 PM

  4. Gaby, make sure you're using the Dynamic Worksheet option in the export. Then, highlight a bunch of columns in your spreadsheet and "unhide" them. You will then see the GUID for whatever entity you exported.

    Posted by: Katie  |  Jan 28, 2009 3:21:27 PM

  5. Sir,
    I have installed CRM 4.0 on my machine, with 1warning : verify domain SPN for crm asp.net application pool.

    Installation of CRM 4.0 is sucessful, the big problem is the credentials with which i have installed the same are Administrator of my Domain still when i start crm 4.0 with same credentials it gives me error restricted access 401.

    Kindly help me sir..

    Posted by: Amey Gondkar  |  Feb 27, 2009 8:34:17 PM

  6. Hi,

    Great article. Worked like a charm once I had discovered the inactive accounts which aren't displayed in the active accounts view :) I do have one question though... how would you import say 10 contacts to an account but have only one linked on the account as the primary contact? If I was to import contacts, it would require my updating the account record with the primary contact ID.

    Thanks,

    Lee

    Posted by: Lee  |  Apr 20, 2009 10:16:15 AM

  7. Hi,

    Your descriptions above are great, thanks.

    I have imported accounts, contacts, leads and opportunities successfully into CRM 4, but cannot seem to be able to crack importing emails to or from those contacts or leads. Do you have a detailed instruction about how this can be completed, especially if it includes what the required format of the To and From fields are?

    I have already tried to import using initially the name, eg First Last, then raw email address, eg first.last@company.com.au, then the GUID, exported from either contacts or leads. None of these seem to work when mapped into either the "To", "From", or "Torecipients" fields.

    Any help would be greatly appreciated!

    Thanks

    John

    Posted by: John  |  Jan 31, 2010 6:07:46 PM

  8. HI..i have created entity called ,city ,which having on field called ,sate(which is comming from state entity),i have taken this state field in city as lookup,while running entity city,it is showing states in lookup view properly,but when i try to do data mapping for city,it is showing error for column state during import csv file, it is showing errors for state column in error report.how to get a rid of it...plz help

    Posted by: prashanth miraaje  |  Feb 20, 2010 4:37:08 AM

  9. Hi Jim,
    Thank you for the informative post which for sure helped all of us at a certain point.

    Moreover, I have one urgent requirement which i appreciate your assistance in it.
    I have to update the product entity in Dynamics CRM 4.0 with new current cost for some items.
    The update feature is not available in Data migration manager and the built-in import feature is failing with conflict error of the same product IDs.

    Your suggestion is highly appreciated on how we can proceed with this update requirement.
    Thanks a lot.

    Posted by: Louay Awwam  |  May 3, 2010 1:34:52 AM

  10. Hi - Great post. Thank you. I've successfully imported my Accounts and Contacts and am now trying to update my Accounts with the Primary Contact. I'm using Automatic Data Mapping and it looks like my import works, i.e. it says x records successfully imported but when I look at the Account information, the Primary Contact is still not updated. Have you run into this?
    Thanks very much.

    Posted by: Positively  |  Nov 23, 2010 1:54:51 PM

  11. Hi - Great info.
    I'm having the same problem as Positively during re-import of existing data using the "enrich" feature and auto mapping.

    Update works fine on everything but lookup fields. Is there something special that needs to be done in order to update lookup values?

    I've tried using the GUID as well as just the value and neither method works.

    Thanks

    Posted by: TD  |  Nov 24, 2010 11:43:45 AM

  12. Thank you for spending the time to ellaborate on your findings. As an IT guy I went straight for the obvious and mapped everything in, I found it strange that there were not direct references to records (ie GUID's or similar) but just went with the flow. Painful it proved until I found you post. Many thanks.

    Posted by: Tim Windsor  |  Apr 28, 2011 9:27:21 AM

  13. Tanks, very much

    Posted by: Tiago  |  Jul 12, 2011 2:44:53 PM

Post a Comment

  • *Required

Recent Posts


Categories

Archives

Links

Contact Us for a Quote, or Personalized Demonstrationof Microsoft Dynamics CRM for Your Business.

Contact Us