Sharepoint 2007 - When use Lookup Columns instead Choice Columns

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.

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.

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.

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.