SharePoint: Lookup a Lookup Column in a List

Question

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.

Solution 1

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.

Solution 2

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.

Comments

[Bruce P. Henry]

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?

[Batman]

It's crazy that you can't do this.

[Carlo Luther]

@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?

[Bruce P. Henry]

Hmm... I'm not really looking for a cascading dropdown (results of one dropdown are filtered by the selection in another dropdown). What I'm looking for is the way that SharePoint can populate another column in your list based on the value of another lookup column. I've edited my example to make that more clear.

[Bruce P. Henry]

Okay, I'm taking this as the correct answer because the first thing that Vladi says is absolutely correct. You can't do that.

[Batman]

You can't use Lookup fields in calculated columns from what I can see.