Monday, April 20, 2009

How to use the lookup field across different site collections

Short Introduction

OK, so we know that the standard lookup field can be configured to have its lookup list in the same site (the web UI allows only this case) or in a site under the same site collection - this can be set using the object model. The question is - is it possible to set a lookup list which is in a site in a different site collection. The answer is - yes, I just created a small POC which demonstrates how this works - still there're some limitations - the site collection containing the lookup list should be in the same content database in which the list with the lookup field is.
First a few words of how I came up with this idea. If you have a look at the XML definition of a lookup field (checking the value of the SchemaXml property) you'll see the attributes that contain the data for the lookup relation - WebId - containing the ID of the site containing the lookup list, List - containing the ID of the lookup list and ShowField - containing the internal name of the lookup field. These three can be modified using the properties of the SPFieldLookup class - LookupWebId, LookupList and LookupField. Still, with some restrictions: the LookupWebId and LookupList can be changed only if they weren't previously set (I assume the reason for that is to protect against loss of data - imagine that someone changes the lookup list of an item in a list with hundreds of items) and the LookupWebId property setter verifies if the web ID provided is of a SPWeb under the lookup field's site collection. But still there is a work-around and the WebId and List attributes can be modified even if they were previously set - the SPField.SchemaXml property setter can be used to modify the field definition XML including any attribute in it.
And this is basically what I did - I used a small WinForm tool to modify the SchemaXml of an existing lookup field setting the WebId attribute with the ID of a site in another site collection under the same web application and the List attribute with the ID of a list that I had on that site. At first the result wasn't very promising - when I navigated to the edit form page to edit an item I just noticed that the lookup field control doesn't work at all. Then I wrote several lines of code using the object model to create a new item in the list and passed an integer value (a valid id of an item in the lookup list in the other site collection) to the recently modified lookup field. After that I browsed the all items view page only to notice that the lookup field works just fine - it showed the related item from the list in the other site collection. So, the conclusion is that internally for the lookup field it doesn't matter whether the lookup site is in the same site collection or not. It is only the field control and of course the field editor control (the one appearing in the add/edit field to list/web) that are not designed to support this scenario. And yet another problem - the display render pattern of the lookup field - it normally displays a link to the lookup item with the value of the lookup field - as I said the lookup value appears correctly, but the link to the display form of the lookup item is not the right one.
So the next question was can these problems be solved - to answer it I created a small POC project with a custom field type inheriting the standard lookup field with a custom field control and a custom field editor control - my idea was to replace the plugable items which were not working in my case with the standard lookup field - the rest I left to the lookup field which just happened to support
internally cross site collection relations.
Several words about the POC project: first you can download it from here. You should keep in mind that this is a sample project with no production quality code inside, so if you want to use it in a real situation you should modify and improve it. Then there is the implication of the officially not supported by Microsoft features - bear in mind that the lookup field used as it is in the POC may cause various problems in different scenarios. Also it lacks some basic features related to code quality and best practices as well as some of the functionality expected is not fully implemented - e.g. I've hardly used proper exception handling logic, in the field editor control no validators are used, though there should have been at least several, etc. The field control uses just a simple drop-down as opposed to the text field with auto-complete logic which appears in the standard lookup control when the lookup list has more than 20 items. I didn't implement a field control for the multiple values mode of the field and this option also cannot be set from the field editor control. The latter is pretty simple, providing almost the bare minimum as user interaction and also misses some of the options that normally can be set for lookup fields. And finally I haven't modified the CAML for the field render display pattern (it inherits the one of the standard lookup), so that the broken link to the lookup list item is either fixed or at least removed (removing it is quite easy though).
OK, so I hope that I haven't scared you away with the big list of not finished stuff above - as I told from the very beginning - this was a sample project and a simple proof of concept.

The Sample Project

