SharePoint “Lookup” Columns

As you may know, you can include a “Lookup” column in your SharePoint 2007 lists which, in effect, creates a reference to another SharePoint list.  This can be used, for example, if you have an address list and want to ensure that your states or provinces are all spelled correctly—just create a “StatesProvinces” list, fill it with the properly-named states and provinces, then add a reference to it in your address list. The user experience is a combo box containing the items in the “StatesProvinces” list, and they can select the one they want without having to type it themselves. [Note that you can configure SharePoint to allow multiple items from the referenced list to be selected, but that’s not what I’m focusing on in this post.]

In order to add a lookup column to your list, you first must have created the other list that you are going to reference.  You don’t actually need data in it, yet, but it does need to be created.  Fair enough.

Next, after choosing the list, you must choose which column in that other list you want to display to the user.  So in this example, you may want to have the “Title” column display to the user.

Once you save everything and add data to the “StatesProvinces” list, you have a workable solution.

But what if you want to have a workflow run against this address list which has the reference to the “StatesProvinces” list?  This is where you need to know a little bit about the inner workings of lookup columns in SharePoint.

If you know anything about database development, you probably know about the concept of a “foreign key”. In short, a foreign key resides in a table that is related to another table.  Let’s call the first table the “Child” table, and the second table the “Parent” table.  So the Child table would hold the foreign key to the Parent table.

What does a foreign key look like? Simple: it is the primary key of the Parent. [A primary key uniquely identifies a row in the database table.] It is usually a numeric type.

The same concept applies to a lookup column in SharePoint, but the execution is slightly different. You see, the actual value stored in the lookup column is the ID of the referenced list, but what is displayed to the user is the configured column from that list.

Back to my workflow scenario: if you want to manipulate lookup columns from a SharePoint workflow, and I’m talking specifically about using SharePoint Designer to create the workflows, you need to keep the foreign key concept in mind. As a test of this, do the following:

1. Create a workflow and attach it to a SharePoint list which contains a lookup column.
2. Create two variables in the workflow, one of type “String” and one of type “List Item ID”.
3. Set both variables to the value of the lookup column

If you examine the contents of each variable (by logging it to the workflow history) you will see that the String variable contains the value of the configured column to display (in our example, the “Title” column), and the List Item ID variable would have a number (which corresponds to the ID column of the referenced list).

If you are going to change the value in the Lookup column via a workflow, you must set its List Item ID (number), not the displayed value(text).  You will wonder why your workflow isn’t working correctly if you don’t follow this advice, especially if you are new to creating SharePoint workflows using SharePoint Designer.

I hope I have helped someone out there with this advice.