Filtering and Sorting SharePoint List Views
Introduction
How do I filter thee? Let me count the ways!
You should that SharePoint usually offers lots of ways to do more or less the same thing, and, of course, filtering a list view is no exception! In this post I am going to talk a bit about the ways that you can do this.
Built-in URL Filtering
SharePoint’s list view controls (XsltListViewWebPart, ListViewWebPart, etc) recognize a number of parameters from the query string that can be used to filter and sort automatically a list view, without requiring any additional effort.
Filter by single value:
http://server/Lists/Tasks/AllItems.aspx?FilterField1=PercentComplete&FilterValue1=.5
FilterField must be an internal field’s name and FilterValue is case insensitive.
Filter by single value and an operator:
http://server/Lists/Tasks/AllItems.aspx?FilterField1=PercentComplete&FilterValue1=.5&FilterOp1=Neq
The list of possible operators (FilterOp) is:
-
Eq: equal (default);
-
Neq: not equal;
-
Gt: greater than;
-
Lt: less than;
-
Geq: greater or equal to;
-
Leq: less or equal to;
-
BeginsWith: the start of a string;
-
Contains: part of a string.
You can add up to 10 FilterField/FilterValue/FilterOp entries, the items must match all conditions (AND operator). If you wish to filter on an additional field of a lookup field, use this syntax:
Filter by one of multiple exact values:
http://server/Lists/Tasks/AllItems.aspx?FilterName=PercentComplete&FilterMultiValue=.5;1
FilterName is also the internal name of the field and the values in FilterMultiValue are treated as case-insensitive. Separate then by ;.
Filter by one of multiple partial values:
http://server/Lists/Tasks/AllItems.aspx?FilterName=Author&FilterMultiValue=*Peres*;*Ricardo*
http://server/Lists/Tasks/AllItems.aspx?FilterName=Author&FilterMultiValue=Ricardo*;*Peres
The * character acts as a wildcard, matching anything before or after. Placing a term between * is the same as using FilterField/FilterValue with the Contains operator in FilterOp.
Filter by a taxonomy field:
If you want to filter by a managed metadata field, you can either search by the display name using FilterField and FilterValue, or you can use the ids of the terms. A little known fact is that all usages of managed metadata values – values of fields of managed metadata type – are stored in the TaxonomyHiddenList hidden list. If you want to filter by an id of a term, you need to use the following syntax (search by one of two values with FilterOp=In, values separated by commas):
Or by a single value (no need for FilterOp):
The FilterLookupId=1 is only there to tell SharePoint that it needs to search not by value (FilterValue) passed in the query string, but by term pointed by it. You can find the ids for FilterValue in the TaxonomyHiddenList list.
Sorting:
http://server/Lists/Tasks/AllItems.aspx?SortField=PercentComplete&SortDir=Desc
Possible values for SortDir are pretty obvious:
-
Asc: ascending (default);
-
Desc: descending.
You can only sort by a single field.
Update: for SharePoint Online, you may need the useFiltersInViewXml=1 parameter too.
Filtering a XSLT Web Part by a Query String Parameter
The idea is to add a ParameterBinding whose Location is QueryString and to use this parameter in a CAML query:
1: <WebPartPages:XsltListViewWebPart runat="server" ListName="{A1ADD3D1-B21F-4F93-9B86-B1FE332424D0}" DisplayName="My List">
2: <XmlDefinition>
3: <View Type="HTML" DisplayName="All Items" Url="/Lists/MyList/AllItems.aspx">
4: <Query>
5: <Where>
6: <Or>
7: <Gt>
8: <FieldRef Name='SomeNumber'/>
9: <Value Type='Number'>{param}</Value>
10: </Gt>
11: <Contains>
12: <FieldRef Name='Author'/>
13: <Value Type='Text'><![CDATA[{param}]]></Value>
14: </Contains>
15: </Or>
16: </Where>
17: </Query>
18: <ViewFields>
19: <FieldRef Name="SomeNumber"/>
20: <FieldRef Name="Title"/>
21: </ViewFields>
22: <!-- all other properties ommitted for brevity -->
23: </View>
24: </XmlDefinition>
25: <ParameterBindings>
26: <ParameterBinding Name="param" Location="QueryString(param)"/>
27: <!-- all other parameter bindings ommitted for brevity -->
28: </ParameterBindings>
29: </WebPartPages:XsltListViewWebPart>
Notice that in case the param value is not present in the query string, the second condition in the Or disjunction will apply, which will try to match an empty string inside of the Author (Created By) field, which will always be true. Be careful, however, that while this works most of the time, if you specify a value for param that is indeed part of the creator of the field, it will be matched. As of now, I don’t have an 100% foolproof solution for this, if you know of any, do let me know!
Filtering by a Form Field
This is a variation of the previous technique, where we use a Location of Form in a ParameterBinding. In this case, however, filtering will only be applied when the page is post back. Add the following HTML to your page:
1: <input type="text" name="param"/>
2: <input type="submit" value="Search"/>
3: <input type="button" value="Clear" onclick="document.location.href = document.location.href"/>
And change the previous ParameterBinding to use the param input:
1: <ParameterBinding Name="param" Location="Form(param)"/>
The Search button will cause a postback, thus applying the filter, and the Clear button will navigate to the current page, using the GET verb, thus clearing the filter.
If instead of HTML we want to use a server side control, we certainly can:
1: <asp:TextBox runat="server" ID="param"/>
Just change the ParameterBinding to point to the Text property of this control instead:
1: <ParameterBinding Name="param" Location="Control(param,Text)"/>
Filtering by a Web Part Connection
Filter web parts:
Another option is to filter by a web part that implements any of the filtering interfaces, used for supplying a value to another web part.
SharePoint Enterprise contains a collection of filtering web parts. These cover a number of scenarios:
-
Filtering from a query string parameter;
-
Filtering from a text box;
-
Filtering from a date picker;
-
Filtering from a choice of values;
-
Filtering from a SharePoint list;
-
Filtering from a Business Connectivity Service;
-
Filtering from a SQL Server Analysis Services cube;
-
Filtering from current user properties (id, profile property);
-
Filtering from a field in the current page ().
For the sake of simplicity, let’s pick filtering from the query string. In a web part page’s edit mode, add the Query String (URL) Filter web part to a web part zone:
If you prefer, you can also add it directly on the markup, just replace the QueryStringParameterName and FilterName for whatever you want:
1: <%@ Register Tagprefix="SPSWC" Namespace="Microsoft.SharePoint.Portal.WebControls" Assembly="Microsoft.Office.Server.FilterControls, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
2:
3: <SPSWC:QueryStringFilterWebPart runat="server" QueryStringParameterName="Status" FilterName="Status" />
Next, add a connection from a list view web part in your page:
Again, you are free to do it in markup:
1: <WebPartPages:SPProxyWebPartManager runat="server">
2: <SPWebPartConnections>
3: <WebPartPages:SPWebPartConnection ConsumerConnectionPointID="DFWP Filter Consumer ID" ConsumerID="g_ef253891_70bd_4996_bf8c_eb19d3d83012" ProviderConnectionPointID="ITransformableFilterValues" ProviderID="g_3c7bedb8_d6d7_42db_85d1_98feabfe5a63">
4: <WebPartPages:TransformableFilterValuesToParametersTransformer ConsumerFieldNames="Status" ProviderFieldNames="Status"/>
5: <WebPartPages:SPWebPartConnection>
6: <SPWebPartConnections>
7: </WebPartPages:SPProxyWebPartManager>
8: <asp:ScriptManagerProxy runat="server" />
Do replace the ConsumerID and ProviderID by the actual IDs of your web parts, where ProviderID is the ID of the QueryStringFilterWebPart and ConsumerID is the ID of the list view web part and also the ConsumerFieldNames for the list field you want to filter on and ProviderFieldNames for the value you used in FilterName.
Filter by a custom web part:
All you need is to implement interface IWebPartField. Here’s a sample implementation of a web part that filters by a query string value, think of it as a poor man’s Query String Filter web part:
1: public class QueryStringFilterWebPart : WebPart, IWebPartField
2: {
3: [WebBrowsable(true)]
4: [WebDisplayName("Field")]
5: [WebDescription("The query string field")]
6: [Personalizable(PersonalizationScope.Shared)]
7: public String QueryStringField { get; set; }
8:
9: PropertyDescriptor IWebPartField.Schema
10: {
11: get
12: {
13: return (TypeDescriptor.GetProperties(this)["QueryStringField"]);
14: }
15: }
16:
17: void IWebPartField.GetFieldValue(FieldCallback callback)
18: {
19: callback(this.Request.QueryString[this.QueryStringField]);
20: }
21:
22: public String Value
23: {
24: get
25: {
26: return (this.Request.QueryString[this.QueryStringField]);
27: }
28: }
29: }
After you deploy this web part and add it to a page, you will be able to create a connection to a list view web part so as to enable its filtering.
Filtering an External List
An external list is filterable by a finder operation, which is an operation of type Read List that you configure on the external content type. You can use SharePoint Designer to create views on the external list on top of an existing Read List operation. In the view’s page, the finder method – the name of the operation, not its display name – is specified as a Method entry in the CAML for the View element in the XSLT list view web part, for example:
1: <View Name="{A3B221F8-A146-4880-9740-433195D4D788}" DisplayName="My Custom Finder" Url="/Lists/MyExternalList/MyCustomFinder.aspx">
2: <Method Name="MyCustomFinder"/>
3: <Query/>
4: <ViewFields/>
5: </View>
Of course, you can also specify parameters to a finder method (a filter):
1: <Method Name=”MyCustomFinder”>
2: <Filter Name=”MyParameter” Value=”{0}”/>
3: </Method>
Filtering by XSLT
The last technique is done on the “client-side”, through XSLT. This offers possibly the worst performance, since all records are retrieved before they are filtered, but the greatest flexibility, because you can apply conditional rules that would be very difficult, if not impossible, using the previous techniques. XSLT filtering can be applied on top of web part connections, external lists, etc.
So, in your XSLT list view web part, inside xsl:stylesheet, add something like:
1: <xsl:template match="/">
2: <xsl:for-each select="dsQueryResponse/Rows/Row">
3: <xsl:choose>
4: <xsl:when test="PercentComplete. > .5">
5: <p>Completion above 50%!</p>
6: </xsl:when>
7: <xsl:when test="PercentComplete. < .5">
8: <p>Completion below 50%!</p>
9: </xsl:when>
10: <xsl:otherwise>
11: <p>Completion exactly 50%!</p>
12: </xsl:otherwise>
13: </xsl:choose>
14: </xsl:for-each>
15: </xsl:template>
This goes through all the returned records and outputs a message depending on the value of the PercentComplete field. Because it is a number field, we must use the syntax PercentComplete. (a dot in the end).
Conclusion
As you can see, a lot of functionality is available without having to resort to programming, SharePoint is very good at it. XSLT offers the greatest flexibility, because you can use conditional logic to express your exact intents. Whenever possible, however, try to filter records at CAML level, because less data will be returned.