How does SharePoint handle AND / OR view filtering?

Question

Premise: For the example let's suppose I want to create a view that displays Pending items but filters out the Rejected. Since there are multiple approvers, both Col A and B must be used. When creating the filtered view, the following rules are used:

1. Column A is equal to "Pending" OR
2. Column B is equal to "Pending" AND
3. Column A is not equal to "Rejected" AND
4. Column B is not equal to "Rejected"

This could also be visualized as Rule 1 || Rule 2 && Rule 3 && Rule 4

Question: How does SharePoint group multiple AND/OR operands?

In my mind, it would be (Rule 1 || Rule 2) && Rule 3 && Rule 4 where all the OR operators are first compared, then that final T/F value is used against all of the AND operators.

Would this change if the order of items was changed?

Answer 1

The formula computation in your case will be - Rule 1 || (Rule 2 && Rule 3 && Rule 4) The conditions in operators are grouped such that the higher precedence is given to 'and' operator, followed by 'or'.

You can also check in the CAML Designer how the query is formed to validate your desired result before creating a view filter.

Answer 2

Actually, at least on SharePoint 2007, when you're using the GUI, your filters are going to be applied and grouped in the order they were entered, ignoring the "AND before OR" rule.

So, in your case:

1 || 2 && 3 && 4

will be treated like:

( ( 1 || 2 ) && 3 ) && 4 <=> ( 1 || 2 ) && 3 && 4

Which would give you the results you were expecting because you entered them in the correct order.

If you instead entered them in in the order:

4 && 3 && 2 || 1

It would be interpreted as:

( ( 4 && 3 ) && 2 ) || 1 <=> 1 || (2 && 3 && 4)

If you open up the CAML, you can see what the GUI is doing. Here's a blog post that walks through this with pictures and an example CAML (and how to see your own).

Answer 3

The precedence of Boolean operators are following conventional rules of Boolean algebra where AND operator is evaluated before OR operator.

If I understand your case correctly, to shows pending items but not rejected:

Column A is equal to "Pending" OR Column B is equal to "Pending" OR Column A is equal to "Pending" AND Column B is not equal to "Rejected" OR Column B is equal to "Pending" AND Column A is not equal to "Rejected" OR

Each of 4 groups is separated by OR operator. Conditions within each group are evaluated using AND operator.

Answer 4

There's a really helpful article about this issue at https://wave-tsg.com/the-problem-with-complex-filters-in-a-sharepoint-view/. But I'd like to point out a few other thoughts in addition to what that article says:

1. An idea I got from a colleague... with modern list views, you can set the filtering through the list UI, then save it, and then go to the view settings to make additional tweaks (like scoping it to "[Me]")
2. The article says re-ordering only works if you have one OR operator, but this is wrong. For example:

a AND ( b OR c OR d ) can be achieved by starting with the OR statements first. SharePoint will translate b OR c OR d AND a as ( (b OR c) OR d) AND a. Which works out to the same truth table as a AND ( b OR c OR d )