Using Site Lookup Columns, across sites

Posted on 3/15/2009 @ 11:44 PM in #SharePoint by | Feedback | 16706 views

Over the last week, I was talking with my friend and fellow MVP, Sezai Komur, who pointed me to a valuable piece of code written by yet another of my friend, and fellow MVP, Tony Bierman.

The problem we are trying to solve is the ability to lookup columns across sites, within the same site collection. Tony has created an elegant way of doing that via a custom field type. I wanted to demonstrate an alternative out of the mechanism. The main difference between Tony's approach versus this approach is that Tony's approach will let you go either way .. i.e. .. a child site's list can be referenced in a parent site, whereas the illustrated approach below lets you go only downwards .. i.e. from root to child and grandchild .. or from child to grandchild. .. but the below is out of the box.

Problem statement:

I have a site collection at root, and a child site at \child. Root contains a list called “Animal Types”. And \child contains a list called “Zoo Animals”. I want “Zoo Animals” to have a lookup column that looks up values from the “Animal Types” list at root.

Solution:

(Before I start, all of this can be/should be done via features and solutions .. okay here goes.)

Step #1: Create the Animal Types list as shown below:

Step #2: In the root site (SPWeb) Create a content type called “Animal” that inherits from “Item”

Step #3: Add a site lookup column in the “Animal” content type, called “Animal Type”, which points to the “Animal Type” list you created in step #1.

Step #4: In the \Child site, create a list called “Zoo Animals”, and eliminate the “Item” content type, and instead add the “Animal Type” content type.

Step #5: That’s it. Now add a new animal .. .. and you should see the cross-site lookup work as shown below:

Now go ahead and add a new value in the “Animal Types” list .. called “Just plain weird”, and you will see that the new value shows up as well ..

 

Pretty cool huh? Okay I’m off to bed .. goodnight everyone!

Sound off but keep it civil:

Older comments..


On 3/16/2009 8:11:38 AM Patrick Sola said ..
Hi Sahil,

Very interesting ! Thanks for the tip !

I was able to do the same thing without creating any content type :


- At site collection level, create a site lookup column (Animal Type) that refers to your list you called "Animal Types" and point to the title field.


- On your child site, go to you desired list settings and add a new column based from an existing one. Pick the one you just created (Animal Type).

Result will be the same...

What advantage do you see using a content type in this particular case ?

Best regards,


Patrick.


On 3/16/2009 11:30:02 PM Sahil Malik said ..
Hey Patrick,

Thanks for your comment, yes you are right, the crux is the site column. The content type is unecessary for this .. except .. you should always structure your information in content types, so I guess call it a habit.

Sahil


On 3/23/2009 10:05:18 PM Sezai Komur said ..
Thanks for the post Sahil.

What if you want to go the other way?

ie. you have a site collection with two subsites below the root called 'A' and 'B'. Can I have a list in 'A' and create a lookup on this list in site 'B' ?

Also - what if I want a Lookup that spans multiple lists in different sites? Say I have 6 document libraries spread out across various sites, can I create a Lookup field that spans multiple lists or libraries in different sites? That would be a neat ability to have for linking documents and list items.


On 6/2/2009 9:41:19 AM Mike said ..
For whatever reason, your pictures or code under each step is now showing up. Could you please re-post or send in an email. This is very interesting. Thanks!


On 8/6/2009 3:31:58 PM Mike said ..
NOTE: this out of the box method does not work if you save "Child" as a site template (include content) and create a sibling site or sub site based on that template. It loses the data in the "Get information from" field for the lookup column.


On 5/20/2010 9:38:20 AM ehaze said ..
Sahil, your images are missing.


On 5/21/2010 12:13:56 AM Sahil Malik said ..
ehaze - I think your network is blocking imageshack.


On 12/28/2010 10:29:18 AM felix jesus said ..
Hi Sahil:

I have a lista with lookup column but does not support more than 20 items, < 19 working properly.

PLease any solution


On 3/29/2011 5:50:02 AM Srikanth said ..
Hi Sahil:

Thanks for the Tip.