CAML and Querying Boolean Fields

February 22 2010 19 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: 5% [?]

19 comments to “CAML and Querying Boolean Fields”

  1. Marion says:

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

    Thanks for the tip!

  2. Dineth says:

    Great.. This helped me a lot.

  3. Rufat says:

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

  4. Jay says:

    This saved my time. Thanks Erkka

  5. Tom says:

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

  6. Tom says:

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

  7. Tom says:

    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:

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

  9. “Great.. This helped me a lot.”

  10. Gil says:

    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:

    that’s : ” 1″

  12. Serg says:

    Thanks! Useful post!

  13. Jocelyn says:

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

  14. Thanks Erkka,
    It was exactly what I needed.

  15. okosym says:

    This works too: 1

  16. okosym says:

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

  17. [...] 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:

    Using Bool solved my problem!
    Thanks

  19. csgo says:

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

Leave a Reply