SharePoint Lookup Column Based on a Calculated Column Woes

I’ve been working on trying to improve the performance of some DVWPs I wrote about a year ago as part of an application that I inherrited from others. (Was that tactful enough?) One of the things that I’ve been trying to do is to filter on the LookupId of a Lookup column, but I couldn’t for the life of me get it to work. I’ve done this dozens of times, but in this case I was getting nowhere.

I finally figured it out. The Lookup column is based on a Calculated column, which is itself built out of two strings which are concatenated. Here’s a simple example.  If the reference list looks like this:

ID	ColumnA	ColumnB	CalcColumn
1	Blue	Cow	Blue – Cow
2	Red	Sheep	Red – Sheep

where CalcColumn is calculated as =[ColumnA]&” – “&[ColumnB], and you create a Lookup column in another list which uses CalcColumn for its values, you’ll see this in the values:

string;#Blue – Cow
string;#Red – Sheep

where you would expect:

1;#Blue – Cow
2;#Red – Sheep

Therefore, you can’t get at the LookupId of the Lookup column because it isn’t available.  (See my post on Using the ID Value for a Lookup Column for how this *ought* to work.)

Moral of the story: Don’t use a calculated column for your Lookup column source.  Now on to some list rebuilding…