Tuesday, June 09, 2009

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:

image

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:

image

As you can the Office column takes its value from:

image

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

10 comments:

Anonymous said...

Thanks for a well-written and simple to understand post. I am curious, is it possible to point the lookup field to a view of the desired list? thanks

Joe said...

Glad you liked you my post, thanks.

About your question, to the best of my knowledge there is no out of the box means to filter a lookup column values based on a view.

If anyone knows a standard way to do that, feel free to contradict me.

If this is a no way out requirement for you, you might try some tricks involving repopulating the columns with some jQuery/Web Services magic or using a custom lookup column (either developed internally or bought from dedicated company such as bamboo or kwiz).

Anonymous said...

I need to do something similar, but I do not have access to run scripts on the SP server. Is there some way to run scripts like this through the design client?

Thanks

Joe said...

Hi,

If you don't have access to the server itself, you can still modify the content types and site columns using the Webs.asmx web service, especially the UpdateColumns operations.
More details at :
http://msdn.microsoft.com/en-us/library/webs.webs_members.aspx
http://msdn.microsoft.com/en-us/library/webs.webs.updatecolumns.aspx

truth be said, I have never used this specific Web Service but seems like it could be enough.

,Joe

Anonymous said...

Hi

Thanx for your post.I am not in coding but want to arrange my columns any way i need to on sharepoint foundation uploaded exel lists.

I want to stthe ordering at one position for all uploaded lists since they hold same columns for different people.

How can I do this?
mkhizefs@gmail.com

ramana said...
This comment has been removed by the author.
ramana said...

Hi Jonathan,
I am so proud in finding your blog..thanks for your valuable posts..I succeeded in adding cross site lookup column in a list but unable to display it in that list.It is displaying only when the list is in edit mode but i want to dispaly this column data in view mode also..plz post your reply ASAP.

Vadim said...

Many thanks. I read a ton of blogs before came across yours and it's really helped. You saved my life.

Pallavi said...

is it possible to get a cross site lookup column accessing list across a farm??

Joe said...

Hi Pallavi,

The out of the box lookup column can not cross site collection boundaries. Thus there is no way to consume data form a different farm.

Of course it doesn't mean that you can not create your own :)

Joe