Comparing Lookup Fields to Global, Regular, and MultiSelect Option Sets

Comparing Lookup Fields to Global, Regular, & MultiSelect Option Sets

In Expert Tips & AdviceLeave a Comment

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 filter the list of available items Using JavaScript code Using JavaScript code Unknown Filtered Lookup Configuration Filtered lookups provide easier filtering of a list.
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

If you need to use the field value in a condition (in a workflow, business rule, or JavaScript), you should not use a lookup. The reason is that a condition involving a lookup uses the GUID value of the data record as the criteria. (To see more on this topic, read our article Querying Using Data: ClickDimensions Survey Questions) Typically, the same lookup value in a DEV org versus a PROD org will have a different GUID value. Unless you can use a third party tool like Scribe to copy the GUIDs across environments, any condition using a lookup field will have to be edited in each environment.

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?

If one set of users can see different options in a list of values, you can either filter the lookup using OOB CRM customization tools, or you can write some JavaScript to affect the option set.

Configuring the filtered lookup is much easier than writing JavaScript, so if you can, use the filtered lookup.

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)

Conclusion

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.

Leave a Comment