Supporters of End User
Web

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

 
Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories: 
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.

Comments

Mark Miller

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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.

Posted 29-Feb-2008 by Mark Miller
Sergio

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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

Posted 08-Apr-2008 by Sergio
Mark Miller

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

Sergio - Have you tried what I showed in the demo?

Posted 08-Apr-2008 by Mark Miller
Robert

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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

Posted 19-Apr-2008 by Robert
Andrew

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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

Posted 07-May-2008 by Andrew
Mark Miller

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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

Posted 07-May-2008 by Mark Miller
Andrew

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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

Posted 07-May-2008 by Andrew
Mark Miller

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

Andrew - I would need to see exactly what you are doing. Is your site viewable to the public?

Posted 07-May-2008 by Mark Miller
Joe Spadea

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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

Posted 04-Jun-2008 by Joe Spadea
Mark Miller

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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

Posted 04-Jun-2008 by Mark Miller
Joy

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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.

Posted 06-Sep-2008 by Joy
Shirley

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

Thank you! Thank you! Thank you! You save my life!

Posted 16-Apr-2009 by Shirley
Brooke

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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.

Posted 01-May-2009 by Brooke
Dolly

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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"]);

Posted 05-May-2009 by Dolly
EndUserSharePoint

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

Dolly - And where, exactly, are you supposed to put that? -- Mark

Posted 05-May-2009 by EndUserSharePoint
Dolly

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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

Posted 05-May-2009 by Dolly
JCNET

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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.

Posted 02-Jun-2009 by JCNET
Dean

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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

Posted 05-Jun-2009 by Dean
Lynne

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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!!

Posted 10-Jun-2009 by Lynne
Concatenate a string (two words) with no space in between

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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))))

Posted 22-Jul-2009 by Concatenate a string (two words) with no space in between
Jason Lochan

EndUserSharePoint.com: Can I use a calculated column as a lookup value?

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 :)

Posted 07-Aug-2009 by Jason Lochan

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: