Filtering SharePoint Lists using nested AND / OR operator precedence hierarchies

A few days ago we pushed out a silent minor update to FilterZen that finally gives you ultimate control over how to combine multiple SharePoint List filters. As the array of available operator options is now quite broad, this article provides a summary overview on how filters and values can be logically combined when filtering SharePoint Lists.

The "original style" of combining operators: AND for filters, OR for values (still the default today)

By default, FilterZen works similar to what you can achieve using only the out-of-box column header filtering menus: different filters are AND-combined, so with a City filter set to London and a Style filter set to Gothic, you would see only those results where both Columns (City AND Style) contain these values.

Because unlike the built-in filters, we also support multi-valued filters (so that you could for example select a few criteria for a multi-choice Column and see all results that contained at least one of your multiple choices), we default to OR for a single filter with multiple values, so with a Categories filter set to Lead; Partner, you would see only those results where the Categories Column would contain at least Lead OR Partner, or both. But these multiple Categories OR combinations are still, as a whole, AND-combined with your other filters (ie. City and Style using the above example).

This is still the default setup and sensibly useful for the majority of scenarios. But FilterZen's real strength is supporting rare edge cases and intricate real-world requirements.

So a few months ago, we started supporting "the other way around":

Now you could switch from the default OR behavior to AND for multi-valued individual filters. You can set this behavior on a per-filter basis. This was originally introduced to support value-range and date-span filters. For the above example, using AND instead of OR, you would now only see results whose Categories Column contains both Lead AND Partner.

At the same time, we also let you switch the AND default behavior for multiple filters with different names to OR, so for the above example, using OR instead of AND, you would now only see results where either the City Column contains London OR the Style Column contains Gothic, or both.

That step moved FilterZen from supporting 90% of use-cases to about 97%...

...but with the newest, we are now proudly supporting "100% of all potential SharePoint List filtering operator requirements". You can now create nested precedence hierarchies!

Imagine you want to show only results where Categories contains Lead OR Partner, AND City contains London OR Style contains Gothic. You may not be directly aware of this, but this implies a "precedence hierarchy", a tree-like structure that, translated into a "pseudo-formula", looks like this:

( ((Categories has Lead) OR (Categories has Partner)) AND ((City is London) OR (Style is Gothic)) )

You can see from the parentheses that the AND clause is at the "top" and combines the two OR clauses. In a "pseudo-functional" manner, you could also read it like this:

AND(OR(Categories=Lead,Categories=Partner), OR(City=London,Style=Gothic))

Out of the box, without custom CAML coding inside a custom .NET-coded Web Part, you had no way of specifying such queries in SharePoint until now. However, the newest FilterZen build now lets you activate a precedence hierarchy using a very simple syntax that is much easier than CAML or any .NET language, and much shorter too. Good news: it's not even Real Coding, and more like a "minor config line of some esoteric format that is however fairly easy to grok". The above example would be defined as follows:

{ "AND": [ "Categories", { "OR": [ "City", "Style" ] } ] }

In case the curly braces and square brackets look foreign to you, the syntax is called and after a few minutes of familiarization this should be easy for any admin or web worker (keep in mind the above will not be done by your SharePoint end users, only by the people setting up Filter Web Parts). Of course, this syntax also implies you have set up your FilterZen filters for Categories, City and Style respectively.

Furthermore, if you like to experiment, just let FilterZen guide you through a trial-and-error process of specifying your precedence hierarchy: once you have a simple {} in there, FilterZen will give you warning messages whenever you click Apply and something isn't quite right with your precedence hierarchy definition. It's much easier than this sounds, and we can't simplify it much until we will give you a real micro UI for this in the upcoming 4.0 release. But really I don't think there's any reason to wait for that if you need a precedence hierarchy, and if you really get stuck we will be able to fix your JSON in a matter of seconds, so as usual don't hesitate to ask for support, preferably in the discussion forum.

Happy SharePoint-List-filter-operator-precedence-hierarchy-defining!