Succeeding with(out) Multi-Select option sets in CRM 2011
As a system administrator in CRM 2011 you have access to many data types, and customization options. One of those is you can add option sets (shown below) that lets you pick one option from a set of options, but one limitation that is often encountered is when you want to create an option set that lets you select more than one option at a time, commonly known as a multi-select option set or multi-select pick list.
While there is no generic out of the box way to have multi-select option sets, there are at least four good ways to implement a solution.
Native Many to Many (N:N) relationships
Manual N:N relationships
Custom UI on an existing option set
Native Many to Many
With a native N: N relationship, you add a new entity instead of an option set for the options you want. Then using CRMs default UI you add a grid in your form (shown below) users can click Add Existing to add or remove items, effectively giving you a multi-select way to associate many options to one record. This is the best solution if you expect your available options to change a lot.
The draw backs of this solution are:
- More clicks to add items then a pick list
- You can’t show selected options in a view
- N: N relationships introduce complexity that may make your reports, charts, dashboards or workflows hard or impossible to configure as you want.
You can’t use the data import wizard to add related options or edit existing options
- By writing code, you can use CRMs SDK to write code to import N: N data though.
Manual N: N
The manual N: N is very similar to the native N: N. The main difference here is that instead of creating one N:N relationship between the entities, you add a 3rd entity and then a 1:N relationship from that entity to the other two you are linking (shown above). This approach allows you to use the data upload wizard to add and edit data, and gives you a friendly UI.
The drawbacks here are:
- You’re creating a new record every time you add an option to your entity; this could be an issues if you have lots of data.
- The UI isn’t quite as intuitive as native N: N, when you open a record from the grid view you are opening the linker (middle) entity not the main one, in some cases this could be a hassle for users.
Custom UI on existing Option set
The drawbacks here are:
- Its unsupported – there are chances that future rollups would make your code fail
- The data is stored in a unstructured way, this makes reporting, workflow and data imports complex or impossible in some scenarios.
- This is more advanced to implement, and requires custom development effort.
This is actually my favorite solution, which is commonly overlooked. Lots of times when you have a set of options to select from you only have 8-12 options. Adding 12 checkboxes right on your form is very easy, and you can see your choices in views, reports etc. with ease.
The main drawbacks here are:
- If you have lots (more than 15 or so) of options it may be hard to manage
- If you are adding or subtracting available options a lot this isn’t a good solution.
- You can’t reuse it, so if you have some options you want on different entities you would need to recreate the checkboxes on each one
Hopefully this gives you enough information to pick an approach that works best for your needs, if you need more help deciding or implementing we have lots of expertise with all of these options and are more than happy to help.