Choice Column or Lookup Column?

Choice or LookupEvery 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.

Values to pick from
These are the Values that end users can select when uploading a document or adding a new list item

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.


Choice Column Options
These are the options for a Choice column
Lookup Column Options
And these are the options for a Lookup column

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)


Lookup Column Example
Example of a Lookup Column with an additional field: You pick “Location” and specify that “City” is added as well. See also the screenshot above, where I specified that “City” has to be added.

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

You see the full details when you click on the Location.


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 /


Oh good – our upgrade budget has been cancelled!

So, there is a new version of SharePoint coming up, so you may be thinking about moving to the latest version. But there’s an economic crisis going on, so your budget may be under attack.
In any case, I expect some very interesting discussions will take place in many organizations. But if your management decides not to spend money at this time, please do not despair! There may be other opportunities to improve your intranet.

What was the situation?

About eighteen months after the launch of our SharePoint intranet we started with preparations for the new version. We attended a demo of the new features, discussed how these matched the needs of our users and made a preliminary time schedule.

Of course I was looking forward to the adrenaline that “a new intranet” brings, such as the creation of a communications plan, doing road shows and late-night functionality testing….not to mention the excitement of the actual launch day!
On the other hand, I knew very well that many users still had problems with SharePoint. Even our most ardent publishers of our previous, custom-built intranet  were struggling with content management in SharePoint.  Would it be a good idea to confront them so soon with even more new functionality?
My more technical colleagues did not share my fears.

What happened?

As soon as we had incorporated our ideas in our annual plan, and had distributed the draft planning to the rest of the team, we received a corporate message that all budgets for next year had been frozen. Not only did we have to cancel our plans, but our in-house developer/support team had to leave as well!  All support and development would be using the normal process that also applied to other systems: completing forms, waiting until someone else decided on the priority, defending your request and fingers crossed that our support partner would know how to maintain our SharePoint environment because that was not their expertise. (Not to mention the amount of customization we had done).
And if you’re used to a few wiz kids in your team, who understand you with half a word, and who have located, if not solved, almost every issue within 5 minutes, it is difficult to accept the bureaucratic route.
My more technical colleagues were devastated.

Secretly I was a little relieved, because the delay meant that our end users were getting more time to get used to the existing platform. So I tried to keep a positive spirit in the team.
We allowed everyone one day for expressing frustration and grumbling.
The next day we looked for positive aspects of the new situation. And guess what…there were many! Because of our focus on new technical developments, we had neglected some other aspects of intranet management. We could give attention to those aspects without any extra budget and with the remaining resources.

What did we do?

  • With the last part of our own development budget, our developers made some small application and modifications that we had never given priority before.
  • We made reprints of our Team Site manual with the remains of our promotion budget, and our designer created a new guide for the External Team Sites in her last weeks with us.
  • All technical specifications, use cases, process descriptions, configurations, special code and other technical and system stuff that I do not know much about, were collected from various sources, evaluated and stored in a Team Site, for transfer to the service partner.
  • We created a maintenance schedule to clean up empty or neglected Team Sites and other content types on a regular basis.  A Team Site Calendar was perfect to store frequency, process and communication for each content-type.
  • We replaced our labour-intensive monthly html-based newsletter by a blog.
  • We started creating personas. That would be taking a long time, so the longer we could think about those, the better.
  • We organized training for new users. We organized a classroom training for new employees on our location, and a Live Meeting session (live or recording) for everyone else. This has the unexpected benefit of getting to know our new employees from the start…and they knew us which was even better!
  • We created a central configuration team (our Business Solutions, who created the DMWS-Examples), to help the business use their SharePoint environment as good as possible.
  • We rewrote our annual plan in 3 days and shifted the focus from “technology” to “user experience”. It looked as professional as if it had been our plan all that time 🙂

My more technical colleagues finally saw the advantages of the situation. And the business was pleased with our training sessions and our Business Solutions Service.

What have we learned?

 Sometimes it is good to not just upgrade to the new version just “because you can”.  If you keep focussing on having the latest version of your intranet platform, you may never get around to doing other things to improve your intranet. If your budget gets cancelled, think how much time you will have to spend to improve your intranet in other ways!
Next to that, we learned to enjoy the challenge of introducing new activities on a low budget.

Have you experienced an unexpected budget constraint? How have you dealt with that?