The sample project contains the following classes and files:
  1. FieldCrossSiteCollectionLookup - the field class of the custom lookup, inherits the standard lookup field class - SPFieldLookup - adds basically only one new property LookupSiteCollectionID, which maps to a custom attribute in the field schema definition (xmlns:LookupSiteCollectionId) - this is used in the field control and the field editor user control to create the SPSite instance of the site collection containing the lookup list. I was tempted to use code like this one to get the url of a SPWeb by its ID:

    SPRequestInternalClass req = new SPRequestInternalClass();

    IntPtr p = IntPtr.Zero;
    req.InitHeap(ref p);
    string webAppUrl = "http://stefan.nl/";

    Guid g = new Guid("4bbbd404-2744-4113-ba70-adb8d4853ce4");
    string url = req.GetWebUrl(g, webAppUrl);
    req.ReleaseResources();

    This way I wouldn't have needed the extra attrubute in the custom lookup schema, since the WebId one would have been enough to first get the url of the web and with it to create a SPSite instance, and then to open the SPWeb itself. But since the stuff in the Microsoft.SharePoint.Library (an interop assembly for the COM library owssvr.dll) is not documented I don't think it would be wise to use it directly.
    One thing that would probably be good to be added as a property in the field class and as an attribute to the field schema respectively would be the server relative path to the item display form of the lookup list, so that it can be then used in the render display pattern CAML of the field type definition to fix the broken link to the lookup item.
  2. CrossSiteCollectionLookup - the class of the field control of the custom lookup. A pretty simple control - creates a DropDownList control and binds it to a DataTable object containing the items of the lookup list. The DataTable is retrieved with SPList.GetItems(SPQuery).GetDataTable().
  3. CrossSiteCollectionLookupFieldEditor.ascx - user control - the editor field control of the custom lookup, uses a small code behind class - CommonFieldEditor, which implements the Microsoft.SharePoint.WebControls.IFieldEditor interface and also defines several virtual methods so that control can be transfered to their overrides in the user control itself. I used inline code directly in the ascx for easy and quick testing and development, no issreset-s needed. There was a little hitch there - the SPField object that the IFieldEditor.OnSaveChange method provides to the control is in some uninitialized state and the SchemaXml property setter just crushed (it does quite a few things extra than just set the field schema), so I went directly to the forbidden fruit here - called on an internal method of the SPField object with reflection to get the schema XML changed - check the comments in the ascx file itself also.
  4. fldtypes_stef.xml - the file containing the custom field type definition (should be copied to 12\template\xml) - just specifies that the field type inherits the standard lookup type and that FieldCrossSiteCollectionLookup is the field type class and CrossSiteCollectionLookupFieldEditor.ascx - the field editor control.

