Importing Multi-Select Picklist Data in Dynamics 365
Multi-select picklist data was one of the most requested features in the Dynamics community and was introduced in Dynamics 365 from ver. 9.0
The issue
Although introduced, importing multi-select picklist data can be a problem using the CRM Import Tool. Below is the sample CSV file with a multi-select column named ‘Hobbies’.
Although it as 2 distinct values in the ‘Hobbies’ column (data in the multi-select field is available in the field separated by a semicolon), the CRM Import Tool does not consider the same. Refer screenshot below.
Importing the above will result in CRM automatically creating a new option set value called ‘Running; Swimming’ which is not the intended purpose.
The good news
The CRM Import Tool supports both CREATE and UPDATE of records. Although the tool does not support multi-select picklist data during record creation, it does support multi-select picklist data during the update of records.
Workaround steps
- On the CSV file which you plan to import (for record creation) add a new column (e.g.: Unique Id) and populate it with a sequential number value.
- Create a similar field in CRM (datatype: whole number) on the entity.
While mapping the columns during Data Import ensure the map the ‘Unique ID’ column with the newly created field.
Do the map of the Multi-select field. (We will be handling this after the import) - Now proceed with the Import. Once successfully imported, you should have all the records created in CRM with the ‘Unique ID’ field.
- Include the ‘Unique ID’ field and the Multi-select field in a new view and sort it based on the ‘Unique ID’. Now export the view to Excel as a ‘Static worksheet’.
- You now have 2 files:
- Original import file
- Exported data file from CRM
- Ensure that the data in both files have been sorted by the Unique ID field. Carefully copy the multi-select column data from the original file and paste it over the multi-select column on the file exported from CRM. (Do not overwrite the header column)
- Simply import back the updated file using the CRM Import Tool. CRM will identify it as an updated job and update back all the records in CRM with the multi-select values.
Alternatively, you could even use the ‘Open in Excel Online’ feature to do the same.
Limitations of Multi Select pick Lists
- The default value for a multi-select picklist cannot be set from within Customizations; however, you could use a custom code approach to set a default value.
- You could define any number of options for a multi-select picklist, however, only a maximum 150 options can be selected for a multi-select picklist field
- Although a multi-select picklist is available as a ‘Trigger’ for workflows, it cannot be used within a ‘Check Condition’ on a Step.
- Bulk-Edit does not support multi-select picklist fields.
- If you plan to use the CRM SDK to create/update multi-select picklist values, ensure that you are using the ver. 9 or above of the SDK assemblies.
More details on how to interact with multi-select picklist via CRM SDK here