Posted by: R Manimaran | January 27, 2011

SharePoint 2010: using SPQuery Joins and ProjectedFields

SpQuery.Joins and ProjectedFields

In SharePoint 2010 we have a good feature to creating relation among the Lists and get the output. SPQuery has two properties which can be used to achieve this.

· Joins

· ProjectedFields

Joins:

Used to join two lists for queries

ProjectedFields:

Specifies the fields in joined lists that can be included in the view when one or more other lists have been joined in a query.

Example:

Consider the following Diagram.

Here I have created 3 SharePoint Custom List named Customers, Locations and CityState. While querying the Customers List I like to have ZipCode and AreaCode along with the result. How we can achieve this?

Here I have changed the display name of the Title column to CustomerName,City & State in the appropriate SharePoint list.

First we will do an initial Filter on the Customers List

using (SPSite site = new SPSite("http://Server/"))
{
  using (SPWeb web = site.OpenWeb())
    {
    SPList list = web.Lists["Customers"];
    SPQuery query = new SPQuery();
    query.Query = "<Where><Eq><FieldRef Name='Title'/><Value
            Type='Text'>Manimaran</Value></Eq></Where>";
   }
}

Now come the Joins, ProjectedFields and ViewFields

Joins:

query.Joins = @"<Join Type='LEFT' ListAlias='Locations'>
<Eq>
<FieldRef Name='CityName' RefType='ID'/>
<FieldRef List='Locations' Name='ID'/>
</Eq>
</Join>
<Join Type='LEFT' ListAlias='CityState'>
<Eq>
<FieldRef Name='State' RefType='ID'/>
<FieldRef List='CityState' Name='ID'/>
</Eq>
</Join>";

Here Type will have the following values

· LEFT

· INNER

ProjectedFields:

query.ProjectedFields = @"
<Field Name='ZipCode' Type='Lookup' List='Locations' ShowField='ZipCode'/>
<Field Name='AreaCode' Type='Lookup' List='CityState' ShowField='AreaCode'/> ";

here Name is the alias name we provided. This Name should be used in the ViewFields property of the SPQuery. Suppose if you provide PostalCode instead of ZipCode, the PostalCode needs to be used in ViewFields.

ShowField :- Field From where we need to get the value.

ViewFields:

query.ViewFields = @"<FieldRef Name='Title'/>
<FieldRef Name='CityName'/>
<FieldRef Name='ZipCode'/>
<FieldRef Name='State'/>
<FieldRef Name='AreaCode'/> “;

Now get the Items for the above query.

SPListItemCollection items = list.GetItems(query);

The output of the ZipCode and AreaCode will come as Lookup value we have to access the value as

SPFieldLookupValue lookupZip = new SPFieldLookupValue(items[0]["ZipCode1"].ToString());
string ZipCodeValue = lookupZip.LookupValue;

SPFieldLookupValue lookupAreaCode = new SPFieldLookupValue(items[0]["AreaCode1"].ToString());
string AreaCode = lookupAreaCode.LookupValue;

I have added another one column (LandMarks) which is a single line of text column in the Locations List.

Then we need to make change in the ProjectedFields and ViewFields as follows.

ProjectedFields:

query.ProjectedFields = @"
<Field Name='ZipCode1' Type='Lookup' List='Locations' ShowField='ZipCode'/>
<Field Name='LandMarks1' Type='Lookup' List='Locations' ShowField='LandMarks'/>
<Field Name='AreaCode1' Type='Lookup' List='CityState' ShowField='AreaCode'/>
";

ViewFields:

query.ViewFields = @"<FieldRef Name='Title'/>

<FieldRef Name='CityName'/>

<FieldRef Name='ZipCode1'/>

<FieldRef Name='State'/>

<FieldRef Name='AreaCode1'/>

<FieldRef Name='LandMarks1'/>

";

Observations:

1. DataTable is not supported when using Joins. I get the errorObject reference not set to an instance of an object” when I call the result in datable as

list.GetItems(query).GetDataTable()

2. Multiple Lines of text, Choice type columns are not supported in ProjectedFields.

Advertisements

Responses

  1. Nice info

  2. Good! Thank you!

  3. Good Post!

    Can you please tell me/link how to enable code highlight in wordpress?

  4. […] SharePoint 2010: using SPQuery Joins and ProjectedFields […]

  5. Thanks you so much …..

  6. Hi there, I discovered your website by the use
    of Google while looking for a similar matter, your website got here up, it looks great.

    I’ve bookmarked it in my google bookmarks.
    Hello there, just become aware of your weblog through Google, and located that it is
    really informative. I am gonna watch out for brussels.
    I’ll be grateful in case you proceed this in future. Many people shall be
    benefited out of your writing. Cheers!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: