Querying Boolean Fields with CAML

Recently in one of our projects we had to write some code to get items from a list in SharePoint 2013 using CAML. “Easy Peasy Lemon Squeezy” … I hear you say. It should be quite trivial especially given the fact that CAML has been around since SharePoint Team Services – the very first version of the platform.

Well, not so fast. If my experience as a SharePoint consultant has taught me anything, it is that even the most seemingly trivial task in SharePoint can kill you a couple of hours. That is – if you are lucky.

So let’s see where the catch is in querying Boolean fields with CAML.

In our example we will query a simple list that stores news. Besides the built-in title column, our list also has a multi-line text column for the news description, a date and time column for the published date and a yes/no (Boolean) column to flag the news as active or inactive:

News List

What we would like to accomplish is to build a CAML query to get all news from the list that are active.

In SharePoint 2010 I used the U2U Caml Query Builder to assist me in building CAML queries. Since this tool is not available for SharePoint 2013, I now use Caml Designer 2013. The latter has a fancy metro styled user interface that takes a bit of time to get used to. But it is quite flexible as it allows one to build pure CAML queries, as well as get code snippets for the server-side object model, the .NET client-side object model, the JavaScript client-side object model and REST services:

Caml Designer 2013

Building a CAML query for all active news in Caml Designer 2013 produces the following code:

<ViewFields>
   <FieldRef Name='ID' />
   <FieldRef Name='Title' />
   <FieldRef Name='NewsDescription' />
   <FieldRef Name='NewsPubDate' />
</ViewFields>
<Where>
   <Eq>
      <FieldRef Name='NewsActive' />
      <Value Type='Boolean'>True</Value>
   </Eq>
</Where>

One would expect the above to work just fine – it looks valid and pretty straightforward. Well, as I already said at the beginning … not so fast! When executed, this query does not correctly filter active news from our list. On the contrary, in my tests it would return the single inactive news item added to the list. SharePoint is a funny platform, I know.

The correct CAML query takes some trial and error to produce, and differs only slightly from the one given by the tool. What needs to be changed is to replace the Boolean type of the filter parameter with Bool:

<ViewFields>
   <FieldRef Name='ID' />
   <FieldRef Name='Title' />
   <FieldRef Name='NewsDescription' />
   <FieldRef Name='NewsPubDate' />
</ViewFields>
<Where>
   <Eq>
      <FieldRef Name='NewsActive' />
      <Value Type='Bool'>True</Value>
   </Eq>
</Where>

Another version that also works is to specify Integer type for the parameter and adjust the value accordingly:

<ViewFields>
   <FieldRef Name='ID' />
   <FieldRef Name='Title' />
   <FieldRef Name='NewsDescription' />
   <FieldRef Name='NewsPubDate' />
</ViewFields>
<Where>
   <Eq>
      <FieldRef Name='NewsActive' />
      <Value Type='Integer'>1</Value>
   </Eq>
</Where>

That is one more lesson learned in the life of a SharePoint consultant.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>