10 tips for Choice and Lookup columns

Columns-10TipsIn an earlier post I showed the differences between Choice and Lookup columns. Usually it is not 100% clear which Column to select and you will need to weigh the pros and cons.

I have collected some tips for Choice and Lookup columns from my own experience, to help you make the choice and to avoid the issues I have faced by selecting the wrong one :-). All tips are for both Column types, unless stated otherwise.

Setting up your columns.

1. Give your Lookup List a clear name and/or a good description.
I usually describe it as “Picklist for …. list/library. Do not delete”. Others recommend to call it “Lookup-Name”. As long as you make it clear that it is an important list, even if it looks like “just one column with some words in it”. 🙂

LookupList description
This is how I mark a Lookup List.

2. Never remove permissions from the Lookup List. If you do that, end users will see an empty dropdown. Of course it may be sensible to make the Lookup list read-only, if you do not want end users to make changes. On the other hand, only few people understand Lookup lists, and/or are adventurous enough to make a change.

3. A Document Library as your Lookup Column has severe limitations.
I would SO like to recommend this as an alternative to attaching documents to list items, but…
a.  You can only select Title, Created, Modified, ID, Version or Copy Source as values. If you use the default SharePoint setup, where Title is not mandatory and you mainly use the name, this will not work for you.  😦
b. If you click on a Document Lookup, you will open the item, rather than the document itself.  😦

Opening a lookup to a document.
This is how the Lookup to the Document opens – you open the item, but not the document itself – that takes another click.

4. Use a Number Column if you want to use the values in a (numerical) Calculated Column.
Numbers in Choice or Lookup Column will be treated as text.
You can use the values of a Choice Column in a (text-based) Calculated Column, but not those of a Lookup Column.

Adding the Values

5. I usually add “(please select)” as the default value, rather than leave it blank. This is:

  • a neutral value
  • a gentle reminder that a proper value has to be selected
  • a reliable filter, e.g. to check which items have not been completed properly. I do not like filtering on an empty field.

6. Try to limit the number of columns.
It can be very tempting to structure your list or library to the hilt, but if you have to add a zillion metadata and end up with one item per category, you have overdone it. Remember that the default metadata (title, created/modified and created/modified by) also provide useful information to help you find what is what.
I have sometimes flattened down a structure when some, but not all values, needed some more details. It does not look pretty but it sorts and filters well. SharePointMike has also written a good post about this topic.

Flat structure
Example of flattening a structure. When you have only a few items that need further breakdown, this can be a nice way to keep things simple.

7. Copy and paste values into Notepad, before you copy them into the Choice column or Lookup List, if you are copying values, currently in Word or Excel.
This removes code that can influence the display of your values, e.g. unexpected spaces. Especially spaces created by hitting the Tab button in Excel can play up, e.g. in Datasheet view.

8. Keep your Lookup Lists < 2000 items to avoid performance issues.
But let’s face it, picking one of 2000 items does not sound good anyway :-).

Filtering and Sorting.

9. Remember to change all views that are filtered on a certain value, when you change a value.  
Unfortunately views are not dynamic, so you will need to make manual changes.

10. Add numbers to the values if you need a different sort order than A-Z (1-10) or or Z-A (10-1).
E.g. 1.High 2.Medium, 3. Low.  The Choice Column can show your preferred order when adding or editing items, but sorting and grouping can only be done alphabetically or numerically.
Remember the SharePoint way of sorting…start with 01 if you have more than 10 items or your sort order result will not be as expected.

Do you have other tips to share? Please do!

Asparagus picture is my own – they are in season!

You may also like:

7 tips for copying from Excel into a Datasheet View

My View on Views


3 thoughts on “10 tips for Choice and Lookup columns

  1. val May 16, 2014 / 2:51 am

    Oh … Asparagus ‘tips’ . I just got that! (thanks for the article)

    • Ellen van Aken May 16, 2014 / 12:18 pm

      Hi Val, exactly 10 asparagus tips! It is always a challenge to find a suitable picture 🙂

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