39 comments:

  1. Great post. I was looking for this all over google. The reflection code was what I needed to save the list item values.

    ReplyDelete
  2. Awesome stuff, but for some reason, the field isn't saving the information. If I try and look at the values of the field (web, list, field), there are always empty...

    ReplyDelete
  3. Hi,
    when you go to the "create column" page and select to create a cross site collection lookup field - after you enter the full url in the "web url" text box and click the "Get Lists" button do you get the "lists" drop-down populated with the lists from the target web. And if yes do you manage to successfully save and get the field created but can't see the selected web, list and field of the newly created field when you open the field properties page?

    ReplyDelete
  4. Hi Stefan,

    Does your custom field support Cross Sites on different content databases?

    ReplyDelete
  5. No, it doesn't support that - I've mentioned this at the end of the first paragraph of the posting.

    ReplyDelete
  6. Hi Stefan,

    I've a problem with your custom field :
    It registers in database the value but it cannot show the value in list view.

    Have you any idea ?

    ReplyDelete
  7. Hi Anonymous,
    so you see the saved value in the display and edit forms but not in the list view page? is the lookup list in the same or another web application - if this is the case then the lookup won't work - the two lists need to be in one and the same content DB (as mentioned in the posting).

    ReplyDelete
  8. Hi Stefan,

    Actually the problem is that your custom field has no "DisplayPattern" in its XML file.
    I am wondering if you intend to do it differently?
    Please let me know.

    ReplyDelete
  9. Hello anonymous,
    The missing of a "DisplayPattern" element is not a problem - in this case the DisplayPattern of the parent field type will be used - you can see this in several standard field types - e.g. PublishingScheduleStartDateFieldType and PublishingScheduleEndDateFieldType in fldtypes_publishing.xml. Does this problem occur when you connect the lookup field to a list in the same site. And also do you use the custom lookup type on a SharePoint or WSS installation (what about service packs)?

    ReplyDelete
  10. Hi Stefan,

    The problem occurs in any cases; the value is not saved (don't see it when I edit the item), and the value does not show up in display mode.
    And I have tried all scenarios (same web, different web, etc.)
    Config is Moss 2007 with SP2
    Any help? Is it working for you? what is your config?
    waiting for your answer!
    Paul

    ReplyDelete
  11. Hi Paul,
    yes, I have it working on WSS 3.0 SP2 and MOSS SP2. So, let's try to dissect the problem:
    1 - have you renamed the assembly or put the code from it in another assembly - if this is the case you have to check all references of the assembly - in the CrossSiteCollectionLookupFieldEditor.ascx and fldtypes_stef.xml files (also - are these in the right locations under the 12 folder).
    2 - can you check and send in another comment the SchemaXml of your cross site collection lookup field - you can use the SharePoint Manager tool - http://www.keutmann.dk/sharepointblog/spm2007.zip
    3 - have you tried to debug the code - in the list's edit form you can debug the field's editing control - the CrossSiteCollectionLookup class - and try to check what's going on OK or not OK with the retrieving/saving of the lookup value.

    ReplyDelete
  12. Hi Stefan,
    Indeed. I have created my own project (actually I have merged your code in my project).
    I have renamed the assembly everywhere needed, of course.
    And actually, it is working fine, except that the value of the lookup is not showing up. And when, I come back in the edit mode, it shows the "none" value.
    When debugging, the value is null; therefore it can't set the selectedValue.
    Do you have a mail address where I can send you my code? Or may I provide you with my email addresse?
    Paul

    ReplyDelete
  13. Hi Paul,
    yes, you can send me the code on this email - code@stefan-stanev.com

    ReplyDelete
  14. Hi Stefan,
    Sorry for the delay, I was on holiday.
    Below, you'll find the schemaXML of my field.



    I hope that will help
    In parallel, I will prepare un zip file with all the code
    Regards
    Paul

    ReplyDelete
  15. Hi Paul,
    XML seems to be not allowed in the comments (unless it is escaped with > < etc) but you can use the email address that I gave to you for the field's schema as well.

    ReplyDelete
  16. Hi Stefan,
    it shows to me, that under shareppoint v3 patched to version 12.0.0.6539 (october 2010) the custom lookup field, in view mode, builds not more the url to the target item correctly, if the target is in another sitecollection (but same content db). May be we must now build an own DisplayPattern, because the std lookup handler to search for webId is now not able to find a web outside the sitecollection.
    :-)TSC

    ReplyDelete
  17. Hi Anonymous,
    this is true - actually it doesn't work on older MOSS installation without SP2 or SP1 either. This is because I didn't implement that in the POC (this is explicitly mentioned in the posting). The solution is to create a custom DisplayPattern as you suggested in your comment. There are two possibilities here: the first one is to add an extra custom attribute to the field's schema that will contain the server relative URL of the web containing the custom list (this option is not very good, since the URL of a web can be changed, so you will need to synchronize the field's schema). The custom attribute can then be used in the CAML of the DisplayPattern to format the correct URL to the lookup list item. The second option is to use a custom redirection page in the standard SharePoint LAYOUTS folder. The CAML of the DisplayPattern will format a link to this redirection page providing it with the lookup site collection's ID, the lookup web's ID, the lookup list's ID and the lookup item's ID as URL query parameters (these are already available in the field's schema XML). The redirection page will use several lines of code to retrieve the correct URL to the item's display page and will redirect to it.

    ReplyDelete
  18. Hi Stefan,
    maybe we are able to use in the DisplayPattern this as target url: /_layouts/copyutil.aspx?Use=id&Action=dispform&ItemId=1&ListId=0B0EFF83%2DAF88%2D4AE0%2D9B09%2DD3A6A7DAEC69&WebId=A19B6018%2DBB10%2D4596%2DAEDA%2D2A117CCB4ACC&SiteId=9d10c924%2Dec17%2D4d02%2D9378%2D135c13e296a4&Source=http..
    :-)TSC

    ReplyDelete
  19. Hi Anonymous,
    this is indeed the best thing to do - that saves you the effort of implementing a custom redirection page. I didn't know myself that the standard SharePoint CopyUtil can do the trick of redirecting to another site collection. Good finding!

    ReplyDelete
  20. Hi Stefen,

    We would like your views on two issues we faced in trying to implement custom lookup field:
    1) We have to use the custom field to create columns to be added in content types for document libraries. You have mentioned this is not supported, as client applications cannot use such columns. Can you please explain a little on this?
    2) Why can’t this approach be used for lists in different Content DB? Is it a restriction from SharePoint end? Please help understand.

    Thanks in advance,
    Sudan

    ReplyDelete
  21. Hi Anonymous,
    About your questions:
    1) I don't think that it will be a problem to have a custom field in a custom content type used in a document library (I can't remember mentioning this in the posting). Basically, you will see this type of warning in the standard UI when you add a field based on any of the custom field types that you may have. And this will be an issue only if you want to edit the SharePoint fields of the document from within the Office applications (Word, Excel, etc) - then for the fields based on custom field types (including the cross site collection lookup) you will see a grayed (disabled) field (with the "Edit proprety in server" label) in the "Document properties" panel in Word/Excel.
    2) This is a SharePoint restriction. The cross site collection lookup uses internally the standard SharePoint lookup (only changes it's UI in the edit list form), so basically it inherits all its limitations.

    ReplyDelete
  22. Hi Stefan,

    I have used this approach, and i did nt face any issues with cross site collection in different cntent DB.
    Is there any particular scenario when it fails?

    ReplyDelete
  23. Hi Anonymous,
    I tested that for sites collections in different web applications (two web applications are always guaranteed to have separate content databases) and it simply didn't work in that case. Did you test with site collections in different web applications or in one web application that has more than one content databases? Also - is this for SharePoint 2007 (which service pack) or SharePoint 2010?

    ReplyDelete
  24. I was able to test it with a site colln from a different web application in same server. The server has SharePoint 2007 (12.0.6425.1000), no service packs.When I tried with a site collection from a diff server, it threw 'File not Found' error.

    I want to use the approach you have mentioned to create columns, and use it as part of a custom content type. Is it possible to specify the site collection url and list name as attributes inside the xml file of content type?

    Thanks for your time!!

    ReplyDelete
  25. Hi,
    Thanks for this great post..
    I need little clarification. I am creating a site column using this approach and used in document library.Now I wanted to edit the column in FLDEDITEX.aspx.I have option to choose different column, but is it possible to change the List also.

    ReplyDelete
  26. Hi anonymous,
    I deliberately designed the field's editing control (the CrossSiteCollectionLookupFieldEditor.ascx) this way. The reason is the same as it is with the standard SharePoint lookup field type - if you have already set some data in the lookup field of your list items when you change the lookup list this data will most probably get totally corrupted (you can't guarantee that the old and new lists will contain the same number of items with matching ID-s, etc). Still, if you want this functionality available you can modify the field's editing control at your own risk (the code is pretty straighforward and this wouldn't be hard to implement).

    ReplyDelete
  27. Hi Stefan,
    I have deployed the solution and wanted to create this colomun using XML file.The code i am using is shown below.when using this method the colomun is getting created but the values(url,list,field) are blank.Could you help me on how to resolve this issue.

    ReplyDelete
  28. code is missing in the above post
    Field
    ID="{69FEc66E-00C8-4321-9A1E-5B5AF98CBEd5}"
    Description=""
    Name="crossList"
    DisplayName="crossList"
    StaticName="crossList"
    Group="Custom"
    Type="CrossSiteCollectionLookup"
    grp:txtWebUrl="{85199536-d773-44fb-b5f0-fa511dac995c}"
    grp:ddlLists="{a6c583fc-923e-4b2c-9b2d-c3ca7a014bde}"
    grp:ddlFields="Title"

    ReplyDelete
  29. here are the changes in fieldtypes_stef.xml

    <PropertySchema>
    <Fields>
    <Field Name="xmlns:LookupSiteCollectionId" Hidden="True" Type="Text" />
    </Fields>
    </PropertySchema>
    <RenderPattern Name="DisplayPattern">
    <FieldSwitch>
    <Expr>
    <Property Select="FieldRef"/>
    </Expr>
    <Case Value="">
    <FieldSwitch>
    <Expr>
    <Property Select="CountRelated"/>
    </Expr>
    <Case Value="TRUE">
    <LookupColumn HTMLEncode="TRUE"/>
    </Case>
    <Default>
    <Switch>
    <Expr>
    <Column/>
    </Expr>
    <Case Value="">
    </Case>
    <Default>
    <FieldSwitch>
    <Expr>
    <Property Select="LookupType"/>
    </Expr>
    <Case Value="Computed">
    <LookupColumn/>
    </Case>
    <Default>
    <HTML><![CDATA[<A HREF="/_layouts/copyutil.aspx?Use=id&Action=dispform&ItemId=]]></HTML>
    <Column HTMLEncode="TRUE" AutoHyperLink="FALSE" AutoNewLine="FALSE"/>
    <HTML><![CDATA[&ListId=]]></HTML>
    <Property Select="List"/>
    <HTML><![CDATA[&WebId={]]></HTML>
    <Property Select="WebId"/>
    <HTML><![CDATA[}&SiteId={]]></HTML>
    <Property Select="xmlns:LookupSiteCollectionId"/>
    <HTML><![CDATA[}&Source=]]></HTML>
    <PageUrl URLEncode="TRUE"/>
    <HTML><![CDATA[">]]></HTML>
    <LookupColumn HTMLEncode="TRUE"/>
    <HTML><![CDATA[</A>]]></HTML>
    </Default>
    </FieldSwitch>
    </Default>
    </Switch>
    </Default>
    </FieldSwitch>
    </Case>
    <Default>
    <LookupColumn HTMLEncode="TRUE"/>
    </Default>
    </FieldSwitch>
    </RenderPattern>

    :-)TSC

    ReplyDelete
  30. Thanks for the property schema. Great job!

    ReplyDelete
  31. Hi Anonymous,
    about the declarative provisioning of the cross site collection lookup field - you shouldn't use attributes like grp:txtWebUrl, grp:ddlLists, grp:ddlFields, etc. The field actually uses three standard field schema attributes - WebId, List, ShowField and one custom attribute - xmlns:LookupSiteCollectionId. The "xmlns:LookupSiteCollectionId" attribute specifies the Guid of the source site collection, the "WebId" attribute - the Guid of the source site, the "List" attribute - the Guid of the source list and the "ShowField" attribute - the internal name of the field from the source list that the lookup column displays in the UI. And the problem is that for three of the attributes you need Guid values which are not known before the time you activate the feature so you cannot put values for these when you create your field feature element. The only way to resolve these is to use a feature receiver that will change the SPField.SchemaXml property of the lookup field programmatically initializing the Guid attributes with the desired values (which will be known or retrievable at the time of the feature activation). Basically you have the same problem with the standard lookup field - you also need to initialize the "List" and "WebId" (the latter only if the source list is not in the same site) attributes with code after the feature activation. In SharePoint 2010 this is fixed a bit - you can provide the site relative list URL in the "List" attribute and this will get resolved automatically to the source list's Guid when the field get provisioned.

    ReplyDelete
  32. Hi Stefan,
    Thank you for all the time you spend on this!

    We implemented a solution on SPS2010 based on the techniques described here. It works fine but unfortunately we realized too late, that this won't work "cross-content database". And that's a main requirement for us... :-(

    Any chance that we get this running cross-content-db? Any ideas?

    Thanks!
    Martin

    ReplyDelete
  33. Hi Martin,
    The custom lookup field actually is a simple extension on top of the built-in lookup field's functionality in SharePoint. And the limitation of the single content database is actually a limitation of the standard SharePoint lookup field. So, unfortunately, if you want to have a cross content database lookup field, you have to abandon the above described approach altogether.
    On the other hand if you are using SharePoint 2010, have you considered whether using managed metadata (taxonomy) fields is suitable for you case.
    Stefan

    ReplyDelete
  34. Hi Stefan
    Thanks for the reply. That's bad... :-(
    Actually, yes, we did consider using a property from the tax store. But unfortunately that's not an option for the specific solution we built.
    Well then - we have to search another solution.
    Thanks agin.
    Martin

    ReplyDelete
  35. Hi Stefan,

    Does the custom field work for site collections in different content databases in SharePoint 2010?

    ReplyDelete
  36. Hi Anonymous,
    unfortunately not - it shows the same behavior as in SP 2007

    Greetings
    Stefan

    ReplyDelete
  37. Try it: http://www.sparqube.com/SharePoint-Lookup-Column/
    It works correctly, when site collections are in difference content databases.

    ReplyDelete
  38. That was a great message in my carrier, and It's wonderful commands like mind relaxes with understand words of knowledge by information's.
    Sharepoint Training in Chennai

    ReplyDelete