Updating existing lookup fields in SharePoint

Posted by Eric Bartels

Creating lookup-fields only via XML-Definition is not possible. You need the ID of the Web hosting the list and the ID of the list hosting the data the lookup field is connected to. This information can only be retrieved when both entities are created…

Object model?!

When looking into the SPFieldLookup-class you find the properties LookupField, LookupList and LookupWebId. LookupWebId and LookupField can easily be set via the object model. The documentation says that LookupList is read and writeable which is not true. Setting LookupList throws an exception stating out “Cannot change the lookup list of the lookup field.”.

SchemaXml and Regex to the rescue

The SchemaXml-property of a SPField can be used to manipulate the LookupList. One stumbling block is that you do not know the Guid of the LookupList currently stored in the schema. Even if you define a Guid inside your xml-field-definition SharePoint puts in an other Guid. Here a simple regular expression comes to help.

The code for connecting a site-column with a list somewhere in some web:

// Declarations (myWeb is the context given by a feature receiver scoped "web")
var rx = new Regex("List=\"([^\"]*)\"", RegexOptions.IgnoreCase | RegexOptions.ECMAScript);
var root = myWeb.Site.RootWeb;
 
// myListInstance is an instance of a SPList existing somewhere
var lookupField = (SPFieldLookup)root.Fields["MyLookupField"];
var xml = lookupField.SchemaXml;
xml = rx.Replace(xml, String.Format("List=\"{0}\"", myListInstance.ID.ToString("B")));
lookupField.SchemaXml = xml;
// Do not call update here!
// Quote from MSDN "Do not call the Update method when using the SchemaXml  property to modify a field."
 
// Fetch the field again and apply the web-id
var lookupFieldReFetch = (SPFieldLookup)root.Fields["MyLookupField"];
lookupFieldReFetch.LookupWebId = myWeb.ID;
lookupFieldReFetch.LookupField = "Title";
lookupFieldReFetch.Update(true);

This problem still persists in the beta of SharePoint 2010. I hope this will get fixed in the RTM. Creating a lookup-field-connection is far more easy by the way …

6 Responses to “Updating existing lookup fields in SharePoint”

  1. Peter Holpar, on February 3rd, 2010 at 9:32 pm Said:

    “Creating lookup-fields only via XML-Definition is not possible.”
    Are you sure? I suggest you to read this post:
    Add SharePoint lookup column declaratively through CAML XML
    http://blogs.msdn.com/joshuag/archive/2008/03/14/add-sharepoint-lookup-column-declaratively-through-caml-xml.aspx

    “The documentation says that LookupList is read and writeable which is not true.”
    Yes, it is true. You can set it when creating the new lookup field from code. Check back to my blog in the next few days or so, I will post a custom solution that is based exactly on this fact.

    Peter

  2. Eric Bartels, on February 3rd, 2010 at 9:40 pm Said:

    Hi Peter,

    thanks for your comment. I’m aware of this “web-relative url-approach”. I this case the field which is updated is a site-column and the connected list does NOT reside in the root web. More specific: the destination is totally unknown until the “target website” is created and the site-column is connected to that web and the containing list.

    The LookupList is writeable, yes but when submitting the changes via SPField.Update(true) I get an exception telling me that this is not possible. The exception says (as stated in the blog-post) “Cannot change the lookup list of the lookup field.”

    Greetings Eric

  3. Peter Holpar, on February 4th, 2010 at 11:04 am Said:

    Hi Eric,

    Yes, the property is writable, but not “changeable”.

    It is due to the definition of the property setter:

    set
    {
    if (!string.IsNullOrEmpty(base.GetFieldAttributeValue(“List”)) && (base.GetFieldAttributeValue(“List”) != value))
    {
    throw new SPException(SPResource.GetString(“CannotChangeLookupList”, new object[0]));
    }
    base.SetFieldAttributeValue(“List”, value);
    }

    It must be “by design”, as changing the List behind the field would cause inconsistencies for existing items referencing items in the target list. Items in the new list would mean something very different or event might not exist at all.

    Peter

  4. Eric Bartels, on February 4th, 2010 at 11:25 am Said:

    Hi Peter,

    thanks for that “reflector-ed code” :-)

    As many things within SharePoint a strange “design decision” made by the development team. By using the SchemaXml (which is the only option you have!) this “integrity check” is fully ignored…

    Greetings
    Eric

  5. Andreas B, on March 11th, 2010 at 4:10 pm Said:

    Hi Peter,

    could you please explain me where I can put the code shown in your example? I’ve got a list in my visual studio solution including some aspx, xml and htm files. I have no clue where I have to add the code.

    Creating a seperate windows form app doesn’t make any sense, since I don’t want to execute from outside.

    Cheers,
    Andreas

  6. Matt., on March 30th, 2012 at 3:56 am Said:

    It works like a charm! Saved me a lot of time.

    Thanks!

Leave a Reply

« »