Understanding Filters in SharePoint List Views [Solved]

Understanding Filters in SharePoint List Views

The other day I had a particularly strained query I needed to configure so I set about understanding exactly what goes on when you set up filters in a view. For some reason it’s not something I’d ever really dug into before, so it was a eureka moment when I realised that I could look at the CAML of a view after I had created it.

[Obviously I knew that each view had CAML underpinning it – and I knew it was possible to get the schema for a list, including the views, through the UI – so why didn’t I do this years ago!?]

So let’s walk through an example. First I’ll knock up a task list with a few items in it:

2011-08-10-UnderstandingFilters-01.PNG

Now, presuming that I want to see items that are related to SharePoint and have a status of In Progress or Completed, I’ll create a view in the list to show items that:

Contain the word ‘SharePoint’ in the Title field
AND
Have a Status equal to ‘In Progress’
OR
Have a Status equal to ‘Completed’

I do this with the following configuration:

2011-08-10-UnderstandingFilters-02.PNG

I’m sure those familiar with list views can already see a problem with this, but actually it seems to read ok. Biased with my expectations of what I want the list to do, I’m in effect hoping for parenthesis around the last two statements in the filter I’ve setup.

But look at the results of this filter. I’m getting an entry in there which is not what I expected as it only fills half of my criteria, e.g. it has a Status of ‘Completed’ but it does not contain the word ‘SharePoint’ in the Title.

2011-08-10-UnderstandingFilters-03.PNG

Let’s inspect what’s gone wrong here. First I want to take a look at the underlying CAML associated with this view. To do that I can use the useful owssvr.dll trick in the URL to ask for an XML dump of the list schema with something like (more information here):
http://www.synergyonline.com/blog/blog-moss/Lists/Posts/Post.aspx?ID=24

http://…/site/_vti_bin/owssvr.dll?Cmd=ExportList&List={LIST GUID}

Taking a look at the generated XML I can quickly find the relevant section for my new view. There I can see the where clause of the query constructed like so:

01 <Where>
02     <Or>
03         <And>
04             <Contains>
05                 <FieldRef Name="Title"/>
06                 <Value Type="Text">SharePoint</Value>
07             </Contains>
08             <Eq>
09                 <FieldRef Name="Status"/>
10                 <Value Type="Text">In Progress</Value>
11             </Eq>
12         </And>
13         <Eq>
14             <FieldRef Name="Status"/>
15             <Value Type="Text">Completed</Value>
16         </Eq>
17     </Or>
18 </Where>

Now the problem starts to become apparent. My query will return any list items with a status of Completed. The mistake – an easy one to make – was in thinking that the query would be ‘partitioned’ on the last possible grouping in the filter. It actually works the other way around, i.e. you should always assume that the operators are taking effect between all conditions on the left of the filter and the single next condition.

We can therefore easily re-order our filters so that the Title field condition is in effect carried out on the other half of an And operator – thus giving the expected results.

2011-08-10-UnderstandingFilters-04.PNG

Given our new understanding of exactly how filters work in list views – how can we apply this? Well, I think the major thing will be to make sure that we don’t have any mistaken assumptions when setting up filters, and to try and work through the filter logic from first to last.

For those particularly difficult filters we may also want to think about viewing the query as a mathematical equation – and then seeing if we can re-arrange our equation to fit into the constraints of the logic that must be applied to filtered views.

So for example our scenario above could be thoughts of as:

A AND (B OR C)

With:
A = Title contains ‘SharePoint’
B = Status equals ‘In Progress’
C = Status equals ‘Completed’

When we put A AND B OR C into the filter of a view, we actually got (A AND B) OR C. Knowing this, we were able to restructure the query so that we entered C OR B AND A to give us (C OR B) AND A – which is actually the filter we wanted!

Credit: https://www.nothingbutsharepoint.com/sites/eusp/pages/understanding-filters-in-sharepoint-list-views.aspx