SharePoint: The balance between choice and lookup

SharePoint Lists have incredibly handy options called choice and lookup. I’m going to oversimplify each of them because the point of this article is not to explain what they are. That will come later ;). Choice columns are pre-defined sets of values from where the user can pick one or more values. These are defined at the creation of the column but can change them after. Lookup columns are links to other lists where you can fetch information and display to the user any information present at that list. Both are extremely useful, but there’s a balance between choice and lookup that I would like to explore.

The Issue

First, let’s define the problem. Well, not a problem, but a strategy with advantages and disadvantages. First, it’s important to say that they are quite distinct and provide different features, but I want to focus on the problem of displaying information to the user.

Let’s say that you have a list with Musicians and want that, one of the fields, be the type of music that he/she plays (you thought I was going to use the old People and Job example, didn’t you? 😉 ).

To achieve this, you have two ways of doing it:

Using a Choice Column

Let’s build the previous example. First, let’s create a choice column.

And add options.

Finally, let’s add some items.

It works fine, and you have all your options. But let’s say that we want to add R.E.M. an Alternative band and we don’t have those options. There are two ways to do this:

1. Allow users to add values. Allow users to add anything that can pollute the choices with similar or misspelled entries. But if you want to, this pick “Can add values manually” when creating the list or give permissions to edit the list.
2. Add yourself before inserting it. It’s painful each time a user wants to add missing choices; they need to call someone to add it to the list, and only after that, they can add the entry.

It’s handy if you know all the values beforehand but a problem if you don’t.

Lookup Column

Now let’s try the lookup column.

First, let’s add the style list:

And now, go back to the artist list and add the lookup column.

Pick the Lookup and press save:

Pick and add values:

They look the same. Let’s think about the previous problem. Someone wants to add a value that doesn’t exist on the list; they can go to the “style” list and add the missing information. The issue of pollution still exists, but at least we solve one of having to give permissions to access the list’s configuration. It’s easier to teach someone to insert values in another list than having them mucking around in the List’s settings.

There’s one advantage of using this kind of column. You can add multiple values from the lookup column. For example, let’s say that we want to show the description also in the list, we just need to pick it from the list:

And the information will show on the artist list.

The comparison

As you’ve seen, each option has some advantages and disadvantages.

1. The choice allows you to have a dropdown menu, radio buttons, or checkboxes in case of multiple items. You can’t define this in the lookup columns.

2. In the lookup columns, you can display multiple items that are automatically updated when you pick a new option. Choice columns can’t do this, so you need to create them individually.
3. There’s no limitation on how many Choice columns you can create, but you can only generate 12 lookup columns. On-premises SharePoint allows you to change this limit, but you can’t do it on SharePoint Online.
4. Since lookup columns are links to lists, you can use automation on the source list when some value changes, something that you can’t do when a new Choice value is defined.

When to use or the other

As you’ve seen, neither is perfect but both super useful. So you need to think a little when to use one or the other.

Choice columns are fantastic when you want to limit the choices and have consistent data, meaning that you don’t change it often or never. Since there’s no limitation, you can set a lot of these columns and keep the information uniform. Also, if you only need a simple string with description, there’s no need to keep on defining lists to have lookup columns. But, if you want to have the same choices in another list, you need to create a column and copy the option. If the options change, you need to remember to replicate it in all the other lists. If you only have reference data that is relevant for one list, Choice columns are perfect.

Lookup columns are useful for reference data since they link to lists so that you can have the same column in multiple lists and, when the information changes, it’s propagated automatically. But since they are links to other tables, they need to be limited, and you can quickly hit a wall when you have a lot of reference data. Also, if you need automation when choices change, you need to use lookup columns and target them. Finally, if you want to display more information than just a simple string, you need to use lookup columns to view that information.

Final thoughts

In the end, as in many things in life, the answer to “what is better?” is “it depends.” As a rule of thumb, think of Choice columns unique information for the list that never changes and Lookup columns as complex information that can be shared and changes a lot.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Microsoft SharePoint-related articles here.

Photo by Ayesh Rathnayake on Unsplash