How can I reset the LookUp value of a Sharepoint List?

Question

I need some help on how to restore the value of the lookup column of one of my sharepoint list.

Scenario: I have 2 sharepoint lists. LIST1 LIST2

LIST1 has a column A with type Extended Lookup Field, this references to column A of LIST2.

Recently, I added another field in LIST2. Then I performed, deactivate/activate | uninstall/install of LIST2.

NOW< the problem is the reference lookup of column A - LIST1 TO column A - LIST2 was lost. Before when editing column A-LIST1, there is information written under:

Get information from: column a - LIST2

Now, it's just blank...

Answer 1

The LookupList property contains the GUID of the original instance of LIST2. If LIST2 was deleted and a new instance was created, the new LIST2 will have a different GUID and your lookup field on LIST1 will not work.

And, unfortunately, LookupList cannot be changed directly:

SPException: The property has already been set. You cannot change the lookup list after the LookupList property is set.

However, you can try the following:

Type type = typeof(SPFieldLookup);
object obj = type.InvokeMember("SetFieldAttributeValue", 
    BindingFlags.InvokeMethod | BindingFlags.NonPublic | BindingFlags.Instance, 
    null, 
    myLookupField, 
    new object[] { "List", guidOfNewList.ToString() });
myLookupField.Update();

Using reflection, you can try to call the internal SetFieldAttributeValue method and change the "List" attribute, which is what is used by the LookupList property.

Answer 2

Here is another method that may work to reset the target web and list. The code is placed into an extension method. I got the basic idea from some other internet posts:

Extension method, modifying XML with string operations: http://blogs.edwardwilde.com/2010/02/08/cannot-change-the-lookup-list-of-the-lookup-field/

Code fragment using XmlDocument: http://social.msdn.microsoft.com/Forums/sharepoint/en-US/f5c421a2-ca88-414e-9110-2b8ecb716e54/reattach-a-sharepoint-list-lookup-column-to-the-source-list

What works for my project:

/// <summary>
/// Extension methods for SPField objects.
/// </summary>
public static class SPFieldLookupExtensions
{

    /// <summary>
    /// Updates a Lookup field's source list by directly manipulating the XML schema SharePoint uses for the field.
    /// </summary>
    /// <param name="lookupField">The field to be updated.</param>
    /// <param name="list">The list that should be used by the lookup field.</param>
    public static void UpdateLookupReferences(this SPFieldLookup lookupField, SPList list)
    {
        // whether or not the lookup field's list is in the same site as the target list
        bool differentSite = lookupField.LookupWebId != list.ParentWeb.ID;
        // whether or not the lookup field's target list is correctly set
        bool differentList = lookupField.LookupList != list.ID.ToString();

        if (!differentSite && !differentList)
        {
            // return if field's properties are already correct.
            return;
        }

        if (string.IsNullOrEmpty(lookupField.LookupList) && (!differentSite || (differentSite && string.IsNullOrEmpty(lookupField.LookupWebId.ToStringNullSafe()))))
        {
            // if field has not been bound to anything, bind it now
            if (differentSite)
            {
                lookupField.LookupWebId = list.ParentWeb.ID;
            }
            lookupField.LookupList = list.ID.ToString();
        }
        else
        {
            // field is incorrectly bound, fix it.
            XmlDocument fieldSchema = new XmlDocument();
            fieldSchema.LoadXml(lookupField.SchemaXml);
            if (differentSite)
            {
                XmlAttribute webAttr = fieldSchema.DocumentElement.Attributes["WebId"];
                if (webAttr == null)
                {
                    webAttr = fieldSchema.CreateAttribute("WebId");
                    fieldSchema.DocumentElement.SetAttributeNode(webAttr);
                }
                webAttr.Value = list.ParentWeb.ID.ToString();
            }

            if (differentList)
            {
                XmlAttribute listAttr = fieldSchema.DocumentElement.Attributes["List"];
                if (listAttr == null)
                {
                    listAttr = fieldSchema.CreateAttribute("List");
                    fieldSchema.DocumentElement.SetAttributeNode(listAttr);
                }
                listAttr.Value = list.ID.ToString();
            }
            lookupField.SchemaXml = fieldSchema.InnerXml;
        }

        lookupField.Update(true);
    }

}