Thursday, November 14, 2013

Searchlike filtering of lists

Watch the screencast of this blog post

When you have content in SharePoint lists or libraries you want to narrow down, you have two options: You can search and you can filter.

Search gives you the flexibility to find keywords in any part of your items columns, like in the Title, Description or in the people fields like Created By. But search rely on the Crawler and its successful schedule and the result is presented in a 'not list like' format.

Filtering is done directly on the list and therefore preserves the list look, but to filter a list you need to type the exact content of a column, not just part of it. That's because a filter by default is an Equals comparison.

With a little trick found many places on the Internet, e.g at WonderLaura, we can get the best of search and filter to work together. I'll show you my version of it with a little twist that enables you to view all items when no search filter is applied.

Let's say you have a list that contains information's about the books you have in your library and you want to be able to filter the books based on their content. In this example the list is called Team Library.

This is how it's done:

1. Start by creating a new view on your list or library for the purpose, I call mine FilterSearch and base it on the AllItems view.

2. Now on the Ribbon click List and then Edit List. This will startup SharePoint Designer
3. Once loaded click on your View in the upper right corner. NOTE: This may require Site Collection privilege dependant on your site and the setup of your masterpage. Should you get a recurring request to logon as a different user, you need to ask your local business supporter or a SharePoint Administrator person for help with steps 4 - 6. 
4. Now click on the (XsltListView) web part to mark it and then click on Parameters

5. In the Data View Parameters dialog, click New Parameter and give it a name, mine is called SearchFilter. Add a default value of the letter e and click OK.

6. Now simply hit the Save button and close SharePoint Designer


7. Back at the SharePoint list, refresh your page by hitting F5, then click Site Actions, Edit Page


8.Click Add a Web Part, choose Forms, HTML Form Web Part and click Add.


9 .You might want to Edit the Web Part and change the Title, remember to set the Chrome Type to Title Only. Here mine is called Search the Team Library.

10. In your list's (again mine is called Team Library) Web Part dropdown menu select Connections, Get Parameters From, and select your web part (Mine: Search the Team Library)
11. In the Configure Connection dialog, bind together T1 and SearchFilter and click Finish.

12. Now select to Edit Web Part in the dropdown and click on Edit the current view


13. This is where we define which fields we want to search in, note that we select the condition Contains, that each filter is OR'ed together and the special syntax of the {SearchFilter}.


14. First thing you might notice when you click OK is that the list now shows up as empty. Now why is that? That's because we are requesting all the items with empty or NULL in their columns and because they have content we get nothing back. I think:-). Luckily we can apply an almost perfect workaround.

For those of you that does not have the Text Filter (below) please skip to the UPDATE section at the end of this blog post.
15. Add another Web Part, this time choose Filters, Text Filter and click Add


16. Edit the Web Part, click Advanced Filter Options and set the Default Value to something that is most likely to exist in the columns you choose to search. Unfortunately you can't search for a space, that's ignored; I found that e is almost always found in one or more columns. Period (.) could also be an option. Set the Chrome State to Minimized and the Chrome Type to None to hide the Web Part. Now click APPLY.


17. While still in Edit Mode, from the dropdown menu, select Connections, Send Filter values to, Team Library


18. In the dialog change the Connection Type to Get Parameters From and click Configure.


19. Accept the default and click Finish.


20. We now have two Web Parts that sends parameters to our list, one of them hidden. 
21. In the Ribbon, click Page, then Stop Editing.
Now the list content is showing and we can start searching (the highlighting is by me)


If you are not getting the desired results you need to ensure that the Team Library web part is getting Parameters from both Web Parts

So, that's it, enjoy search filtering!

Update

