Pick list (Choice) or Entity Lookup - Which is best?

 Pick list (Choice) or Entity Lookup - Which is best?

Let’s say you have a form and you want to add a list of options that the user needs to check, what's the best way to do it?  A  simple Option set of choices or a look up to an entity record?

In general, the answer is going to be 'Use an option set (choice field), but like always there are exceptions to the rule. Let me start by giving some examples of the type of things that get put in to lists for a user to select from:

• Country
• Customer Category
• Region
• Support level
• Product Line
• Preferred Contact Method
• Project

 Before I go ahead and tell you which, in my option are best suited to a simple choice field and which are suited to being choice or Entity Lookups, let me first talk about the two types.

Entity as a single field list

Rather than reinventing the wheel, I've plagiarised the following explanation about look up entities from the internet.

Creating a new entity in D365 (Dynamics 365) can involve some overhead, mainly related to the need to manage the new entity's data and relationships within the system, potentially impacting performance and storage. Here's a more detailed breakdown of the overhead involved:

1. Database Storage and Management:

• New Table:

Each entity in D365 maps to a table in the underlying database, so creating a new entity means adding a new table and potentially associated indexes.

• Data Storage:

The new entity will require space for storing data, which can add to the overall database size.

• Metadata:

D365 needs to store metadata about the new entity, including its fields, relationships, and business rules. 

2. Performance Impact:

• Querying:

Queries that involve the new entity might take longer to execute, especially if the entity is large or has complex relationships with other entities.

• Data Synchronization:

If the new entity is part of a data synchronization process, the synchronization might take longer to complete.

• Business Process Flows:

If the new entity is used in business process flows or workflows, the execution of those flows might be impacted. 

3. Security and Access Control:

• Security Roles:

You'll need to define security roles to control access to the new entity and its data.

• Record Permissions:

You'll need to define permissions at the record level to control which users can create, read, update, and delete specific records in the new entity. 

4. Customisation and Development:

• Plugin Development: If the new entity requires custom functionality, you might need to develop plugins or workflows to handle it.
• Form Design: You'll need to design forms to allow users to interact with the new entity.
• Reporting: You might need to create new reports to display data from the new entity. 

Creating a Choice Field

Even a choice field (Option set) is not without overhead.  Lets again use the internet to define what’s involved with creating an option set.

1. Storage Overhead:

• Option Set Data:

Each option set, whether global or local, requires storage space to hold the list of options and their associated values. 

• Field Storage:

Each field using an option set also needs to store the selected option's value (which is an integer representing the option's position in the list). 

• Global Option Sets:

Global option sets, while reusable, add to the overall storage footprint as they are stored separately from the entities they are used in. 

2. Management Overhead:

• Option Set Updates:

Modifying option sets (adding, removing, or renaming options) can require careful planning and potentially impact existing data if not done correctly. 

• Data Integrity:

Deleting an option from a global option set can lead to data loss or inconsistencies if fields still reference that deleted option.

• Complexity:

Managing multiple option sets, especially global ones, can become complex, especially if they are not well-organized and documented.

• Synchronization:

Maintaining multiple option sets that share the same values can be difficult and lead to errors, especially when mapping entity fields in a one-to-many entity relationship 

3. Potential Performance Impacts:

• Large Option Sets:

Very large option sets can slow down form loading and data entry, especially on mobile devices. 

• Complex Queries:

Retrieving data based on option set values can be less efficient than querying simple text or number fields. 

• Metadata Retrieval:

Retrieving metadata information, including option set values, can create unnecessary overhead if not done efficiently. 

4. Additional Considerations:

• Global Option Sets:

Use global option sets judiciously, as they can simplify management but also increase storage and management overhead. 

• Local Option Sets:

For options specific to an entity, local option sets can be a better choice.

So Which one is best?  It’s still not clear

As you can see from the above definitions, both Option Sets and Look up Entities have overhead and potential performance impacts, so which one is right?  The answer “it depends”. How glib, I hear you say.  Well, lets put some context and add some insights of how you can make the decision on which one to use. Taking our example lists from earlier let’s look give some insight as to which might be the better one to select.

 

Item	Entity Look Up	Option Set
Country	If you have multiple pieces of information associated with the item and you want to be able access that too, or if you have a list that is constantly changing with items being added or removed	When it’s a single piece of information and stable, i.e. it doesn’t change (or changes very infrequently) and you do not want users to be able to add or remove values from the list of available options.
Customer Category
Region
Support level
Product Line
Preferred Contact Method
Project

To try and put this into a little more context let’s use the Country option as an example for deciding whether to use it as a look up entity or as an Option set.

Country

Here, we need a little more information to make a good decision.

When to use an Option Set:

If you just want a list of country names from which to choose, then an option set works well.  Why?  Because country names don’t change (very often) and we don’t tend to create or remove countries in the real world, so it’s a stable list. We can create it as a global Option set and then add it as an instantiated (created from a single global one) list on each form we need to use it. It becomes a field on the record and is easy to use for record grouping or reporting.

When to use a lookup entity.

Let’s assume, that you want to also include additional information about the country, for example, its GDP, its population, its ISO country code, its financial status etc.  This is the perfect time to create an entity record to hold the list.  Now you can add it to a form, can display the additional data on quick view cards on the form too.  So for example, you could, on your country project page, show the Country GDP and Population as a quick view card when you select the country.