Microsoft Dynamics CRM has recently released November 2009 Service update for Microsoft Dynamics CRM online. One of the key features delivered as part of this release is enhanced Import Data Wizard. To familiarize yourself with the new capabilities of enhanced Import Data Wizard, follow the link Introducing-the-import-data-wizard. Some of the features like automatic customization, importing multiple files, creating new record types on the fly make Import Data Wizard extremely powerful.
One of the powerful features of Import Data Wizard are the automatic customizations of field width and list value, which will make customers life easier while importing data. Let us look at the highlights of Import Wizard that make importing data a pleasant experience.
Field Width Customization
Field width customization means customizing the length of Microsoft Dynamics CRM field on the fly based on the length of data in source file.
Often when user wants to bring data from some other system to Microsoft Dynamics CRM system, there are certain fields whose length in source system and target system varies. Let us say we have the name field in source system which maps to Account Name field. The default setting of length for "name" field of account record type in Microsoft Dynamics CRM is say "100" and maximum length of data in that column, among all rows in source file for field "name" is having length say "320". Length of the field needs customization in order to import these rows. There are two ways of customizing Microsoft Dynamics CRM to accommodate the source data:
1. Manually increase the width of field by going to customization area.
2. Use Import Data Wizard for automatic customizing of field length while importing data
Now we have understood what we mean by automatic customization of field width. In the following section, we will see the data types on which Import Data Wizard allows field width customizations.
Data Types Supported by Field Width Customizations
First a few definitions so that we are all talking about the same things:
- Text fields: – typically used to store short length information that can include letters, numbers and characters. Examples include names, addresses, job titles, etc. By default, these fields hold up to "x" characters but can hold significantly more. "x" can be different for different fields and record types and default value of "x" can be known by going to customization area.
- Ntext fields: – used to store larger amounts of text information for users such as descriptions. Ntext fields can hold up to 10,000 characters. The default setting for ntext field is 2,000 characters. In case data exceeds the max limit of field, then user will get the error while importing such record.
When import data wizard encounters a mismatch in the field lengths of source data and target field it deduces following:
1. Is the field customizable.
2. Finds the maximum length of the source data in respective column of given source file.
3. Checks whether the length found in step 2 is more than the default length of the field and is less than the maximum allowed length for the Text column (which is 4000).
If both conditions mentioned in Step 3 are satisfied, it updates the metadata of an attribute to the length found in step2 and changes the column length in database.
Field Width Customization for advanced transformations
Import Data Wizard applies field width customization even if some advanced transformations like Concatenation are applied on source data, and concatenated data is then mapped to some CRM field. If the data obtained after concatenation has length more than the default length of the field to which it is mapping, Import Data Wizard will apply field width customization in that case also.
Field width customization on logical fields
If you want to import data into some logical field whose data does not get stored in same record type, instead it is stored in some other record type in Microsoft Dynamics CRM then in those cases you need to customize the length of field where exactly data gets stored in Microsoft Dynamics CRM by using CRM application customization. Once you have customized the system you can use the Import Data wizard to import data of more length into logical fields also.
Text entered exceeds maximum length or "Generic SQL error"
Most frequently observed error is “Generic SQL error” and the most common cause of this failure is when field size of source record is more than the default setting of Microsoft Dynamics CRM and CRM is unable to customize the system automatically. Whenever such failure occurs, change the field width by using CRM application customization and then try to re-import the failed records.
Picklist customization means automatically customizing the list values in Microsoft Dynamics CRM system to accommodate the source picklist values. As you might already know that picklist values are stored in CRM as values (1, 2, 3 … etc) and not by the label. When user wants to import data into Microsoft Dynamics CRM for picklist fields, following possible cases can occur:
- The source file has some new picklist values, which does not exist in CRM and user wants to bring the records with this new value into CRM.
- The source file has some values that do not exist in CRM, instead of creating new picklist values in CRM user wants to map the certain picklist labels to CRM existing labels. This is out of scope for this blog and more information on how you can achieve this can be found at Picklist import.
Import Wizard can handle the picklist customization when user has chosen Map Automatically or has chosen to Use and existing data map while importing data. Let us see how Import Data Wizard handles the picklist customization in case user has chosen Map Automatically.
1. Identify all the unique values of the column in source file, which is mapping to picklist column.
2. Identifies which source values are having an exact string match with Microsoft Dynamics CRM fields’ picklist string values and can be imported without additional customization.
3. To create new picklist value for the unmatched source values, system verifies:
a. Whether the number of new picklist values which need to be created is less than 400 else system fails the entire import file.
b. If the target field is customizable. Pick list is not created unless the field is customizable and those records fail with appropriate error otherwise.
c. In case field is customizable then system creates new picklist values in CRM with the same label as that specified in source unique value.
Let us take an example to understand what happens when Import Data Wizard finds a column in source file, which is mapping to picklist type field in MSCRM.
Is a column which is mapping to picklist field of CRM “prefferedcontactmethodcode”
Let us assume we want to import the following data file.
User maps the source file to Account record type in MSCRM by going to Map Record Types page of Import Data Wizard. After user has mapped the record type to which the source file maps user can map the columns to fields on Map Fields page of Import Data Wizard. The name column of the source file will be mapping to Account Name of account record type and PrefferedContactMethod to Preferred Method of Contact as shown below.
Preferred Method of Contact field has some unique values in CRM that can be seen by navigating to:
Settings –> Customizations->Customize Entities->Entity Form->Attributes-> Attribute page
As shown above the unique Picklist values in CRM does not have value Mobile in it. Once the source file has been imported using Import Data Wizard, the new picklist value Mobile will be added to Preferred Method of Contact field. We can see that Account4 which was having new picklist value Mobile got imported with Mobile added to list as shown below.
However, if a map is being used instead of automatic mapping then the system customizes pick lists on the basis of map. Handling of picklist customization in case user has chosen the Use an existing data map option is same as that of automatic mapping except that in this case in addition to the existing values which exist in CRM here we also need to resolve the values on the basis of any mapping which are defined in map. The picklist transformations go in this order:
1. Picklist values which exist in map.
2. Existing picklist values in CRM.
3. Any new picklist values which need to be created as part of picklist customization.
So here you go! With the new Avatar of enhanced Import Data Wizard you need not bother about the field width and picklist customizations while importing.