Friday, December 07, 2012

List joins and projections is a new feature in SP 2010 that seems to be seeing very little light of day.  Not surprising, considering it comes with a boatload of limitations and caveats.  But there are some situations where it can come in handy.  In this post I'll go over what they can't do, where they're useful, and how to do some simple joins.

Joins in SharePoint work much like SQL joins.  You have multiple lists that are related, and need to show the data in a combined view.  When there are only two lists, the normal lookup functionality usually suffices.  More than two is where joins come in.  However...

Limitations

• There is no way to leverage joins and projections in a view through the UI.  CAML must be written.  Few things are more badass than CAMLs, but they're often hairy.  (Get it...? Maaan I crack myself up)
• Joins can only be defined on lookup columns
• Projected fields cannot be used to sort or group in the view
• Dynamic sorting and filtering in listview webparts is also not supported
• Projected columns do not show up correctly in datasheet view, or when exported to Excel

Use Case

Joins and projections can be very useful for filtering and displaying in a listview data from multiple related lists.  Consider this example where we have three lists with a grandparent-parent-child relationship: Manufacturers, Products, and Orders.  Each is linked to the previous by a lookup column.

Since Orders has a lookup to Products, we can create a list view of orders with their related products.  OOB lookup column functionality in SP 2010 allows us to pull in other Product fields as well, such as SKU.

But, what if we want to include the Manufacturer and Industry?  In the rest of this post I'll go over exactly how to achieve something like this:

Unfortunately, a lookup column cannot be used to pull in another lookup field.  Product:SKU works because SKU is a text field on Products.  Product:Manufacturer isn't supported since Manufacturer is a lookup field.

We could create text fields on Products and use event receivers to populate them whenever Manufacturers/Products are updated.  But that's clunky, especially if we need to capture other Manufacturers fields such as Industry.

Joins and projections offer a more elegant solution.

Join Example

The markup below joins Orders to Products, then Products to Manufacturers:

<Joins>
    <Join Type="LEFT" ListAlias="prd">
        <Eq>
              <FieldRef Name="Product" RefType="Id" />
              <FieldRef List="prd" Name="ID" />
        </Eq>
    </Join>
    <Join Type="LEFT" ListAlias="mftr">
        <Eq>
              <FieldRef List="prd" Name="Manufacturer" RefType="Id" />
              <FieldRef List="mftr" Name="ID" />
        </Eq>
    </Join >
</Joins>

Note that the actual lists involved are not explicitly specified.  Since we'll be creating our view on the Orders list, that is assumed to be the root for the joins.  The join from Orders to Products does not specify Products either.  That is implicitly determined by the fact that we're joining on the Product lookup field.

Each joined list has to be assigned a List Alias, which is how it will be referred to later.

The two <FieldRef> elements in the join condition specify the pair of fields that are being joined:

• The first refers to the Internal Name of a Lookup column (Product) on the primary list (Orders). In the first join, the primary list is not specified; it is assumed to be the root list (Orders). In the second (and any subsequent joins), the primary list must be specified by it's List Alias (prd). RefType should always be "Id".
• The second FieldRef specifies the column being joined to. It always refers to the ID column of the target list, specified by it's List Alias.

Projection Example

The markup below creates two projected fields from the joined Manufacturers list:

<ProjectedFields>
    <Field Name="Manufacturer" Type="Lookup" List="mftr" ShowField="Title" />
    <Field Name="ManufacturerIndustry" Type="Lookup" List="mftr" ShowField="Industry" />
</ProjectedFields>

Projected fields must  be type Lookup.  The List attribute refers to the List Alias of a join defined above.  ShowField refers to the field on the joined list to create the projected field from.

Putting it Together

When creating a new list via CAML, the joins and projected fields XML above can be included in the list views definition.  Note that they are not children of the <Query> node, but completely separate elements.

Adding a view to an existing list, however, can only be done using the object model.  The easiest way is to make a new feature and put the view creation code in the feature receiver.

Important: Do not include the root <Joins> and <ProjectedFields> tags when setting SPView.Joins and SPView.ProjectedFields.  That will cause a view compilation error at deployment time.

The SPList.Views.Add(...) method does not have parameters for joins and projected fields markup.  Those are set after creating the view.  This means you also have to add view columns after the view is created, instead of passing them in.  See comments below.

public class JoinDemoEventReceiver : SPFeatureReceiver
{

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{

SPSite site = properties.Feature.Parent as SPSite;

if (site != null)
{

SPList ordersList = site.RootWeb.Lists["Orders"];

// Fields to include in view. Leave this empty. We will add fields after
// creating the view. This is because projected vields are not available yet.
StringCollection fields = new StringCollection();

// View Query, Joins, and Projected Fields markup (from examples above)
string query = "<Where></Where>";
string joins = "<Join Type='LEFT' ListAlias='prd'><Eq><FieldRef Name= .....";
string projection = "<Field Name='Manufacturer' Type='Lookup' List= .....";

// Create new view on Orders list, and set the Joins and ProjectedFields
SPView view = ordersList.Views.Add("Joined Orders",fields,query,100,true,false);
view.Joins = joins;
view.ProjectedFields = projection;

// Add fields to view
view.ViewFields.Add("Title");
view.ViewFields.Add("Product");
view.ViewFields.Add("Product_x003a_SKU");
view.ViewFields.Add("Manufacturer");
view.ViewFields.Add("ManufacturerIndustry");
view.ViewFields.Add("Quantity");

// Save
view.Update();
ordersList.Update();

}

}

}

The Finished Product

Build and deploy the solution, and activate the feature.  If all goes well, you should have a new list view with joined columns from its parent and grandparent lists.  Here's our "Joined Orders" view with related product and manufacturer!

If you modify the view from the UI, the projected fields can be shuffled around, or used to filter the view like normal fields.  However, you can not group or sort by them:

 

The end!