Microsoft has added a new data type field, the MultiSelect Option Set. This now gives CRM System Admins four ways to define a field that allows users to select from a defined list of values. This article identifies when you might want to use each of these data types, and compares them over several relevant features.
I have long been a strong believer in using Lookups over option sets, but there are significant limitations with lookups that might warrant using a MultiSelect Option Set instead. This table compares and contrasts these three (or four) data types now available in Microsoft Dynamics 365, version 9.
In brief, the four data types and the Usage Summary for each are:
- Option Set: Define a limited number of choices; users can select 1 from the list
- Global Option Set: Define a limited number of choices; usable across multiple fields, across multiple entities. Users can select 1 from the list
- MultiSelect Option Set: Users can select one or more values from a pre-defined list
- Lookup: User can select one value; can also add a new value dynamically
|Data Type / Feature||Option Set||Global Option Set||MultiSelect Option Set||Lookup||Comments|
|Who can add new options?||CRM System Admin||CRM System Admin||CRM System Admin||Users||The ability of users to create new values can be the criterion that indicates using a lookup field.|
|Select one option among 1-7 choices||Built for this purpose||Built for this purpose||Can select one, as well as many||UI less effective||If only one option among many can be set at any one time, use an option set.|
|Select one option among 100s of choices||UI unwieldy if choices many||UI unwieldy if choices many||Searchable option set makes this a viable option||Built for this purpose||A former huge advantage for lookups, now MultiSelect Option Sets offer a similar User experience.|
|Select more than one options from a list||Not possible||Not possible||Built for this purpose||Not possible||Finally, you can select more than one option from a list.|
|Removing historical options||Removes values from data records||Removes values from data records||Removes values from data records||Deactivate record; old data preserved||A major benefit for Lookup fields: options no longer applicable are still preserved in the transaction records.|
|Can sort by field value||Click on column; or define view||Click on column; or define view||No||Click on column; or define view|
|Can define a condition in business rules||Yes||Yes||No||Yes|
|Can update via business rules or workflows||Yes||Yes||No||Yes|
|Can bulk edit||Yes||Yes||No||Usually||Some lookup values (special CRM fields) cannot be updated.|
|Conditions using this field can be moved in a solution||Yes||Yes||Yes||No||Not being able to specify lookup fields as conditions might warrant using an option set.*|
|* see Querying Using Data: ClickDimensions Survey Questions|
Recommendations by Business Case
Use a lookup
If you want users to be able to dynamically add new field values, use a lookup.
Do not use a lookup
Use a Lookup or a MultiSelect Option Set
If you have dozens of possible values, historically lookups were the right call (more than about a dozen option set values gets unwieldy). Now that you can search a MultiSelect option set, selecting one option from a MultiSelect Option Set is at least as quick as selecting one from a lookup field. Being able to select multiple results opens up a number of design possibilities. If you wanted to select multiple values using a lookup, you’d have to create multiple fields, each a lookup to the reference entity.
Use Global Option Sets sparingly
I tend to shy away from global option sets, and only use them if the SAME FIELD, used for the SAME PURPOSE, exists on multiple entities. Often, if the option set has a small set of values, and several different fields currently need the same set of values, it is very tempting to use a global option set for all of those fields. The problem arises if the business changes (but THAT never happens, rights?), and fields that previously had similar values no longer share the same values. There is no good solution other than:
- Creating a new option set field, with the new set of values
- Creating a workflow, or use bulk edits, to convert the data from the OLD field to the NEW field.
- DELETING the option set field that used the global option set, that no longer has the values this fields needs
Of course, this is typically complicated by the forms, views, business rules, and workflows you may have built that used the field you now need to delete.
The moral: save yourself a future hassle, and only use global option sets for the same field, across entities.
Do the choices vary by user?
Do the choices vary by data?
If you have a “hierarchical option” condition, where one set of field values depend on the value in another field, (think if you select a state, then the cities available vary) then the filtered lookup is the clear best choice.
Do the choices vary by system process?
The lookup can be filtered by CRM views, using OOB system admin customization. The ability to place the same field on the form multiple times (each lookup can have a different filter) results in a system where different users can see a different set of field values.
Does a choice kick off automation?
For status fields and other fields that might trigger workflow or other business processes, I prefer using option sets over lookups. The reason is that if you trigger processes off a value, you don’t want users adding a value that doesn’t have any automation tied to it. The logic is: if you have to add some code, or a workflow step, if you add a new option, then the system admin might as well add it to the option set, and then modify the code.
You can also package an option set in a solution, and move the solution from a test to a production environment without having to rework the query criteria. (For more info on this, see Querying Using Data: ClickDimensions Survey Questions)
Microsoft’s addition of a new datatype gives system administrators another very useful tool in their configuration toolbox. As with all new shiny tools, I predict a rash of new MultiSelect Option Set fields will begin to appear in CRM solutions, but there are definitely negatives you need to consider before pulling that trigger.