CAML and Querying Boolean Fields

By Erkka PeltoniemiFebruary 22 2010 20 comments

Today I had some trouble trying to filter out items by a field of type Boolean. I have a custom content type definition which has a FieldRef to a Field:

  <Field
   Type="Boolean"
   DisplayName="IsHeadlineDisplayName"
   Description="IsHeadlineDescription"
   Required="TRUE"
   Group="SharepointBluesCustomFields"
   ID="{3166A1DF-3EE9-4757-8344-B65BC2482261}"
   SourceID="http://schemas.microsoft.com/sharepoint/3.0"
   StaticName="IsHeadline"
   Name="IsHeadline" />

As my first shot at trying to filter out the records I wrote this CAML query:

  <Where>
    <Eq>
      <FieldRef Name='IsHeadline' />
      <Value Type='Boolean'>TRUE</Value>
    </Eq>
  </Where>

As a new comer to the world of Sharepoint development I expected this query to return all of the records which have a IsHeadLine field set to true, yet when I fired the query with SPList.GetItems method the engine would happily return all of the records regardless of their IsHeadline field's value. As Sharepoint doesn't seem to be too consistent on case sensitivity I first thought this was a case sensitivity issue but the query kept returning the same results regardless of how I wrote my parameter.

Turns out you can treat the Sharepoint Boolean fields much the same way as you may have gotten used to doing with T-SQL. With Integer types. 1 representing positive and 0 a negative value.

So I modified my CAML query a bit:

  <Where>
    <Eq>
      <FieldRef Name='IsHeadline' />
      <Value Type='Integer'>1</Value>
    </Eq>
  </Where>

This solved my problem and now the engine is finally returning me the proper records.

Popularity: 6% [?]

,

20 comments to “CAML and Querying Boolean Fields”

  1. Marion says:
    June 24, 2010 at 16:59

    This is the same problem I was having. Changing “Boolean” to “Integer” got what I needed.

    Thanks for the tip!

  2. Dineth says:
    July 2, 2010 at 19:50

    Great.. This helped me a lot.

  3. Rufat says:
    April 8, 2011 at 09:25

    If you have used “Bool” instead of “Boolean”, everything would be ok.

  4. Jay says:
    April 22, 2011 at 16:12

    This saved my time. Thanks Erkka

  5. Tom says:
    August 5, 2011 at 08:11

    Yep, I verified what Rufat says. You can use TRUE and it’ll work.

  6. Tom says:
    August 5, 2011 at 08:12

    That was supposed to say <Value Type=”Bool”>TRUE</Value>

  7. Tom says:
    August 5, 2011 at 08:14

    For anyone else, if you use "<" and ">" here, you’ll need to do it with apersand-l-t-semicolon and apersand-g-t-semicolon.

  8. Tom says:
    August 5, 2011 at 08:14

    “ampersand”, I mean… it’s been a long day…

  9. para kazanmak says:
    August 7, 2011 at 20:49

    “Great.. This helped me a lot.”

  10. Gil says:
    September 20, 2011 at 21:04

    This worked in my situation only if I used the reverse:

    1

    I’m wondering if the default value is really a NULL and that’s why the positive version, value = 0, did not work.
    Anyone know the CAML specification for a NULL value?

  11. Gil says:
    September 20, 2011 at 21:06

    that’s : ” 1″

  12. Serg says:
    November 16, 2011 at 15:35

    Thanks! Useful post!

  13. Jocelyn says:
    May 8, 2012 at 20:50

    Thank you!!! Useful and solved an issue I have been dealing with for quite some time.

  14. Uilque Messias says:
    August 13, 2012 at 17:23

    Thanks Erkka,
    It was exactly what I needed.

  15. okosym says:
    August 17, 2012 at 15:01

    This works too: 1

  16. okosym says:
    August 17, 2012 at 15:03

    This works too: <Value Type=’Boolean’>1</Value>

  17. Curia Damiano blog | SharePoint: CAML query on boolean field says:
    October 15, 2014 at 17:13

    [...] seen in the post CAML and Querying Boolean Fields, if you need to query a boolean field, don't cast it as [...]

  18. Francesco says:
    September 23, 2015 at 15:48

    Using Bool solved my problem!
    Thanks

  19. csgo says:
    June 5, 2016 at 14:03

    Good Site, Carry on the beneficial work. thnx!.

  20. PGKVWHWLHFOEFQY says:
    August 29, 2018 at 17:06

    Thanks for a marvelous posting! I seriously enjoyed reading it, you can be a great author.I will be sure to bookmark your blog and will come back someday. I want to encourage one to continue your great work, have a nice afternoon!

Leave a Reply

«
»