Using Form Web Part and Data View Web Part to search and filter data

Present data in a grid view with an ability to filter records by a set of fields is a common requirement in an enterprise world. Built-in SharePoint list view filters and search cover basic scenarios, but filtering capabilities are often insufficient to effectively search over particular metadata. For instance,

• a user might need to search only by particular columns;
• search results have to be limited by the certain lists;
• portal is internet facing and configured in a lockdown mode;
• search has to be performed over external data source;

This is where Data View web part may help, it provides a lot of options for customization since it uses XSLT templates. Very good article on how to use Data View web part for filtering was posted by Phillip Wicklund.

So, author used two connected web parts to feed Data View web part with a values from the Form web part, though every consecutive search filter previously filtered data. It happens because a Data View web part keeps parameter values and variables between postbacks.

A Data View web part thinks that no parameter is provided when value is equal to an empty string. To walk around this issue we used a little script. What we have to do is to let Data View web part think, that value has been changed. Let's replace empty string with some token before submit a form and then substitute it back to an empty string before performing the actual search.

Assume we would like to filter data by "County" and "Zip Code" fields, then we define Data Form web part containing two text inputs accordingly:

<table>
  <tr>
    <td>Zip Code</td>
    <td><input type="text" id="qZipCode" name="qZipCode"></td>
  </tr>
  <tr>
    <td>County</td>
    <td><input type="text" id="qCounty" name="qCounty"></td>
  </tr>
  <tr>
<table>

Place Submit and Reset buttons below the input fields:

<input type="submit" name="search" value="Search" onclick="javascript:beforeSubmit();_SFSUBMIT_"/>&nbsp;&nbsp;
<input type="reset" value="Reset" name="reset" onclick="doReset()"/>

The trick goes with the onclick handler of the Submit button. Add the following script block to the Data Form web part. It replaces empty string with the space.

<script>
 var ctls = ['qZipCode', 'qCounty'];
 
 function trimControls() {
  for(var i=0;i<ctls.length;i++) {           
   var ctl = document.getElementById(ctls[i]);
   if (ctl.value == ' ') {
    ctl.value = '';
   }
  }
 }
 trimControls();
 function beforeSubmit() {
  for(var i=0;i<ctls.length;i++) {           
   var ctl = document.getElementById(ctls[i]);
     if (ctl.value == '') {
      ctl.value = ' ';
     }
    }
 }
 function doReset() {
  window.location.href = window.location.href;
 }         
</script>

Then we have to remove spaces in the Data View web part. We use normalize-space function.

<xsl:variable name="qZipCodeU" select="normalize-space($qZipCode)" />
<xsl:variable name="qCountyU" select="translate(normalize-space($qCounty), $smallcase, $uppercase)" />
<xsl:variable name="Rows" select="/dsQueryResponse/NewDataSet/Row[$search='off'][contains(@ZipCode,$qZipCodeU)][contains(translate(@County,$smallcase,$uppercase),$qCountyU)]" />

Notice $search='off' condition, it allows to perform search only if a user clicks the Search button.

Another nice option of Data View web part is an ability to use various ASP.NET DataSource controls (generally, any control which implements IDataSource), for example:

• SPDataSource for searching over SharePoint lists;
• SPSqlDataSource for searching over external database;
• BdcDataSource for searching within BDC applications;
• SoapDataSource to filter data from web services.