Create Cross-site Lookup column in SharePoint

If we create a lookup site column from root site, the source list in sub site is not available from “Get information from” section in column settings.

A quick solution is to create a lookup site column and manage code to link the column with list in sub site when feature activated.

Below is my Site column Elements.xml looks like:

<?xml version="1.0" encoding="utf-8"?>

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

 <Field Type="Lookup"

      DisplayName="ClientNo"

      StaticName="ClientNo"

      Name="ClientNo"

      Required="TRUE"

      EnforceUniqueValues="FALSE"

      List="Lists/Client"

      ShowField="Title"

      UnlimitedLengthInDocumentLibrary="FALSE"

      RelationshipDeleteBehavior="None"

      ID="{468A862E-183B-4F5C-A59F-8BA608117C09}"

      RowOrdinal="0"/>

</Elements>




In your Feature Receiver code:




public static class SPFieldLookupExtensions

{

   public static void UpdateLookupReferences(this SPFieldLookup lookupField, SPWeb web, SPList list, string sFieldName)

   {

      if (string.IsNullOrEmpty(lookupField.LookupList))

      {

         lookupField.LookupWebId = web.ID;

         lookupField.LookupList = list.ID.ToString();

         lookupField.LookupField = sFieldName;

         lookupField.AllowMultipleValues = true;

         lookupField.UnlimitedLengthInDocumentLibrary = true;

      }

      else

      {

         lookupField.SchemaXml = lookupField.SchemaXml.Replace("Lists/Client", list.ID.ToString());

         lookupField.LookupWebId = web.ID;

         lookupField.LookupField = sFieldName;

         lookupField.AllowMultipleValues = true;

         lookupField.UnlimitedLengthInDocumentLibrary = true;

      }

       

      lookupField.Update(true);

   }

}

 

[Guid("76335e5e-5365-4d4d-a1a2-e09c9d059fc5")]

public class CustomFeatureEventReceiver : SPFeatureReceiver

{

   public override void FeatureActivated(SPFeatureReceiverProperties properties)

   {

      try

      {

         // Get root site and web that list exists

         SPSite site = properties.Feature.Parent as SPSite;

         using (SPWeb web = site.OpenWeb(new Guid("{24a0bf4c-1234-4e78-5678-ebb42afb46e1}")))

         {

            if (web.Lists["Client"] != null && site.RootWeb.Fields["ClientNo"] != null)

            {

               SPList listClient = web.Lists["Client"];

               SPFieldLookup field = (SPFieldLookup)site.RootWeb.Fields["ClientNo"];

               field.UpdateLookupReferences(web, listClient, "ClientNameAndNumber");

            }

         }

      }

      catch (Exception ex)

      {

          // Error handler

      }

   }

}