Last verified: February 2019
Watch for my upcoming blog post where I’ll discuss best practises for creating any column in general. This is of particular importance with the new Modern sites and how much power is being placed in an Information Worker’s and Power User’s hands.
Do you need to create a column in SharePoint requiring a set of pre-defined values? If so, you have 3 column types to choose from: choice, lookup, or managed metadata.
DISCLAIMER: there is an additional column called ‘External Data’ that can be used to show values from an external list configured thru Business Connectivity Services. This is not covered in this blog post.
There are nuances to each one and it may not always be a clear-cut decision on when to use one over the other. This post covers some Q&A for each column type to help you decide which one is right for your scenario.
Applies to these versions of SharePoint. (2010/2013/2016/Online)
Column type definitions:
Where are the values defined?
| CHOICE | Entered directly in the column definition. |
| LOOKUP | Entered in a list on the same site as the column definition. |
| MANAGED METADATA | Entered as a set of managed terms. These are defined in the term store and can be added at the farm/tenant level as well as the site collection level. |
What permissions are required to update column values?
| CHOICE | To edit the values, you must edit the column. If it’s a list column, you require the Manage Lists permission of the list (this permission is in the Full Control, Design, and Edit permission levels). If it’s a site column, you require the Design or Full Control permission levels on the site.
If you’ve allowed for ‘Fill-in’ choices, any end user with contribute permission can add a new choice value for any given item however this will not update the available choice values in the column. |
| LOOKUP | You must have Contribute permission on the lookup list to add/update and delete values in the list. This is a great choice if you want a specific set of users to easily administer the lookup values without having to edit the column definition. |
| MANAGED METADATA | You must have Contributor permission to the Term set (Granted in the Term Store Management Tool). If the column is defined as open allowing for fill-in choices, contribute permission to the list is required to add new terms. |
Ease of setup and use?
| CHOICE | Simple to setup however users who are responsible for editing the values must know how to edit the column definition to add/change/delete the values. This can be tricky for some users. |
| LOOKUP | You must first define the list and then add the column referencing the list. Once setup, very easy to use. You can grant a group of administrative users contribute permission to administer the values in the list. |
| MANAGED METADATA | More complicated to setup as it requires a term group, term set and terms to be defined. UI is typically not as intuitive as a choice or lookup column for end-users to use. |
Can you allow multiple values?
| CHOICE | Yes, by selecting the Checkboxes (allow multiple selections) option. Once you do you can no longer group by the column, but can still filter on the value. This is the control rendered on the page:
Classic: Modern: (looks the same as Lookup) |
| LOOKUP | Yes, although once you do you will no longer be allowed to group by the column, but can still filter on the value. This is the control rendered on the page:
Classic: Modern: (looks the same as Choice) |
| MANAGED METADATA | Yes, although once you do you will no longer be allowed to group by the column, but can still filter on the value. The control appears the same whether you allow single or multiple values. The values are separated by a semicolon:
Classic: Modern: |
Can you set a default value?
| CHOICE | Yes. You can set the default value to any one choice value even if you allow multiple choices. You can default the value to blank even if the column is required. |
| LOOKUP | Not really. If the column is required, it will automatically default to the first item in the lookup list. This is why you often see the first alphabetical item in a lookup list set to something that will prompt the user to fill it in such as “~Select a region”. |
| MANAGED METADATA | Yes. You can optionally set one value as default. If you allow multiple values, you can only set one default. |
What happens when you change a value?
| CHOICE | If you change the value of an existing choice it will not change any items that have been created with the old value. However, once you edit the item you will be forced to change it to a new value if you did not allow for ‘fill-in’ values. |
| LOOKUP | To change an existing value you edit the value in the lookup list. This will change all items that have the old value to the new value. This is because the item stores the ID of the item in the lookup list and not the actual value. |
| MANAGED METADATA | You can deprecate the term so it can no longer be used however existing items that use the value remain intact. Note: once you edit the item you will be forced to change it to a new value. You can change the term name in the term store (default label) and this will change all old values to the new value. The hourly (by default) timer job ‘Taxonomy Update Scheduler’ must run to update the terms in all site collections consuming the Managed Metadata Service Application. Thru my testing, this process runs once per hour in O365 as well. |
What happens when you delete a value?
| CHOICE | If you remove a value as a valid choice you will be forced to change it to a valid value the next time you edit the item. |
| LOOKUP | You can enforce relationship behaviour at the column level to either restrict or cascade delete of items relating to a lookup list item as follows: If ‘Enforce relationship behaviour’ is unchecked and an item is deleted from the lookup list the value will be deleted out of any items referencing it. If ‘Enforce relationship behaviour’ is checked with ‘Restrict delete’ you will not be allowed to delete the item from the lookup list if there are any items referencing the lookup item. If ‘Enforce relationship behaviour’ is checked with ‘Cascade delete’ you WILL be allowed to delete the item from the lookup list which will in turn delete all items referencing the lookup item. BE VERY CAREFUL WITH THIS OPTION! |
| MANAGED METADATA | If the term is being used anywhere in the environment, you should deprecate the term rather than deleting it. This will prevent the term from being used on a go-forward basis while still keeping the old term associated with any items that had previously used it. If this is not desired, you should delete the term. Whether you deprecate or delete the term, you will be forced to remove the term the next time you edit any item using the deprecated/deleted term. The hourly (by default) Taxonomy Update Scheduler job will delete the term or deprecate the term in the site collection the next time it runs. Once the job has run, you will be unable to filter by any deleted term, only active and deprecated terms. |
Is there a performance difference?
[Update February 2019] Please refer to my post, List View Threshold Error… Watch your column types!, where I discuss the list view threshold error and column types. This “error” is a performance-based throttling mechanism and is in place for good reason. Microsoft continues to make positive gains in increasing the limit over time. Due to this, the guidance in both of my posts as it relates to performance and limits may change over time. I’ll update them if/when it does.
| CHOICE | Choice columns do not affect list query performance. You can add a column index on single-valued choice columns for improved performance if filtering on the column. You can group by a choice column if it has been indexed even if the list view exceeds 5000 items. (Modern view only) |
| LOOKUP | Be aware of the list view lookup threshold limit (default 12). This is the number of lookup, person/group, or workflow columns in a view. NOTE: what classifies as a lookup column in this threshold? Standard lookup columns, single-value managed metadata, multiple-value managed metadata. You can add a column index on lookup columns for improved performance if filtering on the column. Also, as of February 2019, list views exceeding 5000 items cannot be grouped by a lookup column without throwing the familiar ice-cream cone list view threshold error, even if the column has been indexed. This may change in the future, I will update this behavior if/when it does. |
| MANAGED METADATA | Same limit as described for LOOKUP. Both single and multiple-valued managed metadata columns are included in the threshold limit. You can improve performance for filtered lists/libraries by adding a column index on a managed metadata column. As of February 2019, list views exceeding 5000 items cannot be grouped by a managed metadata column without throwing the familiar ice-cream cone list view threshold error, event if the column has been indexed. This may change in the future, I will update this behavior if/when it does. |
Can you use it in a Content Organizer Rule for creating folders?
Note: for any of these column types to work in a routing rule, they MUST be configured as required with a single value.
| CHOICE | Yes, it will use the choice value as the folder name. |
| LOOKUP | Yes, but it will prepend the lookup value with the ID. Example: 1;North 2;South |
| MANAGED METADATA | Yes, and it will use the hierarchy of the term to create a folder structure. Example: if you have a term called ‘Hardware’ and a sub-term under it called ‘Printer’, the organizer rule will create a parent folder called ‘Hardware’, with a subfolder called ‘Printer’ to place the document in if ‘Printer’ was selected as the term. |
Can you group by this column type?
Note: you can group by any of these columns if you do NOT allow multiple values. For list views exceeding 5000 items, refer to the “Is there a performance different?” question above where I highlight some limitations with grouping by the 3 column types.
| CHOICE | Yes, it will use the choice value as the folder name. |
| LOOKUP | Yes, it will use the lookup value as the folder name. Note: you can also use any of the additional columns you have associated with this lookup column as a grouping column. In the table image at the start of this post, I’m also bringing into RegionHeadOffice. That means you can also group by that column. |
| MANAGED METADATA | Yes. If you chose to include the entire path as the display format, the full path hierarchy will be shown in the grouping. Example: if you have a term called ‘Hardware’ and a sub-term under it called ‘Printer’, the group name displayed will be Hardware: Printer. If instead you chose to include the term label, the group name displayed will be Printer. |
My Recommendations…
CHOICE: great to use when the options won’t change. (Eg. days of week, months of year, seasons, etc.) Also great option when doing up quick prototypes as these are the quickest columns to configure. For modern list views exceeding 5000 items where you want to group/filter by the column, choice is a good option.
LOOKUP: great when you have a large list of values and you want to delegate administration of the values to a select group of users. If you have additional columns on the lookup list you want to bring in, make sure to also include them (image). This is like having metadata on each of the lookup values! You can add them to views, group and filter by them too.
Be aware of the limitation of a lookup column in list views exceeding 5000 items if you intend on grouping by/filtering by the column.
MANAGED METADATA: this is by far the most sophisticated of the 3 column types. You can have a complex hierarchy to organize your terms. Great choice for enterprise-wide values requiring stricter governance. Be aware of the limitation of a managed metadata column in list views exceeding 5000 items if you intend on grouping by/filtering by the column.
All 3 column types provide the basic functionality of selecting from a set of predefined values. Key considerations when deciding which one to use are:
- Who will be responsible for updating the values?
- Is this a global list of values requiring stricter governance?
- Is their a complex hierarchy for the list of values?
- Will the values change and how often?
- How large is your list/library?
Thanks for reading.
-JCK