I’ll let my first post be about CAML queries in Sharepoint…
I was trying to write Caml Query to retrieve list items from Sharepoint, so I decided to write a post about that. First; Operators in Caml Queries are;
- Eq Equals
- Neq Not equal
- Gt Greater than
- Ge Greater than or equal
- Lt Lower than
- Le Lower than
- IsNull Is null
- BeginsWith Begins with
- Contains Contains
Here comes a simple query, which can get list items whose FieldA property is equal to ChoiceB;
<Where>
<Eq>
<FieldRef Name=”FieldA” />
<Value Type=”Choice”>ValueB</Value>
</Eq>
</Where>
You can add as many filters as you like by using And or Or. And lets compare Date values now;
<Where>
<And>
<Leq>
<FieldRef Name=”FieldStartDate” />
<Value Type=”DateTime” IncludeTimeValue=”False”>
<Today/>
</Value>
</Leq>
<Geq>
<FieldRef Name=”FieldEndDate” />
<Value Type=”DateTime” IncludeTimeValue=”False”>
<Today/>
</Value>
</Geq>
</And>
</Where>
By the way, you can use something like <Today OffsetDays=”+3” /> if you want to say 3 days after today.
To get previous days use (-) (negative numbers), or to get next days use (+) (positive numbers)
I searched but could not find something like OffsetMonths, if you know, please let me know.
Also I should say, one of the common mistakes, I am always doing; writing more than 2 statements inside an “And” or “Or”. You should not write more than 2 statements!
Just write it like;
<Where>
<And>
<And>
<Leq>
…..
</Leq>
<Geq>
…..
</Geq>
</And>
<Geq>
…..
</Geq>
</And>
</Where>
<OrderBy>
<FieldRef Name=”FieldA” Ascending=”True”/>
</OrderBy>
Take a look at my final CAML query; (just as an example)
<Query>
<Where>
<And>
<Eq>
<FieldRef Name=”FieldA” />
<Value Type=”Choice”>ChoiceA</Value>
</Eq>
<Or>
<Or>
<And>
<Geq>
<FieldRef Name=”FieldStartDate” />
<Value Type=”DateTime” IncludeTimeValue=”False”>
<Today/>
</Value>
</Geq>
<Leq>
<FieldRef Name=”FieldStartDate” />
<Value Type=”DateTime” IncludeTimeValue=”False”>
<Today OffsetDays=”+10″ />
</Value>
</Leq>
</And>
<And>
<Geq>
<FieldRef Name=”FieldEndDate” />
<Value Type=”DateTime” IncludeTimeValue=”False”>
<Today />
</Value>
</Geq>
<Leq>
<FieldRef Name=”FieldEndDate” />
<Value Type=”DateTime” IncludeTimeValue=”False”>
<Today OffsetDays=”+10″ />
</Value>
</Leq>
</And>
</Or>
<And>
<Leq>
<FieldRef Name=”FieldStartDate” />
<Value Type=”DateTime” IncludeTimeValue=”False”>
<Today />
</Value>
</Leq>
<Geq>
<FieldRef Name=”FieldEndDate” />
<Value Type=”DateTime” IncludeTimeValue=”False”>
<Today OffsetDays=”+10″ />
</Value>
</Geq>
</And>
</Or>
</And>
</Where>
<OrderBy>
<FieldRef Name=”FieldStartDate” Ascending=”True” />
</OrderBy>
</Query>
Found out that there is a CAML Builder tool, you can check that out from this site…