Last verified: February 2019
This post describes a fundamental building block of SharePoint Information Architecture… columns.
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
This is a great post. Thanks for sharing!
Awesome post for anyone who is wondering when to use what. I would like to ask, and something that might benefit more people.
When using the different choices, how do indexes and search behaves for them.
Thank you!
Hi Ignacio,
You can add a column index on any of the 3 types which is something I’d recommend. SharePoint search does not leverage any column indexes, it uses its own optimized search algorithm to retrieve search results. As far as I know there is no difference between the 3 types as far as indexing and performance are concerned.
Joanne
Thank you Joanne for the fast response! Keep up the good work, you now have a new fan!
One other thing that I have found – am I incorrect about this?
With a lookup, I can pull in other attributes of the value selected into my views, where with Managed Metadata, there is not a way to do this.
For example, let’s say I have a hierarchy of State:City:Address and for each address I have other info such as square footage.
If I have the hierarchy in Manage Metadata, I can select Address, and it will see that it is part of State and City, but I cannot group, sort, or filter the list by, say, City, and I cannot have a column in a view that pulls in the Square Footage of the building (address). Is this correct?
If I have a lookup list, I can have fields for State, City, Address (Title), and Square Footage. If I use that list as a lookup and select an Address, in my views, I will also have Address:State, Address:City, and Address:Square Footage as separate fields that I can use for grouping, sorting, and filtering.
Hi Joe, You are correct about this. The lookup field and its additional lookup values will allow you to be able to filter, sort, etc on them. You cannot have an additional piece of ancillary metadata associated with a managed term however.
I will add this to my post as an additional difference.
Thanks,
Joanne K
Great straight forward post on the three options. Lookup columns have become a preference when related lists add a very simple way to enhance the view form.
Hi Joanne! Great post! Just one quick thing as I was reading through it…where you say “To edit the values, you must edit the column. To do this you require Full Control permission of the list if it’s a list column”. By Full control, do you mean, the “Manage Lists – Create and delete lists, add or remove columns in a list, and add or remove public views of a list” permission for the site?
Hi Doug! You are correct! You now get that permission level with the out-of-the-box Full Control, Design, and Edit permissions. I’ll update the post to reflect that. Good catch!
Thanks Joanne, excellent and informative.
A very nice breakdown. One other possible consideration is where the column values need to be reused across multiple site collections, say in a Modern hub architecture. In that case managed metadata with its single cross site collection configuration becomes a logical choice.
Hi Kimo,
Excellent point! I will definitely highlight that in my summary. Thanks for sharing, appreciate it! 🙂
-JCK
Joanne, I enjoyed this post very much. I am a novice SharePoint 365 builder and have a metadata list that I manage. We receive offers to fill positions, normally one at a time; so, one position type, one line entry of data. I have received offers that have two or more position types at two or more locations. As each position type has its own labor category, bill rate, location code, etc., each position or positions with work to be done at different locations, each position type and position at different location each get one line entry of data. I have been asked if we could use LOOKUP to allow all the different position types at one location to one line. I’m not seeing where I can do this unless I do a similar lookup for all other columns that would have a different value; however, when filtering or searching would the additional positions, locations and bill rates retain a relationship with the corresponding values in the other LOOKUP columns?
Hi Mark,
I apologize for the tardiness of this response. I’m having a hard time understanding your example. Please feel free to reach out to me via email (joannecklein@nexnovus.com) and we can have a better conversation about it. If you’ve already resolved this problem, then awesome!! 🙂
-JCK
Very good !
Thank you for “clean” overview of choice, lookup and metadata.
Any ideas what is the difference between these when used inside the SharePoint workflows?
Hi Zane, I’ve blogged about these in another post “Upsate SharePoint columns in Microsoft Flow”.
Joanne
Thanks for suggestion. Any idea of what is the difference between these fields inside SharePoint workflows created with SharePoint Designer 2010/2013?
Hesitant to use Microsoft Flow yet.
Hi Zane, I have stepped away from SP Designer workflows for several years now… However, I know all of these field types are available and workable inside of those types of workflows. I think a good Google search should do the trick. Sorry I can’t help you with this.
-JCK
Given the Content Type Syndication Hub, isn’t the Choice column a viable option to use for tenant wide columns? Especially in combination with Site Design and user initiated site creation.
Hope to hear from you!
Hi Martijn,
I would not use both the Content Type Syndication Hub AND Site Scripts/Site Designs together. The modern story for the ‘Content Type Syndication Hub’ is not there yet particularly how it will play with Site Designs. That said, you certainly could use a choice column in a Site Design and in that way it is a viable “tenant-wide” column.
-JCK
Really helpful this post for in depth understand about the Choice, Lookup and Managed Metadata
Hi, Can you please provide an official Microsoft link to where it states the limitation for filtering/grouping over lists with more than 5,000 items that use Lookup or Managed Metadata fields vs. Choice fields? I cannot find it anywhere in their SharePoint Online limitations documentation.
Hi Yuri,
Although it doesn’t reference Choice specifically, refer to this link in the ‘Edit the list view’ section. https://support.office.com/en-us/article/manage-large-lists-and-libraries-in-sharepoint-b8588dae-9387-48c2-9248-c24122f07c59
-JCK
Hi Joanne,
Great post to layout the difference.
However, I have one concern whit the choice you made to favoritise managed metadata above choice columns.
When using these columns in documents behaviour is not the same.
Choice fields can be updated from within the text whereas managed metadata need to be changed from the properties screen of the document.
Geert
Great point!! To be clear this is a fairly old post that I should probably look at again for my current recommendation. I appreciate the point you’ve made… thank you! 🙏😊
-joannecklein@nexnovus.com
Thanks for comming back to me. Indeed this post is ‘old’ but was the only one I found with clear explenation!!
Anyhow I think it is still valid but my recommendation goes to use columns in environments where flexibility and automation is key.
Hi Geert, I 100% agree. Where it makes sense to have columns (workflows, retention, filtering/sorting), then use them. That is what their intended purpose is. I still use custom columns in SharePoint as a regular IA tool. I will review the post and put a ‘last reviewed’ date on it. I believe the majority of the post is still relevant however I need to make sure all of the changes Microsoft has rolled out does not change the nuances of each of the column data type capabilities.
Thanks for your support and the feedback – much appreciated!
-JCK
I find your post very useful as there’s not much guidance about having something similar to a classic site column in the new flat architecture. If there’s any update, it would be great to have it recommended 😉
One question: what about solving the problem by using an External Data Column in SPO?
Thank you,
Emiliano
Hi Emiliano – I’m glad you found the post useful. I plan on updating/reviewing the post to ensure it reflects the current (modern) state of SharePoint; however the overall guidance does remain largely the same. I have not used an external data column in SPO and I would likely try to stay away from them just for long-term supportability in the modern world.
-JCK
Hi Joanne, this is helpful. I have a question – do you know any way to expose the alias (or other label) of a metadata term as a separate column in a document library? For example, an account name (managed metadata) has a 3 digit code (alias or label). In the document library, we want to have two columns in the view, one is account name (where the end user populates the metadata column using either the name or number) and the other column is the 3 digit code for reference. I thought by creating a list with the account name (managed metadata) and then create another column and manually populate the 3 digit code (which is the alias), I could pull the column over into the doc library as a lookup column. However, when I create a lookup column to lookup the List with the metadata column, the metadata column is not an option to Lookup. I can see why that would not be recommended (just use the metadata column in the library directly), but wishing to figure out how to expose the alias without recreating the term completely in the list.
Hello Carolyn,
I don’t know of an out of the box way of doing this however can’t say I’ve tried to do this. Likely a Power Automate Flow could do something to populate the 3-digit code column. You could ask this question in the SharePoint group in Microsoft’s Tech Community for broader reach. Hope this helped.
-joannecklein@nexnovus.com
Thanks – yes, I might have to go that route! Appreciate the quick reply.