Scenario: A user asked a question, can I still perform CAML query joins even if I don't connect Lookup to ID of another list. I want to connect Lookup to the Title field of the other list. The answer is yes we can do it. However, the RefType attribute will still remain "Id" instead of Title in the CAML join property. And now he example:
Create a list named Resources as shown below:
Create a list named Projects and add a Lookup Column named Allocation which references the Title field of Resources list created above.
In the additional column settings, select Location as additional field to be shown.
Add data to Projects list as shown:
And here is the code to retrieve data from the list using JOIN and Projections:
ListAlias specifies the name of the Lookup List which in our case is Resources.
"<FieldRef Name='Allocation' RefType='Id'/>" specifies the Lookup column. Notice here that RefType is equal to Id even if the Lookup column is attached to the Title in Resources list.
query.ProjectedFields ="<Field Name='ResourceLocation' Type='Lookup' " +
"List='Resources' ShowField='Location'/>";
In the Projected fields any Name can be given to projected field which can then be used further in places like ViewFields or while getting data from list item. However, the List property needs to be the name of lookup list and ShowField needs to be the internal name of the field which is selected during adding additional fields while creating lookup.
Create a list named Resources as shown below:
Create a list named Projects and add a Lookup Column named Allocation which references the Title field of Resources list created above.
In the additional column settings, select Location as additional field to be shown.
Add data to Projects list as shown:
And here is the code to retrieve data from the list using JOIN and Projections:
using (SPSite site = new SPSite("http://sp2010:90"))
{
SPWeb web = site.RootWeb;
SPQuery query = new SPQuery();
query.Joins = "<Join Type='INNER' ListAlias='Resources'>" +
"<Eq>" +
"<FieldRef Name='Allocation' RefType='Id'/>" +
"<FieldRef List='Resources' Name='ID'/>" +
"</Eq>" +
"</Join>";
query.ProjectedFields =
"<Field Name='ResourceLocation' Type='Lookup' " +
"List='Resources' ShowField='Location'/>";
query.ViewFields = "<FieldRef Name='Title'/>" +
"<FieldRef Name='Allocation'/>" +
"<FieldRef Name='ResourceLocation'/>";
SPList customerList = web.Lists["Projects"];
SPListItemCollection items = customerList.GetItems(query);
foreach (SPListItem item in items)
{
SPFieldLookupValue resourceLocation =
new SPFieldLookupValue(item["ResourceLocation"].ToString());
SPFieldLookupValue allocation =
new SPFieldLookupValue(item["Allocation"].ToString());
Console.WriteLine("Project Title: {0}, Resource Location: {1}, Allocation: {2}",
item.Title,
resourceLocation.LookupValue, allocation.LookupValue);
}
}ListAlias specifies the name of the Lookup List which in our case is Resources.
"<FieldRef Name='Allocation' RefType='Id'/>" specifies the Lookup column. Notice here that RefType is equal to Id even if the Lookup column is attached to the Title in Resources list.
query.ProjectedFields ="<Field Name='ResourceLocation' Type='Lookup' " +
"List='Resources' ShowField='Location'/>";
In the Projected fields any Name can be given to projected field which can then be used further in places like ViewFields or while getting data from list item. However, the List property needs to be the name of lookup list and ShowField needs to be the internal name of the field which is selected during adding additional fields while creating lookup.