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: