Add lookup field to list from a RootWeb

Question

I have the site collection rootweb called Main Site. I have many subsites called according to the year, 2012, 2011, etc. In the rootweb, there is a list called Products. In the subsites there is a list called Sales, and I need to add a lookup field to the list Products in the root site. I found this code, but its not working, it created the lookup field, but the dropdown is empty even if there are products.

//Request North lookup
                 currentList = currentWeb.GetSafeListByName(SponsoringCommon.Constants.LISTNAMES_SALESNORTH);
                 string sFldReqNr = SponsoringCommon.Constants.FIELDS_REQUESTNUMBERLOOKUPNORTH_NAME + currentWeb.Title;
                 Functions.CreateRequestNumberLookup(currentList, sFldReqNr, false, Functions.NorthSouth.North);

                 ArrayList colPreviousContentTypes = new ArrayList();
                 currentList.AddFieldToContentType(sFldReqNr, SponsoringCommon.Constants.CONTENTTYPES_SALESNUMBER_NAME, 2, colPreviousContentTypes, 1033);




public static void CreateProductNameLookup(SPList currentList, string strInternalFieldName, bool allowMultipleValues)
        {
              Logger.LogDebug("Functions",
                "CreateProductNameLookup(SPList currentList, string strInternalFieldName, bool allowMultipleValues)", "BEGIN");

            SPWeb currentWeb = currentList.ParentWeb;
            SPList targetList = currentWeb.Site.RootWeb.GetSafeListByName(SponsoringCommon.Constants.LISTNAMES_PRODUCT_NAME);
            SPFieldCollection colFields = currentList.Fields;
            Guid targetListID = targetList.ID;

            int L1 = strInternalFieldName.Length;
            int L2 = currentWeb.Title.Length;
            string sFieldNameWithoutYear = (strInternalFieldName.EndsWith(currentWeb.Title) ? 
                strInternalFieldName.Substring(0, L1 - L2) : 
                strInternalFieldName);

            if (colFields.ContainsField(strInternalFieldName))
            {
                Logger.LogDebug("Functions",
                    "CreateProductNameLookup(SPList currentList, string strInternalFieldName, bool allowMultipleValues)",
                    "Field '" + strInternalFieldName + "' already exists. (>> Skipped)");
            }
            else
            {
                strInternalFieldName = colFields.AddLookup(strInternalFieldName, targetListID, false);
                SPField fld = colFields.GetFieldByInternalName(strInternalFieldName);
                fld.ShowInNewForm = true;
                fld.ShowInEditForm = true;
                fld.ShowInDisplayForm = true; 
                SPFieldLookup fldLU = fld as SPFieldLookup;
                fldLU.AllowMultipleValues = allowMultipleValues;
                string strSchemaXml = fldLU.SchemaXmlWithResourceTokens;
                strSchemaXml = strSchemaXml.Replace("DisplayName=\"" + strInternalFieldName + "\"", "DisplayName=\"$Resources:SPNLSponsoring,Field_" + sFieldNameWithoutYear + "_Name;\"");
                strSchemaXml = strSchemaXml.Replace("/>", " ShowField=\"" + targetList.Fields[SponsoringCommon.Constants.FIELDS_PRODUCT_NAMENEW].InternalName + "\" " +
                    "Description=\"$Resources:SPNLSponsoring,Field_" + sFieldNameWithoutYear + "_Description;\" " +
                    "Group=\"$Resources:SPNLSponsoring,Field_NationaleLoterijSponsoringColumns_Group;\" />");
                fldLU.SchemaXml = strSchemaXml;
                fldLU.Update(true);
                currentList.Update();
            }

            Logger.LogDebug("Functions",
                "CreateProductNameLookup(SPList currentList, string strInternalFieldName, bool allowMultipleValues)", "END");
        }

Answer 1

I simply define column in elements file of site-scoped feature

<Field ID="fieldId" Type="Lookup" Name="Department" StaticName="Department" DisplayName="Department;" List="Lists/Departments" ShowField="Title" Overwrite="TRUE" Indexed="TRUE"></Field>

And then use it in my content type

<ContentType ID="id"
               Name="ct"
               Description=""
               Overwrite="TRUE"
               Inherits="TRUE"
               Version="0">
    <FieldRefs>
      <FieldRef ID="fieldId" Name="Department"/>
    </FieldRefs>
  </ContentType>

It works fine.

Answer 2

if I pasted code its because I wanted the answer to include .net code, not xml. :)

I solved it myself and the problem was on this line:

  strInternalFieldName = colFields.AddLookup(strInternalFieldName, targetListID, false);

there is another overload to this method that receives the Guid of the web where the target list is.

After changing it, it worked perfect.