3/05/2012

SharePoint: Search, Filter or Sort Lists from a Query String

 

The following works in SP 2007, SP 2010 and Office 365/SharePoint Online

 

Microsoft has generally done a good job of keeping new versions of SharePoint compatible with the older versions. While over time some features have dropped out of the documentation, most still work. An example is the Query String option to sort and filter a list. This was a feature from “SharePoint Team Services”, which is what they called SharePoint “back when”! The Query String feature we will look at here is still documented in an officially “archived” document on TechNet named “Using the URL to sort or filter a list” that can be found here: http://technet.microsoft.com/library/cc751316.aspx  As is often the case, I ran across the article by accident while searching for something else.

 

What’s a Query String?

A query string is a collection of more or more parameters added to the end of a URL. You see these throughout SharePoint and at most interactive web sites. As an example do a search using Bing.com for “techtrainingnotes”. After you click the search button your search text is appended to the URL and will look something like this: http://www.bing.com/search?q=techtrainingnotes.  The first parameter in a query string is identified with a question mark and each additional parameter is identified with and ampersand. Here the parameter name is “q” and the value is “techtrainingnotes”.

For the filter and sort tricks we will create a URL that looks something list this:

  http://intranet/sites/training/Lists/Tasks/AllItems.aspx?FilterName=Title&FilterMultiValue=Task 2

The first part, “http://intranet/sites/training/Lists/Tasks/AllItems.aspx” is the path to a view of a list and everything from the question mark to the end is the query string.

 

Filtering One or More Columns

To filter a task list to show just “In Progress” tasks you would first visit a view that included the Status column. The URL to this list would look something like this:

  intranet/sites/training/Lists/Tasks/AllItems.aspx

To filter this view you would add two query string parameters to the URL that specify the column to filter on and the value to filter for. This URL might look like this:

  intranet/sites/training/Lists/Tasks/AllItems.aspx?FilterField1=Title&FilterValue1=In Progress

 

You filter on more than column by adding additional FilterFieldX and FilterValueX parameters to the URL. To find all of Luis Bonifaz’s In Progress tasks you might use this query string:

  ?FilterField1=AssignedTo&FilterValue1=Luis Bonifaz&FilterField2=Status&FilterValue2=Not Started

 

Why “AssignedTo” and not “Assigned To”? List columns can have an internal name and a display name. You will always need to use the internal name in these queries. See “Finding the Real Column Name” at the end of this article to see how to find these internal names.

 

Notes:

  • There can be multiple filters per query named FilterField1, FilterField2, FilterField3, …, FilterFieldxxx
     
  • FilterNameX is the column we are searching in – this column must be displayed in the page… i.e. it must be in the current view.
     
  • FilterNameX is case sensitive!  A misspelled or miscapitalized FilterNameX column name causes the filter to be ignored and returns all rows from the list… So “Title” works and “title” does not.
     
  • FitlerNameX must be the internal name, which is not always the display name (details on how to find this name is at the end of this article)
     
  • FilterValueX is not case sensitive – searches for “in progress” and “In Progress” return the same results
     
  • FilterValueX does not support wild cards
     
  • All of the filters are AND’d together – i.e. the row in the list must match all of the filters to be displayed

 

Filtering a Single Column with Multiple Values and Wild Cards

The FilterField/FilterValue parameters can only find exact matches and only on a single value per column. You can also use “FilterName=” and “FilterMultiValue=” parameters to filter a column on more than one item or with wild cards. If you want to display all “In Progress” and all “Completed” tasks you could use this query string:

  ?FilterName=Status&FilterMultiValue=In Progress;Completed

You can also use wildcards in FilterMultiValue parameter:

  ?FilterName=Title&FilterMultiValue=*meeting*

 

You can combine FilterField/FilterValue with FilterName/FilterMultiValue when needed. Here’s an example to find all of Luis’s tasks with “meeting” in the task title:

  ?FilterField1=AssignedTo&FilterValue1=Luis Bonifaz&FilterName=Title&FilterMultiValue=*meeting*

 

Notes:

  • FilterName is the column we are searching in – this column must be displayed in the page… i.e. it must be in the current view.
     
  • FilterName is case sensitive!  A misspelled or miscapitalized FilterName causes the filter to be ignored and returns all rows from the list… So “Title” works and “title” does not
     
  • FitlerName must be the internal name, which is not always the display name (details on how to find this name is at the end of this article)
     
  • FilterMultiValue is not case sensitive – search for “in progress” and “In Progress” return the same results
     
  • FilterMultiValue supports wild cards! – Searching for “task*” finds “Task 1”, “Task 2” etc
     
  • As FilterMultValue contains the word “Multi” then you would expect to be able to filter on multiple terms… and you can – separate each term with a semicolon (;) like this:  &FilterMultiValue=In Progress;Not Started
    Be careful not to add any extra spaces. The following will not find “Not Started” because of the extra space after the semicolon:  &FilterMultiValue=In Progress; Not Started