For those of you that does not have the Text filter web part available, here is a work around using only one HTML Form Web Part. 

  1. First note that step 5 above has changed and that the Parameter now has a default value of e.
  2. Edit the Page and Edit the HTML Form Web Part (or add one if you haven't got on already)
  3. Click the Source Editor... button.
  4. Replace the default html code with the code found here:
    If you have made any changes to this html code yourself you will have to redo them
Now click Save, OK and Stop Editing

So what does this code change do? First I have given the Search textbox an ID so the we can read the value of it. Once the user click GO or hits Enter we call a small function that checks if the textbox is empty. If it is empty the code sets the value to "e" and then performs the submit. Once the page is reloaded the last nameless function checks if the textbox contains only the "e" and then removes it.

Buttomline is that if the textbox is empty the code will temporary add the "e" as a default value. The drawback of this solution is that it is visible for a short time, but if you can live with that you should be good to go.


80 comments:

  1. Hi Jesper,

    When I get to pint 11. In the Configure Connection dialog, I get a message saying “No Consumer Schema”. Could you kindly help please.

    Lyn

    ReplyDelete
    Replies
    1. I have only seen this if the list view web part does not have a parameter, so you need to make sure that step 2 to 6 are done correctly and throws no error.
      It's very important that you select the list view web part by clicking on it, before you click on Parameters. Have you tried watching the screen cast?

      Delete
  2. I am having problems connecting the web parts in 2010. When doing step 11 my Connection Settings say “No Consumer Schema”. All the other steps seemed to have worked fine. Any help would be greatly appreciated.

    Thank you

    ReplyDelete
    Replies
    1. And the same here: I have only seen this if the list view web part does not have a parameter, so you need to make sure that step 2 to 6 are done correctly and throws no error.
      It's very important that you select the list view web part by clicking on it, before you click on Parameters. Have you tried watching the screen cast?

      Delete
  3. Thank you Jesper. Your solution a slighty easier, then WonderLaura's. Works great!
    There is a way to get to filter on multiple columns at once? For example I type in the filter box: "Mr. Bell 01.04" and the filter must show record that the book is taken by Mr. Bell 01.04.1999.

    ReplyDelete
    Replies
    1. Thanks for your kind words. If I understand your question correct, then that is in fact what I do in step 13: If Title OR Description OR ISBN OR Loaned By CONTAINS the value of the SearchFilter then it is shown. And you can combine that in any way you like by using the standard SharePoint view dialog.

      Delete
  4. Excellent tutorial Jesper, many thanks.

    I've followed your steps and watched your video. When I try to modify filters in the view, SP gives me an error when I choose a numeric column. Any ideas on how I can solve this?

    ReplyDelete
    Replies
    1. Not sure what error you are getting, but I'm guessing it could be something to do with numeric vs string. What I would try is to add a Calculated column, have be equal to your numeric column and let its output be string. Then try filtering on that column. Let us know if it works

      Delete
    2. No, doesn't seem to work, no error, but no function

      Delete
    3. A somewhat bulky way of achieving this is to create a Single line of text column e.g. named NumericAsString and then create a workflow that copies the Numeric value to the NumericAsString whenever an item is created or modified and then filter on that column, that actually works

      Delete
  5. how do you do a filter on column that has multiple choices i received an error on mine took that off the column and filter worked. would really like to have that column in there though is possible

    ReplyDelete
    Replies
    1. I just created a Choice column with Checkboxes (allow multiple selections) with the choices Yes, No, Maybe and I could filter on that with no errors. Which error did you get?

      Delete
  6. Hi Jasper, this is a great research, i have played the YouTube video 'a good few times'.

    I do not have the Text Filter webpart on my version of SP2010. Therefore I can not display all the records when the user lands on the page. I am guessing most won’t have this webpart, do you know of a work round, and i would appreciate a step by step guide.

    I thank you in advance.

    by this is great work.

    ReplyDelete
    Replies
    1. Hi Moses. Please see the Update section above.

      Delete
  7. Thanks, a beautiful solution for wildcard filtering on a list\library. Shame Microsoft do not expand the functionality of their webparts.

    ReplyDelete
    Replies
    1. this is a good point, for me MS has seriously missed a lot of out of the box features which I would expect from something like sharepoint, very dissapointng to have to jump through these hoops. Let's face it this implementation is not exactly straight forward either - and we have to have that special 'e' filter added too which just shouldn't be required

      Delete
  8. Great Post - have any idea how to enhance it to do...

    When user clicks 'Go' have the parameter entered copied, then have IE open ctrl+F then paste paramter so it is highlighted?

    Just a thought :)

    ReplyDelete
  9. Is there a way to have it search the contents of a document why trying to filter a document library?

    ReplyDelete
    Replies
    1. It boils down to being able to expose that content in a column, which again depends on the type of document. You may have to restructure your documents, say use/create multiline text columns in the doclib, copy the content to the column e.g. using a workflow and insert the column in the document using Quick Parts. And then filter on that column.

      Delete
  10. Very nice. I can get this to work when I create a web part page within the document library. However I have similar problems as others (“No Consumer Schema”) at step 11 if I create a web part page within the site pages directory. I'd like to have this wild card search work on a site page rather than part of the document library -- is this possible? - Stu

    ReplyDelete
    Replies
    1. Hm, I just tried it and did not encounter any issues. I created a new view based on the AllPages view, opened the site in SPD, browsed List and Libraries, Site Pages, Views, and then clicked on the XsltListViewWebPart and added the Parameter. Then just followed the rest of the steps and could make the connection with no issues

      Delete
  11. Hi,

    But it doesn't search document inside the folders.
    can you please help.

    Regards,

    ReplyDelete
    Replies
    1. It is still a filter function so it will only work on what's visible. What you could do is to change the view of the listviewwebpart under the Folders option to Show all items without folders.

      Delete
    2. Thanks Jesper...

      Worked like a charm...But again ran into an issue.. it doesn't filter the document on one click, instead work on second click.

      Please let me know, if i am wrong somewhere..

      Delete
  12. Hey Jesper...

    This is great! But it *almost* does what I need, so as a struggling noobie I am hoping you (or someone here) can help me modify the provided javascript to do exactly what I am trying to do.

    I am hoping that this is just a simple coding error but let me give you some context. I am using your method to search for list item numbers and I seem to have made all the required adjustments to get it to work but for the piece where you use the provided javascript to populate the list with everything so it can therefore be filtered down I am having to use the number "1" instead of the letter "e" (because clearly list item IDs generated by sharepoint do not include letters.

    The problem with this adjustment is that by using "1" I can get the list to display any number that includes a 1 but this does not account for all of them. My understanding is that I can not define a value of "1 or 2 or 3 or 4" etc (in the if condition that populates the text box with an intial default value to search for) and was originally this line = document.getElementById("tbFilter").value = "e"; ) so I am trying to use an array. Unfortunately I have only the most basic understanding of what an array is and how to use it so when I define the document.getElementById("tbFilter").value as = ["1","2","3"]; it fails to yield the proper result when I click the associated button.

    Perhaps this is a misapplication of the array variable itself and perhaps I simply have my syntax wrong but I am not sure. Maybe in this context it can not be used at all for some reason I do not yet understand. In any case if you (or anyone reading) have any guidance I would greatly appreciate it.

    ReplyDelete
    Replies
    1. barring the use of an array is it possible to either define a wildcard or range of values (ie. 0-9) as the value that get's plugged into the search box?

      Delete
  13. Hi,
    This is great that I found this article and it fully implemented on what I am looking for... Many Thanks

    ReplyDelete
  14. Great article and thanks for sharing. Is there a way to set this feature up to search dates on a list or library?

    ReplyDelete
  15. Replies
    1. There are some smart people that seems to have found the solution for SP 2013, so head over to my YouTube post on this subject and look through the comments starting at rishi2089's comment

      Delete
  16. Works great in 2013. Followed the video and only had a poroiblem with seeing the T1 in the connection window. When back found my error and corrected. Now is working great.

    ReplyDelete
    Replies
    1. What was the error and what was the correction for SP 2013?

      Delete
    2. I was getting a "No consumer schema" error. I went back into my list and selected my new view, then tried again and received the option to select T1 and my new SearchFilter.

      Delete
  17. Hi Jesper,
    Thank you SO much for this, it's exactly what I was looking for, and works a treat! I have a problem though, that I hope you may be able to help with?
    When I add the HTML Form Web Part, the ribbon disappears from the top of my page, which means I lose all navigation and editing capabilities. If I need to change any List Settings, I have to delete the web part by going into the Edit Page Function, then make my changes and re-insert the Web Part - not ideal!
    I notice that your ribbon stays throughout your video - please can you tell me how I can achieve this?
    Many thanks in advance,
    Hannah

    ReplyDelete
    Replies
    1. Hi Hannah. Oh that troublesome Ribbon:) Go ask Mr. Google about disappearing ribbon and he will talk a lot. I can't remember to have done anything special to keep the ribbon, but I noticed one answer that you could give a try: http://social.technet.microsoft.com/wiki/contents/articles/31204.ribbon-missing-on-sharepoint-list-or-library-page.aspx

      Delete
    2. Hi Jesper,
      Thanks for getting back to me!
      I've given that a go, and unfortunately it's not doing the trick... - I've created a work-around though, keeping the "All Items" view (where the ribbon still shows) as the default, but adding a hyperlink to the Search view, in a CEWP. Once in the Search view, users can navigate back to the All Items by using the Quick Launch column. Not perfect, and it's still a bit of a pain for me to edit, but it'll do!
      Pesky Ribbon... :-(
      Thanks,
      Hannah

      Delete
  18. Hi Jesper! Do you have by chance reviewed this for SP 2013?

    ReplyDelete
  19. Hi Jesper, Thanks, you put me on the right path. I was not able to use the letter 'e' or a period as some of my columns were codes. To get around this I made a calculated column called ALL with the value of * (="*") and added the condition OR ALL is equal to {SearchFilter} and changing the html in the form field to look for *

    ReplyDelete
  20. Hi Jesper, I've tried your method on SP2013 and it works! But I seem to meet a problem when filter the key words, if the result can be showed in one page, then it works very well(I've limited 30 items show in one page ), but if I filter with a simple word like "a", I would get few pages result. The problem is when I click the second or later filtered result page, it is blank! There is no result shows as the first result page, is that because I click the next page button so the filter key word is set to null again? Could you please me with that problem? Many many thanks in advance.

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Hi Jesper, Thank you, been searching for this for a long time. Works great. Surprising that MS could leave out something so basic.
    Jonathan

    ReplyDelete
  23. I was having the same "No Consumer" issue and couldn't get around it. I could use get filter but not get parameter. I am using Sharepoint Online and Designer 2013. If i open the entire page in Designer and highlight the entire list web part, the options menu shows up in the ribbon and allows me to add parameters and add connections. I also could not add connections in the browser unless i edited the page and hit apply on the web part before the Connections menu showed up. When i did it all in the Designer it worked fine.

    ReplyDelete
  24. This comment has been removed by the author.

    ReplyDelete
  25. Hi Jesper,

    Thanks for the post and it is amazing, I also want to use And condition like I want search functionality to those items which are Open in Status.

    Ex:

    Status = Open
    and
    Body Contains {SearchFilter}
    or
    Description Contains {SearchFilter}
    or
    Some more fields.

    Can we implement it?

    Thanks in advance
    Ankur

    ReplyDelete
    Replies
    1. Sorry for the late reply, yes you can, but strangely enough you need the AND filter to be the last of the filters.

      Delete
  26. Thanks! Very helpful!

    ReplyDelete
  27. Is there a way to add a button to clear the search results? I know you can just delete what you searched for and then hit search again but it is an extra step for users.

    ReplyDelete
  28. First, thanks Jesper for this great article, I have used and reused this across SharePoint 2010 and 2013 in past with great user experience. Recently I have implemented this within SharePoint Online Document Library and facing a weird issue. For some reason I seem to need TWO submits (either Enter key on keyword box or 2 clicks on Go button) for the results to filter properly. Status doesn't change with just 1 click or pressing Enter once, NO IDEA Why this is happening. I tried to investigate the HTML we use in the HTML Form Webpart Source Editor to see if I could tweak that somehow, there is a _SFSUBMIT_ and I tried to put 2 of those but to no avail. Anyone - faced this - or any ideas what could be causing this ?

    ReplyDelete
  29. since I found a solution to above myself, I am posting that back here so others who face similar issue can solve it similarly: basically if I set Server Render property to true for the listview webpart, it gives quite a 2010 type view with all newer links like Sync, SHare, Upload for Document Library are missing - so bit of a functionality loss but our HTML Form and Text Filter Webpart tricks work well with that View with a single click/single submit , otherwise in a SharePoint Online Document Library I am unable to implement this solution without it needed 2 submits on the HTML Form webpart. Let me know if others can find a better solution.

    ReplyDelete
    Replies
    1. Thank you so much for this! I had the same issue.

      Delete
  30. Thank you for these great guru tricks. It works great on sharepoint 2010 and 2013. Of course, with this search or filtering you cant export list in excel. Its export in excel with no data... Could someone try this?

    ReplyDelete
    Replies
    1. Thank you. Regarding export you could just make a view with no filtering on and make the export from there. And inside Excel you can then do all the filtering you like.

      Delete
    2. Yes I know, I did yesterday, exactly that you say. New view for printing in excel. Its little stupid, but its only solution. Thank you.

      Delete
  31. Hello Jesper!
    Great Article . Just one thing, I have 60 items in my Search Filter view and I set the item limit to 20.Now when I search for a word in 1-20 slab it works fine. But when i navigate to 20-40 items slab and search for a word which is present 1-20 or 40-60 slab but not in 20-40 slab,it gives a message "There are no items to show in this view of ....".I am trying to find a solution. In modify view if we set the "Item Limit" to 20 and choosing "Limit the total number of items returned to the specified amount" it works as I want but at I loose navigation functionality. Any suggestion will be very helpful.Thanks in advance.

    ReplyDelete
  32. Bare in mind that it is a filtering function and not a search. So if you limit the list to items 20 to 40 you cannot filter for content that is not present in that scope. In short you can only filter for content that are visible on the page.

    ReplyDelete
    Replies
    1. Correct! I was also thinking the same. I will probably increase the limit. Thanks once again!

      Delete
  33. This works Pretty well, but I have to click the go button twice to actually get it to filter my expected result

    ReplyDelete
    Replies
    1. Yes, I heard that before, no clue why, does it also happen if you just press Enter?

      Delete
    2. I am also running into this problem, and it does still occur if I press enter. Did you have a solution for this? Thanks.

      Delete
    3. I have the same problem. Have somebody any idea?

      Delete
    4. Yes, I heard that before, no clue why, does it also happen if you just press Enter?

      Yes...

      Delete
  34. Hello! Thanks, this is super helpful. I've got filtering working in my library, but I was wondering if the same technique could be used for a search-like filter in Document Sets within that library? I tried following the same steps for my Document Sets view, but the web parts in Document Sets don't seem to allow connections.

    ReplyDelete
    Replies
    1. Never tried it on doc sets, can you set up a view that only displays the documents within a doc set? And would that accept connections?

      Delete
  35. Hi Jesper, Great work. This is working for me, however, I can't perform additional filters on an any column in the filtered list. Is this the same for you?

    ReplyDelete
    Replies
    1. Hi Pearler. You mean like cliking a column to filter on existing content? Yes, I have heard that. Maybe you can use other filtering web parts + a connection the same list to get the job done?

      Delete
  36. I will keep looking but do not have access to SharePoint Designer. Any options without that?

    ReplyDelete
    Replies
    1. Not quite sure what you mean, SharePoint Designer (SD) is a download from Microsoft. If your admin does not allow you that then you should ask them to help you with the steps that require SD as mentioned in step 3.

      Delete
  37. Hi. I was able to get it working on one list but when i tried to do it again on a different list, it keeps showing all the records. I have to manually clear the second text filter then it will filter according to my first filter. Do you by any chance know why that might be the case. Thanks.

    ReplyDelete
    Replies
    1. My best guess would be that your views filter is using OR between the filtering options when it should be AND

      Delete
    2. I found the solution. I just made my first filter the hidden one instead of the second filter.

      Delete
  38. Thanks for this article.Everything works fine but after i filter the item i need when i click to edit it the Edit option in the ribbon is disabled.can you help?

    ReplyDelete
  39. If you mean Edit properties, have you then selected one and only one item? And could you use the dropdown from title column or add an edit column?

    ReplyDelete
  40. sorry for the late reply.yes i did only selected one item but unable to see the quick edit drop down next to that particular item.also i cant even edit the item from the ribbon as well.any suggestions to make it work?

    ReplyDelete
  41. Hey
    Its working now.I just went into the modify view and selected the filtered column with (link to edit item menu) in the list of columns.Then it worked.Before i have selected the filter column without the link to edit item menu that's y i was unable to edit item. Any ways thanks for this post it really helped me.

    Thanks

    ReplyDelete
  42. Hi, I don't have a sharepoint designer - what could you propose?

    ReplyDelete
    Replies
    1. Install it! It's a free download on Microsoft, just search for it.

      Delete
  43. Thanks Jesper,

    works great in a list view, but if you try to put it on a web part page,it fails and won't show the parameter, instead shows 'No Consumer Schema'; I used this same method in 2010 and it works fine on pages etc. any idea why it won't work on a page in 2013?

    ReplyDelete
    Replies
    1. Thanks NancyB. Personally I haven't tried it on 2013, but I see people on comments to this blog post and on my one&only youtube video that seems to have made it work on 2013. One talks about making both the parameter and the connection from inside SharePoint designer. Give that a try.

      Delete
    2. Had the same issue. You need to add the parameter to the webpart on the page you're trying to add the form to, not the webpart on the default page for the view.
      Hope that makes sense

      Delete