Cannot change the lookup list of the lookup field.

Ever get this error message

SPException “Cannot change the lookup list of the lookup field.”

Once a lookup field (SPFieldLookup) has had it’s LookupList and LookupWebId set you can’t change them.

There is a good reason for this if your lookup field is used in multiple lists or already has data entered, you would effectively break the referential integrity of the data. So what I’m showing here as a work-around to this error message should only be used if you are *sure* no lists reference the lookup column or no lists have any data using this column

Okay with the health warning out of the way the work-around is simply to update the Schema property to overwrite the intervalues for List & WebId

I created a handy extension method for dealing with the string manipulation:

Usage:

SPFieldLookup field = (SPFieldLookup)site.RootWeb.Fields[MyId];
field.UpdateLookupReferences(newWeb, newList);

Implementation:

public static class SPFieldLookupExtensions
{
    public static void UpdateLookupReferences(this SPFieldLookup lookupField, SPWeb web, SPList list)
    {
        if (string.IsNullOrEmpty(lookupField.LookupList))
        {
            lookupField.LookupWebId = web.ID;
            lookupField.LookupList = list.ID.ToString();
        }
        else
        {
            lookupField.SchemaXml =
                lookupField.SchemaXml
                    .ReplaceXmlAttributeValue("List", list.ID.ToString())
                    .ReplaceXmlAttributeValue("WebId", web.ID.ToString());
        }

        lookupField.Update(true);
    }
}

public static string ReplaceXmlAttributeValue(this string xml, string attributeName, string value)
{
    if (string.IsNullOrEmpty(xml))
    {
        throw new ArgumentNullException("xml");
    }

    if (string.IsNullOrEmpty(value))
    {
        throw new ArgumentNullException("value");
    }


    int indexOfAttributeName = xml.IndexOf(attributeName, StringComparison.CurrentCultureIgnoreCase);
    if (indexOfAttributeName == -1)
    {
        throw new ArgumentOutOfRangeException("attributeName", string.Format("Attribute {0} not found in source xml", attributeName));
    }

    int indexOfAttibuteValueBegin = xml.IndexOf('"', indexOfAttributeName);
    int indexOfAttributeValueEnd = xml.IndexOf('"', indexOfAttibuteValueBegin + 1);

    return xml.Substring(0, indexOfAttibuteValueBegin + 1) + value + xml.Substring(indexOfAttributeValueEnd);
}
Advertisements
Tagged ,

9 thoughts on “Cannot change the lookup list of the lookup field.

  1. Alahyane says:

    You saved my life. Thank you very much

  2. Danny says:

    You are a life safer.
    Great article!

    Will reference this on my blog.

    Gr.
    Danny

  3. László Jankó says:

    Thank you very much, this code saved me a lot of trouble!

  4. Mark says:

    In order to be a bit more robust, you might want to search for something a bit more specific than xml.IndexOf(attributeName..) .

    For example, if “List” is a part of any attribute value, it will find that first and generate incorrect xml.

    As a simple hack/quick fix, you could search for something like ” “+attributeName+”=\”” which should be a bit more reliable.

  5. Sherbertas says:

    Little bit buggy code. Class definition is closed too early. Method ReplaceXmlAttributeValue should be inside the class SPFieldLookupExtensions.

  6. kiran says:

    as Mark Mentioned List is part of my attribute list, so we need space before List when we look for index

    int indexOfAttributeName = xml.IndexOf(string.Format(” {0}”, attributeName), StringComparison.CurrentCultureIgnoreCase);

    and update indexOfAttibuteValueBegin
    int indexOfAttibuteValueBegin = xml.IndexOf(‘”‘, indexOfAttributeName + 1);

  7. Vikram says:

    Thanks. VEry helpful indeed.

  8. Pierre-Luc says:

    If the attribute does not exist, the code will hang. Let it fix it and adding the missing attribute to XML :
    public static string ReplaceXmlAttributeValue(this string xml, string attributeName, string value)
    {
    if (string.IsNullOrEmpty(xml))
    {
    throw new ArgumentNullException(“xml”);
    }

    if (string.IsNullOrEmpty(value))
    {
    throw new ArgumentNullException(“value”);
    }

    int indexOfAttributeName = xml.IndexOf(attributeName, StringComparison.CurrentCultureIgnoreCase);
    int indexOfAttibuteValueBegin;
    int indexOfAttributeValueEnd;

    if (indexOfAttributeName == -1)
    {
    value = attributeName +”=\”” + value + “\””;
    indexOfAttributeValueEnd = xml.IndexOf(“/>”) – 1 ;
    indexOfAttibuteValueBegin = indexOfAttributeValueEnd;
    }
    else
    {
    indexOfAttibuteValueBegin = xml.IndexOf(‘”‘, ” ” + indexOfAttributeName);
    indexOfAttributeValueEnd = xml.IndexOf(‘”‘, indexOfAttibuteValueBegin + 1);
    }
    return xml.Substring(0, indexOfAttibuteValueBegin + 1) + value + xml.Substring(indexOfAttributeValueEnd);
    }

  9. Chris Cox says:

    Thank you this saved me when I was having issues with lists created from list templates that had MMS columns.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: