Subscribe via RSS Feed Connect with me on LinkedIn

SharePoint 2007: When to use look up columns instead of the choice column in SharePoint

[ 11 ] June 6, 2010 |

Introduction

There are a number of ways to store options for a field that requires a group of pre-selected options, a drop down list as it were. I always thought that defining a column of the type choice is the way to go but recently discovered that in a couple of areas that creating a list and then using that as a look up is actually a better solution.

When to use a Choice Column

You should use a choice column, pictured below, in the following circumstances:

1) Only site administrators should be able to add/remove/edit options

2) You don’t have that many options to choose from (maximum ten) and users will usually only select at most 5 options.

3) The options rarely change

This is the usual way to go in a SharePoint environment but it can cause many issues. For starters a choice column can only store 255 characters (SharePoint in built field limit) so if your users are choosing 10 options each of 26 characters (see below) then they wont be able to. This is because the choice fied actually stores all the selected options as a semi-colon delimited string, which also causes some issues with the Data View Web Part when attempting to render the values.

Field Limit Error

Field Limit Error

Another reason is that if you have many, many options the interface will look horrendous. In my case we had something like 250 possible options for 6 different field which meant that a user would scroll themselves silly when trying to enter metadata.

Finally the options would change often, new options would have to be added but we didn’t want to give these people administrator privileges or have them harass IT for a simple task… so what was the answer? Use a lookup column instead.

The Lookup Column

The lookup column is simply another type of column within SharePoint that looks up a list to get its options. Seems simple enough but it has certain advantages that in this case saved us a whole lot of time

To implement a look up column in SharePoint you need to do two simple things things:
1) Create a list that will store the data that you want to look up and
2) Create the look up column to be used.

Lookup Column Advantages

The LookUp column has the following characteristics which were very useful in our case:

Can be easily managed without special permissions

If you want to give your users the power to add, edit and delete options then since the values for the lookup are stored in a list they can have normal access rights without giving them the special permissions required to manged fields in Sites or Site Collections.

Can store an unlimited amount of options

The Lookup list, unlike a choice field, can hold an unlimited amount of options that are selected if you choose the “Allow Multiple Values” and “Allow unlimited length in document libraries” options. This is a really good way to get around the pesky 255 character limit that you encounter whilst using a choice field.

Look Up Options

Clean Interface

This is one thing that the Look Up list really prevails in that instead of using a series of select boxes, which gets unwieldly  when you have many options, the Look Up list is rendered as two lists where the user can select values, much cleaner! You can also change the size of the boxes pretty easily using JQuery or SharePoint Designer, a good link is here.

Look Up List Rendering

Look Up List Rendering

Conclusion

So as you can see there are many ways to skin the cat in SharePoint and the choice between a look up list and a normal choice field can produce some interesting results and challenges. I hope that this article gave you some insights into how to solve these issues.

Tags: ,

Category: General

About Michal Pisarek: Michal Pisarek is the founder of Dynamic Owl Consulting and a Microsoft SharePoint MVP. View author profile.

Comments (11)

Trackback URL | Comments RSS Feed

  1. Hubert says:

    ..and the question is: how to convert my choice column into a lookup column in WSS 3.0?

  2. elorg says:

    Excellent! I haven’t had to work with large listing of choices yet, so I hadn’t thought of the character limitation. That’s good to know. :)

  3. Michael,

    One of the primary reasons for using a lookup column, as opposed to a choice column, is the ability to expose the list information across an entire site collection.

    As in your example, create the lookup list at the top level of the site collection. As a best practice, I preface the name with “Lookup-” and then the list name. It makes it easy to find when you’ve got a bunch of lookups in one place.

    Use the look up list to populate a site column at the site collection level. Because this is set at the top of the site collection, that column is now available anywhere within the site collection!

    When you drilldown a little deeper in this concept, you can see that each item in a lookup list can have other information related to it. A common lookup list is “Lookup-ProjectName”, which holds the names of all the projects being worked on. Adding columns such as “Project Manager”, “PM Email”, “Project Description”, makes it so that when a project name is clicked on when used within a list or library, the project information is shown.

    Lookup lists are very powerful when designed with the full implications in mind.

    Nice article.
    Mark

  4. minchik says:

    There is a problem. If your list is too long, you can’t see full content of this column when trying to set file properties in Microsoft Office. It download only first part of this column. Do you have any soluton for that?

  5. Magnus says:

    There’s one big problem with lookup columns: if you set it to “required”, sharepoint will force the first value in the list to be set as the value, in a blank form. This is erroneous behaviour, since the user actually never will be required to enter anything (sharepoint does it for you, and if you don’t notice this column, the value entered could be faulty). Bad!

  6. Magnus says:

    …if you chose the column to be non-required, you’ll get a “(none)” value as default, but this doesn’t exist when the column is required.

    So what is the point of setting a lookup column to “required”? This is a pure bug.

  7. MIYA says:

    Hi,

    I would like to know how to cascade a parent field to a child which is lookup and multiple values.

    Thx!

  8. KN says:

    [quote]…if you chose the column to be non-required, you’ll get a “(none)” value as default, but this doesn’t exist when the column is required.

    So what is the point of setting a lookup column to “required”? This is a pure bug.[/]

    Can’t find the source right now but Ive seen this mentioned elsewhere and the poster established that the bug was dependant on the number of items in the list (having more or less than a certain number causes the bug).

  9. KC says:

    I’m taking over a site and the site was built with a lot of choice columns instead of lookups. That said, I now have a requirement to change one of the choices to another name but I can’t quite remember if it’s okay to just change the name. I think this action is okay. And what I was told not to do is insert a new value because “behind the scenes” SharePoint has given an “ID” to each of the choices and if you insert a new choice in the middle of the existing choices this will change the metadata? True/false?

  10. […] 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 […]

Leave a Reply




If you want a picture to show with your comment, go get a Gravatar.