At some point in every Microsoft Dynamics 365 CE project, there comes a time where you find yourself with a requirement for a picklist and wonder, should I create this as an option set or a lookup?
While some of my colleagues at Sikich have strong preferences for lookups over option sets and vice versa, I don’t. I have a set of guidelines to follow when making my selection between option sets and lookups and generally they’ve served me well.
Dynamics 365 CE Option Sets
Option sets are usually preferred by end users since they require fewer clicks to record information. But option sets can quickly become frustrating if the list contains more than 10-12 options. The Dynamics 365 CE interface shows a few options, leaving users to scroll through the rest to find what they’re looking for.
Users can skip to values in the list by typing the first few characters, but fuzzy searches for option set values are not available.
Option sets are great if you have a list of 10-12 values though. You can translate the values to multiple languages. Values are transported to different environments through a solution deployment, which is convenient if you’re referencing option set values in workflows; more on this later. And, through the use of global option sets, values can be used across different entities.
There are downsides to option sets too. If you want to add a new value, most organizations will require you to go through IT rather than give, even small groups of end users, access to Power Apps where option sets are configured. Removing values from option sets also becomes tricky. When you remove a value from an option set, any record that had that value selected will now show a null value and you’ve lost all the historical information.
Dynamics 365 CE Lookups
If filtered lists are a must have, lookups do offer this functionality using native configuration. You can create two entities, a relationship between the two of them, and then a lookup field on the form of one of them, you can use the related records filtering, to create the dependency.
Another benefit to lookups is you can remove a value from the list by deactivating the record. When you deactivate instead of delete, the value no longer appears in the list of options and all the records that had that value in the lookup field, retain that information. Remember, with option sets we lost the information when the value was removed.
Lookups also allow users to create new values without involving IT. Since the options available in a lookup field are records of that entity, users with the security privilege to create records on this entity can add new values, Power Apps not required. With that said, if you want users to have the ability to create new values for your lookup fields, I would be very selective with the users who are given the create security privilege.
While lookups offer functionality that we often desire, they do have their limitations. Selecting a value in an option set requires more clicks. If you value doesn’t show up in the first 10 options, you need to lookup more records. Once the lookup window pop-up is displayed you must scroll or search for your value.
Lookups do support searches with wild card characters which makes finding what you’re looking for a little easier.
If you’re using lookups in a check condition step in a workflow, and you need to deploy your configuration to another environment, you will have to migrate the configuration data. Lookups are records so they have GUIDs, the workflow references the GUID, not the text that makes up the name of the record. To do this, you will have to use a third-party tool such as the Configuration Migration tool, KingswaySoft, or Data Transporter from the XRM Toolbox.
There are clearly advantages to both Dynamics 365 CE option sets and lookups. Hopefully this blog has helped clear up what those benefits are. If you have additional questions, reach out to the Sikich team at any time!