EndUserSharePoint.com: Can I use a calculated column as a lookup value?
The question of the day comes from Toby in the UK:
Does anyone know how to get it so I can have a Lookup field that uses a calculated field in another table? My requirements is to create a concatenated name from first name and last name in Contacts (not the Full name that it comes with) and use that as a lookup in another table.
The problem manifests itself like this: lets say you have a table, like Contacts. If you add your own field to the table, say ‘newfield’ and make that a calculated value, in this example I use “=CONCATENATE([first name], “ “, [last name])”. Having done all of that I then go to a new table and want to include “newfield” and have it be a LOOKUP on “newfield” from my Contacts table. Trouble is, whenever I try to do this, although ‘newfield’ definitely exists it refuses to show as something that can be used in the LOOKUP.
Toby – after looking at your calculation value, I think that might be the problem. My value for the calculation is =[FirstName]&” “&[LastName] and that seems to work fine. Let me know how it works out for you. I have provided a short demo screencast.
on
Toby and I had a discussion through email. It turns out he is using SharePoint 2003 and the functionality to expose calculated columns as lookup values doesn’t appear to be available.
Hello!
Can you tell me whether the opposite of this is possible? I want to create a calculated field that uses the value of a lookup field.
Thank you
Sergio – Have you tried what I showed in the demo?
Hi!
I have a question regarding filename column, how can you make a reference with a calculated column to a filename column.
Please help!
thank you
Hi Mark,
I am trying to use a lookup column within a calculated column. When I choose the Calculated Column option, it does not put my lookup columns in the insert column table. Do you have any tricks for this?
Thanks,
Andrew
Andrew – Check the location of the calculated column. A column used as a lookup must reside in the current site or it must be defined as a site column. This is usually the problem… define it as a site column so it can be exposed to all sites in the site collection.
Mark
Mark – The lookup column resides in the current site, but I defined it as a site column for a test. Either way when I try to create a calculated column, the lookup is still not present as a choice. Any other thoughts?
Thanks,
Andrew
Andrew – I would need to see exactly what you are doing. Is your site viewable to the public?
It is not possible to use a lookup column as part of a formula in a calculated field. When deciding to use a Choice or a Lookup, we always consider whether the column will be needed in a calculation (among other things).
Joe Spadea
Thanks Joe. After running some tests, I agree. The lookup column is never exposed to the calculation list. You can’t even enter it by hand.
Mark
I used a lookup table to concatenate first name and last name like your example.
In the “Edit in Datasheet” view, is it possible to display the lookup list in a column without the “string;#” appearing before each item in the list? It makes searching for items a little harder as I have close to 1,000 names in my list.
Thank you! Thank you! Thank you! You save my life!
Hi,
I’m having a similar problem to Joy’s. I have a Calculated column that concatenates two text fields and a look up column to this calculated column. While this works great when i select an item from the look up using the edit item option, I am unable to select items from the look up in the datasheet view. Instead, I get the following error: “An error 080004005 occured. No further info was provided”. Also, the items appear in the drop down with “string#” preceding it. Any help would be much appreciated since it would save a lot of time to be able to edit these in the datasheet.
Re getting the value from a calculated field, it’s this simple, i.e. no “string#”
SPFieldCalculated cf = (SPFieldCalculated)item.Fields["myfield"];
Console.WriteLine(”Value of myfield = ” + cf.GetFieldValueForEdit(item["myfield"]);
Dolly – And where, exactly, are you supposed to put that? — Mark
You’d put that in your backend code, i.e. your custom web part.
I gather you’re not doing that.
Sorry I didn’t really read your comment but was having the same problem with “string#” when trying to use a calculated field programmatically so thought I post to help anybody in my boat…
This is a great feature as is the lookup. However, what’s really missing from the Native SP List (IMHO):
- Unique Constraint rule on columns
- A GUI way to join Lists. Or at atleast a way to have a calculated column with some real cross list logic, like say, give me this column from the first matching row on this other list where my column matches that column.
- Regular expression validators on columns. For Example, only allow phone numbers in this column.
If MOSS had full support for these three features in the SPList, then maybe we could seriously take a bite out of all those Excel and Access applications that are floating around.
And in a perfect world, there would be an easy to use, but rich SQL interface dialog box for the views builder.
Thanks.
Joy, or anyone else, any suggestions on how to eliminate the string;# prefix that shows up in the datasheet view of a list that uses a calculated column in a lookup field?
Thanks
Dean
I have lookups as per your demo. These are at a top level site and they are used in sub-sites. I then need to take the lookup field and concaternate it with another field at the sub-site level. However the lookup fields are not available for concaternation. Any suggestions please!!
Suppose you want to concatenate forename and surname without leaving any space in between.
Example: Jane Norwood (field A) to janenorwood (field B)
1. Create a calculated field (field C) where you look for the ” ” character in field A.
=SEARCH(” “,[field A],1)
This returns the position of the ” ” character.
2. Create another calculated field (field B) where the ” ” character is replaced by some unprintable value (for example, CHAR(7)) and then do a CLEAN to get rid of that value.
=LOWER(CLEAN(REPLACE([field A],[field C],1,CHAR(7))))
The “string;#” appearing in a lookup column based on a calculated value in the datasheet view can be solved by requesting a hotfix from Microsoft:
http://support.microsoft.com/kb/948952
HOWEVER, I am still running into another annoying issue, where if the value is left blank in the datasheet view, and the user modifies another field, I get a validation error:
“The text entered for isn’t an item from the list. Select an item from the list, or enter text that matches one of the listed items.”
I love/hate SharePoint :)