Search This Blog

Sunday, December 7, 2014

SharePoint 2010 Join 2 Lists by using CAML

It’s maybe not new to you, but i found out, that you can join two or more lists with caml query. I tried the caml query builder but didn’t know how to use the join. So i created a small console app in order to try this out. I created two lists. List “Projects” with Title and a lookup column (“Phase”) to the second list “Phasen” to the title field. Now i would like to get a datatable which combines the values which are additional in the list “Phasen”, e.g.  the wow field.
The result should look like this afterwords:
The column “wow” comes from the second list “Phasen”. My Lists are set up like this:
Let me show you the code and afterword i explain it a little bit more.
01SPListItemCollection joinedResults = null;
02DataTable joinedResultsDataTable = null;
03 
04SPSite site = new SPSite("http://yourserver/sites/yoursitecollection/");
05SPWeb web = site.OpenWeb();
06SPList listFaculty = web.Lists["Projects"];
07 
08SPQuery query = new SPQuery();
09//Here you can define your where clause
10query.Query = "<where></where>";
11query.Joins = "<join Type='Left' ListAlias='Phasen'>" +
12"<eq><fieldref Name='Phase' RefType='ID' />" +
13"<fieldref List='Phasen' Name='ID' /></eq></join>";
14query.ProjectedFields = "<field Name='wow' Type='Lookup' List='Phasen' ShowField='wow'/>";
15query.ViewFields = "<fieldref Name='Title' /><fieldref Name='Phase' /><fieldref Name='wow' />";
16 
17joinedResults = listFaculty.GetItems(query);
18 
19if (joinedResults != null &amp;amp;amp;amp;amp;&amp;amp;amp;amp;amp; joinedResults.Count > 0)
20{
21//Important - Don't know why, but otherwise it can't get the DataTable
22int fieldCount = joinedResults.Fields.Count;
23joinedResultsDataTable = joinedResults.GetDataTable();
24}
As you can see, one of the important steps is to use the query.Join function. At first you define the list which you want to join, then you define the field in the main list which should make the reference, and then you define the field of the join list which is referenced. The reference goes to the id. It’s same like the lookup.
In the next step you define the projected fields. These are the field which are additional added to the results of the query. So they are like additional lookup fields, which you first define and then use in the viewfields.
Now you can put it into a datatable. But i had to use this code of line first:
int fieldCount = joinedResults.Fields.Count;

No comments:

Post a Comment