Often times when designing a CRM environment there is the need to put a field on an entity form that allows the user to choose from a predefined list of values (in contrast to writing free text in a string-field). Examples for this are a list of countries, a list of hobbies, a list of titles etc.
There are generally two ways to go here – an optionset (a.k.a. “Picklist”, “Combobox”, “Dropdown”, …) or a lookup to a new custom entity. So which one is the better fit?
Whenever I discuss this decision with a customer, there are a bunch of questions that have to be asked and can pretty quickly decide which way to go:
Do you care if the design is good? No? → Optionset
It is done quicker, you don’t need to worry about user roles or such things.
However, of course you should care if the design is good, so please read on.
Do you use different languages in the system? → Optionset
The values of an optionset can be translated to accommodate the UI language of the user.
If you use a lookup the name of the custom entity will be displayed, and since that is stored as data (as opposed to meta-data), you won’t be able to store translations for it.
Do you want to (easily) have multiple values selected simultanously? → (Multiselect-)Optionset
Since this datatype is available we can cover this problem as well with optionsets. It can still be achieved with lookups (as it was done in “the old days”), but it is more complex, including a subgrid and a m:n intersection entity.
(For Multiselects) Do you want to make it a required field? → Optionset
An optionset can be marked as “business required”, meaning you can not save the record without selecting a value for this field. When you have a m:n-relationship with other entities in order to achieve a multiselect using entities, you do not have this option.
(Keep in mind, if you only need single-select, a lookup is perfectly fine too!)
Do you have a lot of possible values? → Lookup
With a generic optionset, having more than ~100 values makes it exhausting to move through, while a lookup offers you a search field with wildcards. Keep in mind, the newer multiselect-optionset can handle this easier (since it is providing a search field as well), but I still would not recommend it for a big number of values, and you might not always want to allow multiselect in your use case.
Do you want do save additional information to the options? → Lookup
If you have a list of countries, you might want to save the ISO-code for that country too, to also let the User search for that. Or add a responsible person to a list of departments of your company and so on. This can be done easily by just adding a field to a custom entity, while with an optionset you only have a label and a number, and the latter can’t even be seen by the user.
Do you want to have a dynamic set of values? → Lookup
To add or a remove an option from an optionset you need to have customizing rights to the system, which not a lot of people should have.
For a custom entity you can give diverse access via user roles, so you could define some key users who might be able to add values (e.g. you have a set of titles like “Dr.”, “MBA”, “Prof.”, and a user wants to add a new possible title).
Do you want to model a hierarchy (“dependent lists”) ? → Lookup
Say you want to store the info in which branch of business an account is practicing in, and some branches have sub-branches (or even sub-sub-branches…). In CRM you can model these as different entities and define a hierarchy between them (by linking the sub branch “Insurance” to the main branch “Finance”). Now you can have one lookup for picking your mainbranch, and a second lookup can then offer all corresponding subbranches. CRM lookups can do this out of the box.
This can also be achieved with optionsets, but only with custom code, and it is not flexible for new values (if you don’t want to touch your code every time you add some optionset values)
Do you want to restrict access to some of the values? → Lookup
With a lookup you can manage ownership rights to the records of your custom entity, making it possible to prevent some of the values from being seen or selected by some users.
Again, this can theoretically be done with optionsets as well, but only with custom code.
There are some small other factors, like the usage of global optionsets vs local ones, or integration topics with other systems and so on, but this should cover the main topics.