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…

1 Comments

  1. I was messing around with this today and I just used a workflow that fires off on new item creation and set a hidden column with the values i wanted in it.

    This is sort of a weak workaround since it duplicates data but it allowed me to parse the value like I wanted to.

    This applies more to the post you made for Using the ID value for a lookup column.

    Reply

Have a thought or opinion?