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.

How Can You Tell if You Found a Valid List ID?

I’m currently working with several different custom SharePoint lists.  Some of the lists have references to other lists, via a “Lookup” column.  For example, a book might have a reference back to the library it came from.

Sometimes I only know the name of the library, and I want to be able to set a reference to it on the book’s list.  So I retrieve the library’s ID using the name of the library.  SharePoint Designer dutifully warns me that I’m not looking up the value using a unique ID, and that it will just give me the first one it finds.  I’m OK with that, since I control the names and I know that they’re all unique.

But I’m only human, and sometimes I don’t have a library name that exists in my library list.

In those cases, when I log the contents of the list ID variable into which I placed the data, it shows me “0”.  [That’s the number zero, not to be confused with a lower-case “o”.  I’m considering changing the font for this blog… -sj]

As it turns out, this is the way you can tell if you have a valid list ID.  Valid IDs start at 1, I presume, and invalid IDs are all zero.

[It’s easy to tell if you have a bad reference to another list when you retrieve string values, since they show up in log messages as “????”.]

Workflow Comparisons Aren’t Always Accurate

I have a SharePoint workflow, developed with SharePoint Designer, which is supposed to compare values in the list it is attached to against values in another list, and copy the other list’s values if they are different.  I kept noticing, via a workflow history log message, that the workflow was copying values every time it ran, even though in another log message for the same workflow instance it showed me that all the values were the same.

As it turned out, I was comparing a string value to a list ID.  Even though they both printed in the log message exactly the same (and so were “equal” in my eyes), SharePoint saw them as different values.

The fix was to copy the list ID value to a string variable, and then compare the two values as strings.

[As a side note, what made this worse was that I had a second workflow which would run when anything was changed on the list.  It would then update a value in the list, which would then cause the buggy workflow described above to run again.  For the astute readers, this meant that the two workflows attached to the same list would cause each other to run infinitely.  It pretty much brought the site to a halt!]