You can sort too!

You can use the SortField/SortDir to sort on any column in the view:

    ?SortField=Title&SortDir=Desc

 

Sort can also be used with the other filters:

  ?FilterField1=AssignedTo&FilterValue1=Luis Bonifaz
     &FilterName=Title&FilterMultiValue=*meeting*
     &SortField=Title&SortDir=Desc

 

Filtering from Quick Launch and other Links

So how do you use this little tidbit of information? You can generally do what these filters do with a view. Views do have one have annoying limitation… you can only display the first 50 views created. Technically you can create more than 50, but only the first 50 get displayed. Besides, it’s kind of tedious creating all of those views.

Let’s say you have a list with your 350 retail stores and you wanted to be able to quickly display a view of the stores filtered by any one state. You could create the 50+ views (50 states plus the other postal codes), or you could:

  • Add links in Quick Launch to selected states:
        intranet/sites/training/Lists/Tasks/AllItems.aspx?FilterField1=State&FilterValue1=Ohio
     
  • Create a links list with all of the state codes and display it as a web part
     
  • Create a links list with all of the state codes, but display it as a dropdown list (http://techtrainingnotes.blogspot.com/2010/12/sharepoint-convert-links-list-to.html)
     
  • Create an HTML dropdown list with the state code and add a little JavaScript to create the filtered URL and redirect to it

 

Finding the Real Column Name!

The column name used as the FilterName parameter must be the internal name used by SharePoint, not the displayed name. As an example, in a task list there is a column named “% Complete”. The internal name of this column is “PercentComplete”. To find this name:

  1. Display the list
  2. Click the List (or Library) tab in the ribbon and click List (or Library) Settings
  3. Scroll to the bottom or the page and click the column name
  4. Explore the URL to find the internal name

image

intranet/sites/training/_layouts/FldEdit.aspx?List=%7B64C528B3%2D224B%2D4613%2D9D60%2D216300B5BCE0%7D&Field=PercentComplete

 

 

.

43 comments:

LJ said...

Hi, I bought your book and am loving it! I've only been doing this since Nov2012 so I'm new at this stuff! Question about the survey section of your book: I've created views (alot of views) that I'd like to delete...but how?

Mike Smith said...

Thanks for the kind comments about the book. As far as views, display the view, edit the view and click the Delete button. The only view you can't delete is the default view.

Unknown said...

Mike,

Great article! helps me tremendously. Question for you, From what I can tell, when configuring multiple filters, Sharepoint does an "AND" search. Do you know of a way to configure this to do an "OR" search?

thanks!

Mike

Mike Smith said...

> an "OR" search?

Not that I know of.

George Winters said...

Mike:
I've just found this blog and think it's terrific.

You say that the filterfield must be shown, but I am finding that it doesn't !

In MOSS 2007, I get values from
../Lists/Employee Directory/Initial FrontPage.aspx?FilterField1=WorkZip&FilterValue1=11753

Even though the field is not shown in the view :)

Anonymous said...

Wow! This is super helpful! The standard filter seems to work but when i try to do a multi filter, I get an "Exception from HRESULT: 0x81031904". Then it gives a Correlation ID GUID. Any ideas what this means or how can I fix it?

?FilterName=CourseName&FilterMultiValue-*2643*

Thanks!

Mike Smith said...

Anonymous,

If you typed it just as in you example above, change the "-" to "=".

?FilterName=CourseName&FilterMultiValue=*2643*

Also, if that column is numeric, then wild cards may not work.

Mike

Anonymous said...

Good information. What if you have more than one list displayed on a page and you want to target the filter to a specific list. Is this possible?

Mike Smith said...

Anonymous,

My guess is that it would be seen by both web parts.

Mike

Anonymous said...

Hi!

i was just wondering from your URL
intranet/sites/training/_layouts/FldEdit.aspx?List=%7B64C528B3%2D224B%2D4613%2D9D60%2D216300B5BCE0%7D&Field=PercentComplete

if you could explain what this is and if it has any specific use %7B64C528B3%2D224B%2D4613%2D9D60%2D216300B5BCE0%7D

Thanks !

Mike Smith said...

"%7B64C528B3%2D224B%2D4613%2D9D60%2D216300B5BCE0%7D" is the GUID (Globaly Unique Identifier) that is used internally by SharePoint for that list. In the example above the GUID is not imporatant in that you will be using the browser to navigate to the list settings and clicking the column name. The important part for this article is the internal column name at the end of the URL.

GUIDs are used everywhere in SharePoint. These article might supply a little more info for you:

http://techtrainingnotes.blogspot.com/2011/06/finding-sharepoint-guids-using.html

