Posted on:
Categories: SharePoint
Description:

In the SharePoint web interface, projected fields for a lookup field can be specified on the field settings page.

 

The extra fields from the lookup list can then be consumed in views.

 

The following CSOM code can be employed to provision such views remotely. In this example I have a list named Registration which has a lookup field pointing to a list named Calendar. The internal name of the lookup field is calendarLookupField.

The objective is to add two projected fields as illustrated above.

 

using (var ctx = new ClientContext(url))
{
 ctx.Load(ctx.Web, w => w.Id);
 ctx.ExecuteQuery();

 var list = ctx.Web.Lists.GetByTitle("Registration");
 var view = list.DefaultView;

 view.ViewJoins =
  "<Join Type='LEFT' ListAlias='cal'>" + 
   "<Eq>" +
    "<FieldRef Name='calendarLookupFld' RefType='Id' />" + 
    "<FieldRef List='cal' Name='ID' />" + 
   "</Eq>" + 
  "</Join>"

 view.ViewProjectedFields = String.Format(
      "<Field Name='eventId' Type='Lookup' List='cal' ShowField='ID' WebId='{0}'/>" +
      "<Field Name='start' Type='Lookup' List='cal' ShowField='EventDate' WebId='{0}'/>",
      _ctx.Web.Id.ToString("D")
 );

 view.ViewFields.Add("eventId");
 view.ViewFields.Add("start");

 view.Update();

 ctx.ExecuteQuery();
}

 

These names in the code are aliases, which I chose arbitrarily. Avoid using spaces, etc. when naming them.

  • cal refers to the Calendar list
  • eventId refers to the Id field of the Calendar list.
  • start refers to the EventDate field of the Calendar list.

Some learning I went through

The documentation makes no reference to a WebId attribute on the projected field definition, but omitting it leads to a runtime error on the view "Unable to cast object of type 'System.Xml.XmlElement' to type 'System.String'". Thanks to Sergei Snitco for providing that insight here.

It may seem weird at first that nothing in the code refers explicitly to the list that we're getting the projected fields from (Calendar in this case) . As per the MSDN reference at the end, this is because the lookup field holds that relationship implicitly.

I intially started down the path of omitting the join expression from my code and relying on an implicit join.  Then I noticed this advice on MSDN: "We do not recommend working without a Joins element. You will maximize your solution’s chances of being compatible with future releases of Microsoft SharePoint Foundation by always using an explicit Join element."

References

List Joins and Projections

https://msdn.microsoft.com/en-us/library/office/ee539975(v=office.14).aspx

SP 2010: List Joins & SPQuery enchancements. Tobias Zimmergren

http://zimmergren.net/technical/sp-2010-list-joins-spquery-enchancements