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

 

 

.

17 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

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.