This post is on when should we use Option set (Pick list field) and when we should use Lookup field in Dynamics CRM when it comes
to using them for making choices, i.e., the end users need to be given a choice of options or a selection.
Working on different projects across different clients, geographies, consultants and developers- I still notice that
people have a tendency to choose this types differently Some example are:
· Category
· Sub-Category
· Territory
· County
· Town
· Country
The list can keep growing.
So, my blog’s main focus is to understand when to pick what:
· Option set Fields:
Ø This should be picked when the number of option set values are smaller (typically up to 10 values).
Ø This should be used when there are not many changes required to the values or new additions to the possible
values is not needed by the users.
Ø Global option sets provide additional benefit of using common type of picklist across different entities.
Ø From user perspective, this should be chosen if end user group intends to pick a set of drop-down values.
· Lookup Fields:
Ø This should be picked when the number of choice values are larger (typically greater than 10 values).
Ø This should be used when there are many changes required to the values or new additions to the
possible values is needed by the users.
Ø Global option sets provide additional benefit of using common type of picklist across different entities.
Ø From user perspective, this should be chosen if end user group intends to pick a set of recently chosen
drop-down values and if not found users are ok to select a search bar to find the intended value.
Ø This should be also user if user intends to search the possible values using wild card searches.
Ø From Relationship perspective, Lookup is a relationship between two entities.
This should be a consideration during field creation.
Ø In Various Grid views, is users intend to be able to click through and reach the option value and the
option has additional details than just display text.
For e.g., Country might have Country code, Description etc.
Some more considerations from a great MVP Jukka Niiranen:
Hope it helps and Happy CRMing!
MSDYNAMICSBLOG BY DEEPESH 
Some further things to consider while making the choice:
* If the CRM system needs to support multiple languages, option sets will give you the ability to translate the value labels, while achieving the same functionality with lookups would require custom code
* Lookups offer the ability to create dependent fields without custom code
* Filtering the available values for different user groups could be achieved with standard CRM security model when using lookups
Thanks Jukka, I will include them shortly 🙂
Reblogged this on and commented:
Reblogging… Thanks!
Nice summary of differences.
I love to be able to devolve the admin of the system out to the business managers, and lookups work well for that if they need to maintain and change the available options over time. Just give them appropriate rights to let them work with the parent records (including create and write) and off they go. Give other users Read and Append To only.
Also consider deprecated options, where old data needs to retain a value but new records should not be allowed to use it. With lookups you just disable the relevant record. With an Option set you end up having to move it to the bottom of the list and add a “ZZZ Do Not Use…” prefix, and maybe configure Business Rules to warn users if they make a poor choice. Ugly.
Lookup tip: I often find that users have a “short code” for items in a list, and then lookup entities work really well to have the main label as the primary field and the short code as a second field and enable both for quick find. As an example, I can then type “UK” and hit tab and the lookup is filled in with “United Kingdom” or “UAE” to get “United Arab Emirates”. If you don’t have the shortcode you have to get as far as “United K” before it can be uniquely recognised.
Option sets don’t have this and don’t work so well with type-and-tab style of data entry.
Option sets for a small number of categories work well when using that as a chart category and you can be sure of the sort order being in numerical value order. Lookups can end up in strange orders, or always alphabetical, unless you add a custom “sort order” field and make sure to build this into the chart XML. Much more work.
Incidentally, I saw a post today on Dynamic communities that highlighted a bug – in online update 1, option sets are being sorted by numerical value, not by configured display order any more. This is a very annoying breaking change, especially for customers who have old, deprecated options shoved to the bottom of the list. Vote to fix on Connect:
https://connect.microsoft.com/dynamicssuggestions/Feedback/Details/1802495
Great thoughts 🙂