CAML Query and the SharePoint List Views Filtering

I have a list which resembles a Tasks List with an additional column ‘TaskCreated’ – a DateTime column.

I wanted to query the list with the following filters :

  1. Get all the items with status “Not Started”
  2. Get all the items with status “In Progress”
  3. Get all the items with status “Completed” AND the ‘Task Created’ is within the given number of days.
  4. Get all the items with status ‘Deferred” AND the ‘TaskCreated’ is within the given number of days.

I used the following CAML query to fetch the results :

oQuery.Query = String.Format(
“<Where>” +
“<Or>” +
“<Or>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>Not Started</Value>” +
“</Eq>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>In Progress</Value>” +
“</Eq>” +
“</Or>” +
“<Or>” +
“<And>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>Completed</Value>” +
“</Eq>” +
“<Geq>” +
“<FieldRef Name=’TaskCreated’/>” +
“<Value Type=’DateTime’ StorageTZ=’TRUE’>{0}</Value>” +
“</Geq>” +
“</And>” +
“<And>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>Deferred</Value>” +
“</Eq>” +
“<Geq>” +
“<FieldRef Name=’TaskCreated’/>” +
“<Value Type=’DateTime’ StorageTZ=’TRUE’>{1}</Value>” +
“</Geq>” +
“</And>” +
“</Or>” +
“</Or>” +
“</Where>”,
SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow.AddDays(Completed)),
SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow.AddDays(Failed)));

I was curious to know how SharePoint would have done if I had created a view out of it.

This is how it looks like . Since there are nested operators it is very difficult to relate to the the filters displayed ….If you have a complex view then you are better of by creating a custom view using code and the CAML rather than relying on the view creation UI of SharePoint.

image

The view as seen by Stramit CAML Viewer :

<Query>
<Where>
<Or>
<Or>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>Not Started</Value>
</Eq>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>In Progress</Value>
</Eq>
</Or>
<Or>
<And>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>Completed</Value>
</Eq>
<Geq>
<FieldRef Name=”TaskCreated” />
<Value Type=”DateTime” StorageTZ=”TRUE”>2009-11-16T22:05:59Z</Value>
</Geq>
</And>
<And>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>Deferred</Value>
</Eq>
<Geq>
<FieldRef Name=”TaskCreated” />
<Value Type=”DateTime” StorageTZ=”TRUE”>2009-11-16T22:05:59Z</Value>
</Geq>
</And>
</Or>
</Or>
</Where>
</Query>