Choice, Lookup or Managed Metadata?

Reading Time: 8 minutes

Last verified: February 2018

Circle10This 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:

ChoiceLookupMMVisual


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:MultiChoice

Modern: (looks the same as Lookup)ChoiceMultiSelect

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:MultiLookup

Modern: (looks the same as Choice)LookupMultiSelect

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:MultiMM

Modern:ManagedMetadataMultiSelectManagedMetadataMultiSelectv2


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:EnforceRelationship2

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?

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.
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.
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.

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.

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.

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.

AdditionalColumns

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.


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:

  1. Who will be responsible for updating the values?
  2. Is this a global list of values requiring stricter governance?
  3. Is their a complex hierarchy for the list of values?
  4. Will the values change and how often?

Thanks for reading.

-JCK

17 comments

  1. 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!

    1. 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

  2. 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.

    1. 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

  3. 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.

  4. 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?

    1. 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!

  5. 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.

    1. Hi Kimo,
      Excellent point! I will definitely highlight that in my summary. Thanks for sharing, appreciate it! 🙂
      -JCK

  6. 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?

    1. 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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.