Search This Blog

Sunday, August 14, 2011

Understanding the Filters in SharePoint 2010 List Views

Lists are fundamental to SharePoint. Views are fundamental to lists. It’s therefore important to understand exactly what’s going on when you set up a new view in a SharePoint list. The number of items to display, which columns to show and even the sort order are easily understood – however I’ve seen people become unglued when it comes to setting up the filters on the list (including me!).

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:



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:




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.




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.



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!
Confused? Me too a little. But the important thing is to remember that the conditions in operators are grouped from the beginning!



11 comments:

  1. Good catch

    - Ricky

    ReplyDelete
  2. What if I want:

    (A or B or C) and (D or E)

    Thanks

    Lee.

    ReplyDelete
  3. Thanks for the tip!

    ReplyDelete
  4. good post
    HOWEVER, WHY DONT YOU LIKE TO ALLOW YOUR USERS TO NAVIGATE BACK TO WHERE THEY CAME FROM

    ReplyDelete
  5. Excellent explanation...but as Lee mentioned previously, what about "(A or B or C) and (D or E)" situation?

    ReplyDelete
  6. Yet another reason why SharePoint is retarded.

    ReplyDelete
  7. hi!,I like your writing so a lot! share we communicate more approximately your post on
    AOL? I need an expert on this space to solve my problem. Maybe that's you! Looking forward to peer you.

    Also visit my blog post: view it now

    ReplyDelete
  8. For users asking about the (A or B or C) and (D or E) problem...

    There are 2 ways I've been solving this. One is to create a calculated feild that summarizes your filter or parts of your filter.

    Sometimes you can't use a calculated field. In this case, you will need to use a workflow to set a field's value, and you may filter on this field.

    ReplyDelete
  9. Aging is one primary cause of cellulite zumba,
    with mixed results. The more weight we gain the greater the pressure.

    Drink about two to three exercises for each body part.
    Revitol comes with a special gel, messenger roller, and an active air suction.


    Also visit my blog: cellulite and diet

    ReplyDelete
  10. The child can easily pick up any easy gig bags for eastman er3
    song for beginners and start playing.

    Here is my weblog - http://rantingsfromflorida.blogspot.com/2010/05/ever-changing-fifth.html

    ReplyDelete
  11. Through a tiny incision the laser on the end of chapter three.
    This is why more women suffer from skins than men? But with books you can use it at home when ever you
    feel like it. I hate it I was getting out of the tiny
    opening. ''None of this caters for me, but I also work at it.


    Here is my blog - cellulite remedies

    ReplyDelete