SharePoint Single-Value Lookup Column Index (Strange Behavior)

Today’s post continues my previous discussion of column index issues, but this time we focus solely on the indexing of a single-value lookup column and some of the strange behavior that indexing can produce when the row contains a blank value.

Keep in mind that lookup columns set to allow multiple selections are not supported by Microsoft in SharePoint 2010.

Diagnosis

I had a production list of “requests” with a single-value lookup column into another production list of department employees. The lookup column was thus used to assign a request to an owner. Now, you might be wondering why I would simply not just use a column of type person, and I would say to you that you’ve asked a perfectly valid question. The lookup table, if you will, allows me to track historical owners that have moved on from the department, without worrying about data loss as requests are updated. The lookup table also enables me to store additional information on the owners that can easily be retrieved from within workflows.

So with this production list approaching the dreaded 5,000 item list view threshold, I decided it was time to properly address column indexing. One of my changes included indexing this single-value lookup “Owner” column. Now, in most cases this would be perfectly fine, but keep in mind (as I initially did not), that new, incoming requests do not have an owner until assigned. This means that the now indexed lookup column has (albeit very few) blank values.

SharePoint will accept the column index and go about daily operations as if everything is fine, but you may notice something strange when using column filters in your list views. When you select (blanks) as the filter on this “Owner” column, you would normally expect to see all list items that do not have an owner assigned. But instead, the list view shows you nothing (as if to say there are no items with a NULL owner).

Solution

This behavior persists until you remove the column index. Instantly, the column filter (blanks) will work as you would expect. Add the column index back, and the issue is reproducible.

Strange, but nonetheless manageable, when you remember that column indexing simply does not play well with blank values. Lesson learned!