With the 2011 beta out it’s time to get blogging again. Over the coming weeks I will post on new features that catch my fancy. Here’s #1!….
The Import Wizard has had a nice upgrade in CRM 2011. Previously, I only used this for very simple imports, typically to populate master data into simple custom entities. When customers wanted to try out the wizard the approach I would always recommend was to start with an Advanced Find query that includes all of the columns you will want to import, run that query and then export the results out to Excel. This gives you a nice excel template for you to then key/paste your source data into, for later import. Microsoft took this approach a bit further in 2011 and you can now download Excel Templates from CRM that give you not only perfectly named columns but also comprehensive validation IN EXCEL!
Download Template for Import option available on the Entity Ribbon
Excel import template including column names, comments and validation for pick list fields
The second area of improvement I’m liking is in the Import Mapping. The big limitation in CRM 4 lay with importing data sets that included relationships to other records. In v4 you could do this but your source data had to provide the “Name” of the related record and that value had to resolve to a single record. e.g. if importing Contacts and you wanted your Contacts to be linked to Accounts you had to provide the name of the Account on the Contact record and that name had to exist in CRM on one, and only one, Account. In many cases this level of uniqueness just doesn’t exist in real world CRM data. Often I would want to map on an alternative key. Often I would have a reliable unique IDs in my data sets as my data was coming from a legacy relational database that enforced uniqueness. 2011 gives us a nice solution now, you can map to a related entity using any field you choose. So now I can load my Accounts into CRM with their ‘Legacy Unique ID’ stashed in a field somewhere and then when importing my Contacts to their Parent Account using the unique IDs provided by the legacy system.
Import mapping using a lookup reference to a specific field
Import Mapping in 2011 now also offers us a completely new way of actually building out your CRM schema. As you map your source file you can indicate that your data represents a new entity in CRM and you can ask the import wizard to create the entity for you. Further, as you map the fields you can indicate that you want new fields created in CRM, and this includes the creation of string, date, integer, float and even option set and lookup fields! The wizard keeps things pretty simple so you will likely need to adjust the schema definition after the fact to tidy things up – e.g. my float field was created with a precision of 10 decimal places – but that’s easy enough to do. I would love to see someone build a schema editor to support rapid tweaking (post import) of these sorts of properties in a grid view. I haven’t used this feature in a real world scenario yet to learn the gotchas but there’s potential here for at the least some extremely rapid prototyping, especially on those projects where you are migrating from a legacy system.
Creating a new CRM entity on the fly during data import
To see how this all works in practice, here’s a quick example. I have a source file containing data about Phones. I want to represent this in CRM as a new “Phone” entity and I want the entity to have a many-to-one relationship to Account. My source data refers to Account records by the attribute Account Number:
Source data in Excel
Step 1 – Run the Import Wizard:
Step 2 – Map the source data to a CRM entity, chose Create New:
Step 3 – Map your fields, clicking Create New to create new fields on the fly:
Step 4 – To relate the Phone data to the Account entity select Create New for the Manufacturer field, chose Account as the Related Record Type and chose Account Number as the Referred Field:
And you’re done! Import away. When the import has completed you should review the definiton of the entity and attributes created and edit as required, and then configure the forms and views for the entity.
In summary, CRM 2011 gives us:
- Can import an Excel file saved as Excel 2003 XML (i.e. can avoid issues with commas)
- When mapping to a CRM entity, can indicate that the data represents a new Entity, and CRM will create the entity.
- When mapping fields, can indicate that a new field is required in CRM, and CRM will create the new strong, int, float, pick list, date, lookup field
- When mapping to a lookup field can indicate which field on the related entity your source value maps to (e.g. you can specify the unique key – e.g. can use an alternative foreign key)
- Note: CRM will create entity and fields. But will not put fields on the form.
- Can downloaded import templates from CRM – this gives you an Excel file with columns and validation rules based on the CRM meta data, required fields, pick lists, etc.
Note: The Data Migration Manager from CRM 4.0 has been discontinued in 2011
GT // CRM 