http://techtrainingnotes.blogspot.com/2012/02/sharepoint-finding-site-guid-sharepoint.html

http://techtrainingnotes.blogspot.com/2009/03/sharepoint-finding-sharepoint-guids.html

Mike

Anonymous said...

Just found this blog entry and it is great! Is there any way, if the search results in just one list item, to have that item automatically opened for editing?

Thanks

Mike B

Mike Smith said...

> if the search results in just one list item, to have that item automatically opened for editing?

Yes, with a little JavaScript. (so he says!). The code would need to check the number of rows returned, and if 1 then in 2010 call the built-in JavaScript function to open a dialog box or in 2007 do a page redirect to the edit page. Either way you will need to create the URL to the edit page. (but there is enough info in the displayed list to do so.)

Mike

Anonymous said...

how to use not equal to or greater than on the field

Mike Smith said...

> how to use not equal to or greater than on the field

That does not seem to be one of the options with this feature.

Anonymous said...

Thanks a lot !

FilterName=Title&FilterMultiValue=*pattern* was what I was looking for.

This gives me the "contains" function ! Thanks!

Martin

Anonymous said...

Hi,

I'm trying to filter a field to only those results where value is "blank/empty/null". Any thoughts on what the syntax for that would be - if supported via the string itself?

-mk

Anonymous said...

Is there a way filter by "Or" instead of "AND" when searching for a keyword in two list columns using query string filters. So if the keyworkd can be found either in column1 or column2.

Mike Smith said...

> Is there a way filter by "Or" instead of "AND"

Not that I've found.

Mike

gapletona said...

Great resource! I'm using this on a document library in 2013 and am getting extraneous results. In my particular use, I am filtering by case number. For example, I am looking for 2014-11-07-AZ. I get the results back but it will also randomly add another record in there with a different case number. In the view, I have items grouped by document type. Looking at the heading for that document type, it shows 1 document, but there are two in the list. Hopefully this is something easy.

Unknown said...

> "OR" search

There is one way I know of to accomplish this: by creating a calculated column to fulfill the desired comparison (only display this column in the particular view you’ll be using your URL query against). It's not pretty, but depending on your needs you could do it one of two ways:

Case 1: You have one specific OR criteria that you will use frequently. For example, you want to display all items that are either open, or were closed recently. You create a calculated column that returns "Y" if the Status column equals "Open" or if the Last Modified column is on or after two weeks ago: =IF(OR([Status]="Open",[LastModified]>=[Today]-14),"Y","N"). Filter for "Y" in this new column.

Case 2: You want to support any number of OR criteria against a particular set of columns. For example, you want to be able to supply any name and get results if the name is found in any of the name columns in a contact list - Name, Manager, Alternate Contact. Create a calculated column that simply concatenates all of those columns together: =[Name][Manager][AlternateContact] . Target this new column with your query instead of the other columns.

The only bad thing is that since the column you’re filtering has to be included in the view, you’ll have to put up with the fact that this column, which isn’t useful for purposes other than filtering, will still be displayed when viewing the list. You might be able to find a way to hide it after page load with some CSS or javascript without affecting the URL query, though.

Anonymous said...

I want the search to filter results based on a dropdown on the page.
How can the search box read value from a dropdown and add it to the url

Mike Smith said...

Anonymous,

You could create a select list and some JavaScript that redirects back to the same page. Maybe something like this:


<select name="FilterValue1"
onclick="yourJavaScriptToSubmit">
<option>select a name</option>
<option>Luis Bonifaz</option>
<option>Sam Jones</option>
<option>Susan Smith</option>
</select>



<script>
//your JS would build a URL
//that looks like this and then
//redirect:
//urlToYourPage?FilterField1=AssignedTo&?&FilterValue1=valueFromSelect

</script>

Mike

Andrea said...

Hi Mike

As usual, your blog post is very informative. Thanks for providing such great information.

I am using the multiple value filter on the ID column.

e.g. FilterName=ID&FilterMultiValue=188;393

The problem I noticed is that when the page loads, the little filter icon does not show to indicate there is a filter on the ID field and the filter can't be cleared using the 'Clear filter from ID' drop-down menu action. I have tested the same thing using a single value filter and it works.

e.g. FilterField1=ID&FilterValue1=188

Have you had any luck getting the filter clearing to work when using multiple values?

(SharePoint 2010, IE 11)

Thanks
-Andrea

Mike Smith said...


Andrea,

This seems to be a bug (or a feature!) in both 2010 and 2013. The only way to get the clear option to appear is to click the column dropdown and click one of the displayed choices (for your example: 188) and then you can click Clear Filters.

Mike

Paul said...

This is incredible! Do you know if I can use this method to change the view used in a library webpart?

Mike Smith said...

Paul,

