SharePoint Lookup Columns on steroids with CAML

In an earlier post I told you about a CAML query you could use to add a cross-site lookup column, pointing on a list from any other site of the same collection.

 

 

No suspens for this post, here is the Query :

<Field Type="Lookup" DisplayName="Office" Required="FALSE" List="{}" WebId="" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" StaticName="Office" Name="Office"/>

Where List and ShowField specifies the list and list column to lookup from. The WebId is the id of the Web where this list is.

If you take a close look at the Field reference: http://msdn.microsoft.com/en-us/library/ms437580.aspx you will notice that the WebId attribute isn’t specified, yet I am using it. Well I obviously didn’t invent this parameter, as I explained in my previous post, this cross site lookup only worked if using a web content type. So I checked the field definition of the working cross site lookup I created using a Document Library template and noticed this parameter using SharePoint Manager 2007 www.codeplex.com/spm.

By using it I can do create a working cross site lookup column without having to create a web content type. A short demo might help you understand how it works:

I have the following site hierarchy:

I want to add a column in the Phones Directories document library, this column shall be a lookup to the Title column of the Offices List. With standard functionality it’s a no go but let’s try a CAML query to create this column.

Assuming that:

• SA ID is 460dd869-7508-4eba-8c34-bfeffcc823fc
• Offices ID is 45BE507B-DF8C-43BC-AF6A-4C05EECA13DA

I know that I should use the following query:

<Field Type="Lookup" DisplayName="Office" Required="FALSE" List="{45BE507B-DF8C-43BC-AF6A-4C05EECA13DA}" WebId="460dd869-7508-4eba-8c34-bfeffcc823fc" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" StaticName="Office" Name="Office"/>

As PowerShell is my BFF when dealing with SharePoint, I wrote a short script to create this column :

# IT Joe – http://blog.jonathanroussel.com # One Shot Script - Add a column to a list using a CAML query [System.Reflection.Assembly]::LoadWithPartialName(”Microsoft.SharePoint”) $siteurl = "http://moss:81/SB" $mysite=new-object Microsoft.SharePoint.SPSite($siteurl) if($mysite) { $web = $mysite.openweb() if($web) { "WEB : " + $web.name + " / " + $web.title + " @ " + $web.url $list =$web.lists["PhoneDirectories"] if($list) { "List : " + $list.title $list.Fields.AddFieldAsXml('<Field Type="Lookup" DisplayName="Office" Required="FALSE" List="{45BE507B-DF8C-43BC-AF6A-4C05EECA13DA}" WebId="460dd869-7508-4eba-8c34-bfeffcc823fc" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" StaticName="Office" Name="Office"/>') } $web.dispose() } $mysite.Dispose() }

After running the script I open an item of the PhoneDirectories library and …. Voila:

As you can the Office column takes its value from:

One more remarks, if you try adding a new column “Office2” looking up the same list without the webid with the following CAML query :

<Field Type="Lookup" DisplayName="Office2" Required="FALSE" List="{45BE507B-DF8C-43BC-AF6A-4C05EECA13DA}" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" StaticName="Office2" Name="Office2"/>

You will see that this column works. I can imagine you thinking

“Then why did he told us a minute ago to add this undocumented obscure WebId parameter, I bet he invented it to try to look smart. What a fiasco!”

To answer this question I will just let you remove the original Office column and see for yourself if this Office2 column is still working...

Ok, spoiler alert, it won’t work. Seems like SharePoint needs at least one reference to the Web where the list to lookup is.

I don’t know if you will find It usefull but I hope you will like the trick.

Photo : Nelson's Column credit vgm8383 @Flickr

SharePoint Lookup Column and its limits

SharePoint comes with a very nice feature, the possibility to create columns (for lists, libraries metadata) based on lookup on other lists.

While this is very nice, it come with a huge limitation, you can only lookup list on the site where you are creating the column. If it is a site column, you will still be able to use it in subsites but that’s it.

If for example you have the following sites/subsites

Where SA, SB, SC, SD are SharePoint Sites (SPWebs) and SB includes a List L1.

There is no Out Of The Box (OOTB) means to create a lookup in SC on L1. In case of need you will still be able to use some cross site solutions available here and there (e.g. : http://tonybierman.blogspot.com/2008/07/free-custom-cross-site-lookup-column.html).

That’s usually where my conclusion would take place, maybe with a bit of ranting against Microsoft for not allowing us to do that in a standard WSS collection. This conclusion might even have ended with a new item being added to my “What I want in Microsoft SharePoint Server 2010?” wish list.

But it isn’t because I stumbled upon an interesting fact: there actually is a way to do that OOTB.

So first create a Site Column in SB, this column shall be a lookup on a L1 field. Now Create a document library in SB, let’s call it DL1. Use the site column you just created in SB.

You can now save DL1 as a library template and finally create a new document library DL2 in SC based on this template.

You get something like that:

If you add a document to DL2, you will be able to select a property based on a L1 lookup. If you still can’t believe it, try adding an item to L1 just to make sure it is available in the list of choice in DL2.

I don’t think it is usable in real life but it triggered something in me. It is clearly possible to lookup a field from another list using only standard features. I then decided to give it a try using CAML to create my lookup field and guess what it works very well. I will give you more details very soon!

Edit: More Details in a newer post.

Photo : Lookup Pop credit Tomas @Flickr