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


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


So now my list looks like:


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


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.


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.


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.


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.


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


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.


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


Add a new clause In the Filter Criteria dialog window.


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.


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'


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.


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.


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.


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