It will work with library views. If you are asking about a library web part added to a page, it will still work, but will also attempt to filter all of the other list/library web parts on the page. I.e. it will also attempt to filter the Announcements and other web parts on the same page.

Mike

Anonymous said...

I don't think the syntax FilterName=Title&FilterMultiValue works on libraries (at least we've never been able to make it work on a library view). It does work for us on list.

Oddly enough the FilterField1 FilterValue1 syntax does work in a library view, you just can't use the wildcard characters.

benchow6157 said...

Nice article. You should probably mention FilterOp, too. For example, ...&FilterOp1=Neq to set the operator to Not Equal.

Andrey Myasnikoff said...

Thanks for the post! And what is the maximum number of values we can input in FilterMultiValue filter?

Andrey Myasnikoff said...
This comment has been removed by the author.
Andrey Myasnikoff said...

According to my tests, I managed to use maximun 30 values in 1 filter, though the max number of elements in the view are set to 5000. So I guess we have to create sharepoint views to filter large capacity of items

fursat said...

Hi Mike,

Great article, as always. I have a quick questions. I have a Site Page with two list web parts (Webpart 1 and Webpart 2). Both the web parts are different views of same list.

I want a link on the main page to this site page but want both the list web parts to be filtered.

My URL is something like:

http://test/SitePages/YearlyTotals.aspx?View={GUID1}&View={GUID2}&FilterField1=Title&FilterValue1=ABC

This link results in only the first View being filtered, while the second one is not.

Do you know what I am missing here?

Thanks,
Ricky

Jan Coetzee said...

Hi Mike,

Is there any way to hide the filter value in the URL?
I do a filter on a company name and the user can now display another companies details simply by changing the filter value in the URL which I want to avoid.

Thanks,
Jan Coetzee

Mike Smith said...

Ricky,

I have not tested with two web parts, but would have guessed that both would have been filtered.

I would try the URL without the GUIDs:
http://test/SitePages/YearlyTotals.aspx?FilterField1=Title&FilterValue1=ABC

Mike

Mike Smith said...

Jan,

> Is there any way to hide the filter value in the URL?

Not that I know of. Query strings in URLs are always "public".

Mike

Unknown said...

This was very helpful to me in creating a solution. I wanted to be able to filter any view to show the checked out documents. Took me a little playing around, but the solution I came up with was "?FilterName=CheckoutUser&FilterMultiValue=*?*". This filters any view to show checked out documents, in case anyone else has the same need.

Mike Smith said...

Thanks Brendan! Very nice find.

Mike

Peter Bowers said...

This post is from quite some time ago, but it seems to be absolutely unique - nobody else is giving clear documentation on this feature that I can find. WELL DONE and THANKS!

That said, on O365 I've tried on 2 custom lists and am unable to use the FilterMultiValue. I verified that I had the correct internal name by successfully filtering using ?FilterField1=x&FilterValue1=y but when I tried to use ?FilterName=x&FilterMultiValue=y it acts as if I didn't put these query strings in the URL at all - all items in the list are displayed just as if I deleted them.

I have made sure I am case sensitive, I have tried multiple columns of multiple types and everything I try shows exactly the same results - perfect with the single-value query but ignoring the multi-value query.

Any ideas what I might be doing wrong?

Unknown said...

You should make mention of useFiltersInViewXml=1 - necessary for sharepoint online, apparently, if you want to use FilterName/FilterMultiValue

Unknown said...

I was able to filter a list successfully using SharePoint on-prem, but when we upgraded to SharePoint Online, my filter is no longer working. It will work for an exact match, but I am trying to run a wildcard search (FilterOp=Contains) and, after hours and hours of trying different permutations, I can't get it working again. FilterName/FilterMultiValue doesn't work at all for me and throws a 'Something went wrong' error.

I am running the filter over a document library with the following URL:
/[libraryname]/Forms/[viewname.aspx]?FilterField1=projectid&FilterValue1=796209-0213&FilterOp=Contains

SharePoint displays exact matches, but seems to ignore the FilterOp parameter now.

And here is my unsuccessful FilterMultiValue attempt:
?useFiltersInViewXml=1&FilterName=projectid&FilterMultiValue=796209-0213

Please help?!!!

Mike Smith said...

Nothing from older versions is guaranteed to work in SharePoint Online. Features there come, go and change every week. Going forward, I would not expect any of the old on-prem tricks to work in SharePoint Online.

Anonymous said...

We were able to find a solution by using a search string in the URL, instead of a filter. It used this format:
https://[SharePoint Site]/sites/[Site Name]/[Folder Name]/Forms/AllItems.aspx?view=[View ID]&q=[Search String]
E.g. https://mysite.sharepoint.com/sites/programme/projects/Forms/AllItems.aspx?view=7&q=projectname

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.