Tuesday, June 9, 2009

Advanced List View Filtering

The filter section of views is limited at best, and in some cases it is just wrong. For example if you have a list and you want to filter based on this boolean expression:
Expression 1: ((Column1=A OR Column2=B) AND Column3=C) OR Column4=D
you would think you could expand the expression (because list view filters don't have parentheses) to
Expression 2: Column4=D OR Column3=C AND Column1=A OR Column3=C AND Column2=B
The SharePoint UI will group the expression in a different way, not based on the order of operations (often giving you empty results, and always giving you the wrong results)
However, SharePoint designer allows you to modify the filter yourself (of course it's kind of a pain).
Steps
  1. First make sure you've attempted to put your filter expression into view through the UI (this will place the appropriate columns in your Where clause so it will be easier to move things around.
  2. Open the list view page in SharePoint designer
  3. Find the <listviewxml> node
  4. Between the node you'll see that there is a lot of cryptic looking text that has a lot of &lt; and &gt; markup in it. This is the XML markup for the list view definition, we'll need to modify a part of this to alter the list view correctly
  5. Within this cryptic text look for '<Where', this is the beginning tag of the where clause. Once you find it, place your cursor before the '&' and highlight to the '\Where>'
  6. Copy this entire expression and paste it into notepad (or any other text editor)
  7. Do a replace on the text, replacing '&lt;' with '<' and '&gt;' with '>', now the text looks like xml (optionally, put in line breaks to make it easier to read)
  8. Now you have to build your expression, my suggestion is to start with the outermost expression and work your way in
    For example, Expression 1 builds up like so (this example doesn't include the correct syntax for the columns, only the logic):
    <where>

      <or>

        <column4=D>

        <and>

          <column3=C>

          <or>

              <column2=B>

              <column1=A>

          </or>

        </and>

      </or>

    </where>
  9. When you finally have it correct, remove your line breaks, and do a replace swapping '<' for '&lt;' and '>' for '&gt;'
  10. Paste the new where clause over the old where clause and save the page (this will customize the page from the site defintion)
  11. Test

No comments: