
Learn to Use CAML Query – Part 1
Collaborative Application Markup Language (CAML) may be used in BrightWork to reports and metrics.
CAML language that can be used to query a list or report to bring back more precise information that list filters may not be able to do too easily. In part 1, we take a look at CAML Syntax, and CAML joins.
CAML Syntax
When using CAML in BrightWork, the syntax used is similar to the following:
<Where> .......... Query </Where>
The Order By and Group By attributes are not used. These attributes are managed with BrightWork Reporter instead.
In the following example, this query is pulling in a list where all items in the “Predecessors” column are null.
<Where> <IsNull> <FieldRef Name='Predecessors' /> </IsNull> </Where>
CAML Joins
There are 2 ways to perform a logical CAML join. These can be nested together to perform complex queries.
AND Join
Where 2 or more statements must be true to return a value e.g. Return the values where the Status is (4) Deferred, and the Priority is (1) High
<Where> <And> <Eq> <FieldRef Name='Status' /> <Value Type='Choice'>(4) Deferred</Value> </Eq> <Eq> <FieldRef Name='Priority' /> <Value Type='Choice'>(1) High</Value> </Eq> </And> </Where>
OR Join
This where one or the other statements must be true to return a value.
<Where> <Or> <Eq> <FieldRef Name='Status' /> <Value Type='Choice'>(4) Deferred</Value> </Eq> <Eq> <FieldRef Name='Priority' /> <Value Type='Choice'>(1) High</Value> </Eq> </Or> </Where>
Return the values where the Status is (4) Deferred, or the Priority is (1) High
Nested Join
A nested join is where you use several And or Or statements together to bring back values from the query. These can get complex, so it’s advisable to break down how you want the statement to look and what you would like returned.
Here, it query looks to see if the (Status Equals (4) Deferred AND Owner is not null) OR (Priority Equals (1) High AND Owner is not null)
<Where> <Or> <And> <Eq> <FieldRef Name='Status' /> <Value Type='Choice'>(4) Deferred</Value> </Eq> <IsNotNull> <FieldRef Name='Owner' /> </IsNotNull> </And> <And> <Eq> <FieldRef Name='Priority' /> <Value Type='Choice'>(1) High</Value> </Eq> <IsNotNull> <FieldRef Name='Owner' /> </IsNotNull> </And> </Or> </Where>
Over the coming weeks and months, I’ll post more about how you can use CAML code within BrightWork to advance the configuration of BrightWork Reports and metrics. If there is anything specific, please post in the comments below. Or search our blog to find more interesting articles on CAML, including Making use of Shared Filters and the Web Part Gallery.
Update: Read Learn CAML Statements Part 2 here where I look at comparison operators.