Raphael's Technical Blog

  private static void AddFilteredLookupByXml() {

    try {

      // set target site as appropriate

      using (SPSite site = new SPSite("http://localhost/")) {

        // set target web as appropriate

        using (SPWeb web = site.RootWeb) {

          // the source list, i.e. where data will be retrieved from

          SPList sList = web.GetList("/Lists/Tasks");

          // the target list, i.e. the list that will contain the lookup field

          SPList tList = web.GetList("/Lists/FilteredItems");

          // the query to use to filter retrieved data

          string q = "<Where><Eq><FieldRef Name="Status" /><Value " +

            "Type="Text">In Progress</Value></Eq></Where></Query>";

          // the CAML xml fragment for the new FilteredLookup field

          string xml = string.Format("<Field ID=\"{0}\" " +

              "StaticName=\"{1}\" Name=\"{1}\" " +

              "Type=\"FilteredLookup\" DisplayName=\"My FilteredLookup Tasks\" Required=\"FALSE\" " +

              "WebId=\"{2}\" List=\"{3}\" ShowField=\"{4}\"/>",

            // the ID (GUID) of the new FilteredLookup field

              "{b28aa3a5-c186-49e1-b0f6-e9a949ffffff}",

            // the Name and StaticName of the new FilteredLookup field

              "MyFilteredLookup_Task",

            sList.ParentWeb.ID, // GUID of the source web

            sList.ID, // GUID of the source list

            sList.Fields.GetFieldByInternalName("Title").Id // GUID of the source column

          );

          string fn = tList.Fields.AddFieldAsXml(xml); // add new FilteredLookup field as xml

          tList.Update(); // update target list

          FilteredLookupField f = ((FilteredLookupField)tList.Fields.GetFieldByInternalName(fn));

          // Don't set both query filter and view filter. ONLY set one or the other

          f.QueryFilterAsString = q; // set query filter

          //f.ListViewFilter = "";

          f.AllowMultipleValues = false; // set allow multiple values

          // set whether query is recursive. This ONLY applies to query filter

          f.IsFilterRecursive = true;

          f.Update(true); // finally update the new FilteredLookup field

        }

      }

    }

    catch (Exception e) {

      e.Source = "AddFilteredLookupByXml";

      throw e;

    }

  }