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