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…
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.