Choice Column or Lookup Column?

Every time I configure a new list or library, I have to make The Decision: do I use a Choice column or a Lookup column to add metadata? It may look as if there is little difference, but your decision can have consequences for maintenance, scaling, copying etc.
Below are my considerations for creating one or the other.

To avoid confusion between “Choice Column” and “Choices”, I will use the word “Values” for the “things” that your users will pick when they add a new item to the list or upload a new document to the library.

You use a Choice column when…

1. The Site Owner wants to be in control of the values. Only someone with Full Control on the list or library will be able to make changes to the values.
2. You want to allow Contributors to add new values during startup only. It may be wise to give Contributors the “Fill-in choice” option, because you may have forgotten some values. Over time, you can add the frequently used Fill-in choices to the “regular” values.
3. You want to keep the selected values in items/documents that have been added earlier. When a value in a Choice column is changed or removed, all items with this value will keep the old value, until you edit the item. For example: you want to keep the Year for past items, but you want to show only the current or future Years for new entries.
4. There is a chance you will re-use the list or library in another site than the current one. If you save the list or library as a template, the values will be included in the template.
5. You want to control the sort order of values as they appear to the Contributors.
6. You want to define a default value, such as the most frequently used value, i.e. current Year.
7. You want to display values as radio buttons. I like radio buttons because they give a quick overview to Contributors. On the other hand, they make your add/edit form longer so I only use them when the add/edit page is short.

You use a Lookup column when…

1. All Contributors should be able to add or edit values. This can be useful in recently created or very collaborative environments.
2. You want changes in values to be adjusted in the attached items or documents immediately. The values from a Lookup column are dynamic, unlike the values in a Choice column.
3. You use the same values for other lists or libraries in the same site. Using one central Lookup list saves time in setup and maintenance and creates more consistency.
4. You want to allow end users to filter content on a web page with radio buttons. A Lookup column is easier to work with when you connect web parts.
5. You have many values and allow multiple values. According to Michal Pisarek, a Choice column can only contain 256 characters, so there is a limitation in the number of values you can select when you allow multiple values. I have not come across this myself, and I do not know if this is still the case for SP2010 or SP2013, but I thought I’d share this.
6. You want to allow end users to see more information than just the value. The Lookup field is clickable, so when the Lookup list contains more columns, you can easily click-through to the complete information.
7. You want to show more than one column from your Lookup column, e.g. when you pick the Location Code in your lookup column, you can choose to display the City Name from the same item in the Lookup list, such as in the screenshot below. (This functionality is available from SP2010 onward)

You also see that the Location column is clickable. When you click on item Number 1, it opens this:

Of course you usually have to weigh the pros and cons of each column type and end up with having to make some allowances. I had already started on a comparison table when I found Susan Hanley’s post, including a good table with evaluation.

In a next post, I will share some tips to make “selecting values” as easy, low-maintenance and error-proof as possible.

Do you have other recommendations for making The Decision? Please share!

Image courtesy of m_bartosch / FreeDigitalPhotos.net