SharePoint: Lookup a Lookup Column in a List

11
votes

I have three lists that look like this:

Initiatives
-----------
Initiative (single line text)

Themes
------
Theme      (single line text)
Initiative (Lookup from Initiatives:Initiative)
Points     (number)

Features
--------
Feature    (single line text)
Theme      (Lookup from Themes:Theme)
Points     (Lookup from Themes:Points)     # <- This here works fine.
Initiative (Lookup from Themes:Initiative) # <- This here is busted and can't do.

See that last line there... Initiative (Lookup from Themes:Initiative) <- that's me trying to get the initiative associated with the Theme. I've tried about a bazillion different things and just cannot figure out how to do this.

Is it even possible? If so, how?

FWIW - I am using SharePoint 2010 and can use anything from the web tools to SharePoint Designer. I'm an admin on the SharePoint Site but not on the Server.

5
sharepointsharepoint-2010lookup
Okay, so on stumbling upon this MS article on creating list relationships. I have come to the conclusion that lookups are not allowed as secondary columns in a lookup. So much for that. But what I want to do isn't THAT crazy. There must be someone out there who has done something like this to normalize their SharePoint list structure. Anyone? – Bruce P. Henry
It's crazy that you can't do this. – Batman
@BruceP.Henry I know that the post is old, but still I've got no solution for this issue, this is the main drawback that I have with sharepoint. Meanwhile have you found a solution or a workaround? – Luther

5 Answers

4
votes

You cannot do that, what you need to do is use a cascading lookup field (a custom field that enables filtering one lookup according to the value[s] selected in the parent loookup. Then you can set up two lookup columns in your Features list, the first looking up the Initiatives list, the other - Themes list. Once you select a value in the Initiative, only the relevant values are available in Theme.

An example of a field like this is http://infowisesolutions.com/product.aspx?id=ConnectedFields2007 (from our company :)), but there are other solutions as well.

2
votes

How about this... you create a column in Themes that is a calculated field that concatenates all three values you seek from the Themes table, i.e.

=[Theme]&" / "&[Points]&"/ "&[Initiatives]

In the Features List you create a Lookup column that pulls in the concatenated value. You then make the Theme, Points & Initiatives" columns calculated fields that parse the correct value from the concatenated lookup value.

Voila.

0
votes
  1. Create hidden column in Themes list, for ex. InitiativeHidden.
  2. Create EventReceiver on Added and Update events for Themes which will fill InitiativeHidden based on Initiative lookup.
  3. Use your new field in the Features list as lookup column.
0
votes

This cannot be done with OOTB SharePoint. You should create custom field type. From commercial add-on, try to use Sparqube Lookup Extra field type. It supports to lookup any types of columns, including Lookup fields. http://www.sparqube.com/SharePoint-Lookup-Column/

0
votes

There is no out of the box solution for lookup columns in sharepoint but this list of opensource tools can help you do this

Check the Lookup Field with Picker 2010 tool