Wednesday, January 26, 2011

SharePoint List filter wildcard search on multple columns .. LIKE

This implementation on how to use a SharePoint List filter with a wildcard  on multiple columns is a solution for MOSS 2007 and it requires SharePoint Designer.

The idea here is that given a search string a SharePoint view will then display all records that contains the string value in any of the several columns specified.

So for example if I specify 'AAA' as the value for the search string the result set will be Items #1 and  #2 because we can find the 'AAA' value in Item  #1 / Column 1  and Item #3 / Column 2

01_multiple_column_sharepoint_list_search

First thing I do is to add a Calculated Column to the list that will contain the concatenation of Colums 1,2, and 3.

multiple_column_search_calculated_column

So now my list looks like:

03_multiple_column_search_calculated_column_view

I then create a web page where the filter and the result set will be displayed.

04_multiple_column_search_webpart_page

I then add a Text String search web part to the newly created page. I have used Choice Fileter and SharePoint List Filter with similar results, but in this example I will use Text String because is the easier to use.

05_multiple_column_search_text_filter

I edit the search web part and give it a name. We'll need this name latter on in the solution. In this example I will use the 'Multiple Column Search' name for the filter as illustrated below.

06_multiple_column_search_text_filter_name

The web part will have a warning note indicating that it is not yet connected to another web part.  We will leave it like this for now and come back to connect it after we've edit the web page using SharePoint Designer.

07_multiple_column_search_text_filter_not_connected.gif

Edit this web page using SharePoint designer and notice the SharePoint List in the Task Pane on the right hand side under the Data Source Library tab.  If this tab is not displayed, make sure to check the 'Data Source Library' menu option under Task Panes.

08_multiple_column_search_sharepoint_designer.gif

Drag and drop the SharePoint list, in this example 'My Custom List' to the web page.

09_multiple_column_search_data_view_tasks.gif

You can edit this view to remove the default columns it has added and add the columns that you wish to display by clicking on the 'Edit .Columns...' menu choice in the Common Data View Tasks dialog window.

In this case I will remove the 'Modified By' and 'Modified' columns that were automatically added and I will instead display the 'Column #1', 'Column #2' and 'Column 3' in addition to the 'Title' column already selected for display.

10_multiple_column_search_data_view_columns

I will then click on the 'Filter' menu option in the Common Data View Tass dialog window to specify a filter criteria.

11_multiple_column_search_view

Add a new clause In the Filter Criteria dialog window.

12_multiple_column_search_filter_criteria

Select the calculated field that was created in the Field Name prompt and for comparison choose 'contains'. In the Value promp select Create a New Parameter from the pull down menu.

13_multiple_column_search_filter_criteria_values

The Data View Parameters dialog windows will show up creating a new parameter called 'Param1' as the default. Select 'Form' for the arameter Source and for the 'Form Field' enter the name of the filter web part that we had placed in the web page, in this case 'Multiple Column Search'

14_multiple_column_search_filter_criteria_parameter

Save the web page in SharePoint Designer and then refersh the browser where the web page was being edited.  The filter web part is now ready to be connected to the SharePoint Data View web part.

From the Connections Menu select 'Get Parameters From' and then choose the 'Multiple Columns Search' filter.

15_multiple_column_search_connect_webparts

In the Configure Connection dialog window select 'Param1' from the pull down menu and click the Finish Button.  Then Finish editing the web page in the browser.

16_multiple_column_search_connect_dialog

The Multiple Column Search filter is now ready to use.  You can type 'AAA' in the search value and confirm that only Record #1 and #3 will be dislayed.

17_multiple_column_search





For additional
SharePoint or Project Server
related articles please visit my website at:





9 comments:

  1. Thank you for this tip. I was able to use this idea in our SharePoint 2010/ SPD2010 environment and it worked! My question for you is what is your professional opinion of WHEN/WHERE Sharepoint Designer (2010) can be safely used? Thanks go you, now that I have my wildcard search on multiple columns working in our TEST environment, I want to do the same thing in PROD. However, I am leary of launching Sharepoint Designer 2010 in our PROD environment and repeating the same steps. I'm very confused about when it is safe to edit/not edit pages in Sharepoint Designer. I've read several posts about there being problems doing this and that it is safest to launch SPD only in a QA/Development environment. To get around this, I did try to export the list and webpart to which I added the parameter and then, import it into PROD but the parameter did not come through. Any suggestions? Thanks again for the post! It was a lifesaver!

    ReplyDelete
    Replies
    1. That is a very good question. I avoid using SPD in Production at all cost. I usually do all my work in DEV then migrate to PROD. I even have my PROD environment locked for SPD.

      Delete
  2. As way of an update, I answered my own question of Nov 11! At the suggestion of Laura (of WonderLaura!), I created a separate site collection and opened up that site collection in SPD. There I followed similar instructions as above but created a list view web part with a parameter. What I discovered is that I had to export the web part INSIDE of SPD (not export within the SP user interface.) Doing so gave me a prompt asking if I wanted to use a RELATIVE path for the associated list. I said Yes. I was then able to successfully import that web part on to my production page. I also found that I had go to List Settings within the SharePoint UI and re-set up the "CONTAINS" filtering. (That piece did not come through with the webpart export/import.) FYI! And, again thanks for the idea ... I think I will find a lot of use for this tip!

    ReplyDelete
  3. Hi! Gustavo

    I am not able to add the new paramater, its does not apppear. Any idea?

    Select the calculated field that was created in the Field Name prompt and for comparison choose 'contains'. In the Value promp select Create a New Parameter from the pull down menu

    Thank you.

    Dave

    ReplyDelete
  4. Hi, any clues on how to set the 3rd column such that it contains a set of numbers that lie between the number specified in Column 1 and 2?
    Eg: Column1: 12 Column2: 20 Column3: 12,13,14,....20

    ReplyDelete
  5. HI, Very Nice Article. Easy to implement in SharePoint 2010 also

    ReplyDelete
  6. In SharePoint 2010, for adding a Sharepoint Data form Web part, we need to add a Empty Data View web part and then click on datasource and select the list we want to use in the data form web part. This might be useful when somebody uses sharepoint 2010

    ReplyDelete