SharePoint View Filter order of precedence.

SharePoint Queries order of precedence.

WSS has the ability to build complex ad-hoc views of list data. But, configurators often run into strange results when mixing “and” and “or” operations in the filter section of the view.

Consider this example:

I want to create a view called “Overdue Tasks” which will display only those tasks that do not have a “Completed” status. I also want to limit the view to items with a due date that is either null or before today.

In a logical form, I’m looking for:
(Status <> Completed) AND ( (DueDate = null) OR (DueDate < Today) )

If I here to hand-write a CAML query (see http://msdn.microsoft.com/en-us/library/ms467521.aspx) it might look like this:

I used the following data set to make sure I configured the view correctly:

Given my logical conditions and the above dataset, I can expect to see two entries returned from the query: “Not Complete and Late” and “Not Complete no Due Date”

Initially, I tried to configure this query like so:

And got these incorrect results:

Reviewing examples above you can see that the order of precedence is important. I want WSS to consider the OR operation before considering the AND operation. It turns out the Edit View tool makes this simple if we remember a couple simple rules.

1. All logical operations occur from top to bottom.
2. Earlier logical operations are nested within later logical operations.

If I examine my logical form for the previous filter settings, I can immediately see my error.
( (Status = Completed) AND (Due Date = null) ) OR (Due Date = Today)

I need to restructure my logical form with the above rules in mind. Doing so produces this:
( (DueDate = null) OR (DueDate < Today) ) AND (Status <> Completed)

Which leads me to configure the view like this:

And get the expected results:

Indeed, pulling the CAML query returns the following:

Notice that the only real difference between the initial hand-written query and the WSS-generated one is the Neq clause moved from the beginning of the query to the end. The Neq clause is still nested within the And clause, but after the Or clause (which doesn’t cause any problems because the “And” operation is commutative. See http://en.wikipedia.org/wiki/Commutativity).

Happy view building!

~ED