Choice, Lookup or Managed Metadata?

 

 

Circle10This post is part of my beginner blog series on SharePoint Information Architecture.

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: their 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 in the Managed Metadata Service Application.

 


What permissions are required to update column values?

CHOICE 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 or Full Control of the site if it’s a site column. 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:MultiChoice
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:MultiLookup
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:MultiMM

 


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.
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 are not apt to 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 if you want to delegate administration of the values to a select group of users.

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.


THE END

All 3 column types provide the basic functionality of selecting from a set of pre-defined 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.

Advertisements

4 thoughts on “Choice, Lookup or Managed Metadata?

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s