Signature Sterling

There are a couple of options when you find yourself in need of a search form that queries a SharePoint list and filters on multiple parameters. One option is to create custom search scopes based on the desired criteria - effective, but somewhat cumbersome and not very aesthetically appealing in my opinion. A second option is the content query web part, but it's not really functional as a dynamic search since users have to edit the page and modify the web part in order to alter the search parameters. The third option is the use of an HTML form web part, a list web part, and a web part connection but unfortunately web part connections only allow the transfer of one value so multiple parameters is not feasible. The method I've outlined below uses a Data View and its associated configuration options within SharePoint Designer to achieve the described behavior.

1. Start SharePoint Designer 2010 and open the relevant SharePoint site.
2. Create a page to house the search form and results, edit the page, and select 'Advanced Mode' from the top toolbar ribbon.
   NOTE: The search controls must be asp.net (not HTML) in order to be visible as control parameters. SharePoint Designer will only allow the addition of asp.net controls outside of the WebPartZone tag so the page must set to advance edit mode in order to allow editing outside the WebPartZone tag.
   
3. Place the cursor just outside the WebPartZone tag in the code view of the page and add an Asp.net textbox control. Note the id attribute value of the control (the default is an incremented version of 'TextBox1'). Repeat this step for each text/date parameter. NOTE: The Ajax Control Toolkit Calendar Extender can also be used for a friendlier date control, but I haven't tested the 2010 Toolkit so proceed at your own risk.
   
Place the cursor just outside the WebPartZone tag in the code view of the page and add an Asp.net dropdownlist control. Note the id attribute value of the control (the default is an incremented version of 'DropDownList1'). Repeat this step for each dropdownlist parameter.
4. Place the cursor in the WebPartZone you'd like the list result set to appear and add an empty Data View.
   
5. Select the appropriate data source from the Data Sources Picker.
6. Navigate to the Data Source Details pane and select 'Multiple Item View' from the 'Insert Selected Fields As' dropdown. You can also use this pane to select the list columns you would like displayed within the Data View.
   
7. Highlight the Data View and select 'Parameters' from the tool bar ribbon. Add a control parameter for each text box and dropdownlist search control.
   
8. Highlight the DataView and select 'Filter' from the tool bar ribbon. Add a filter criteria line item for each non-date text box and dropdownlist. NOTE: You can include appropriate OR filter line items to return all results when search controls are left empty/unselected.
   
9. Within the Filter Criteria dialog box, select the Add XSLT Filtering checkbox and click the 'Edit' button.
10. To filter on records who's field value 'Created' matches dates contained within parameter ranges 'BeginDate' and 'EndDate', paste the following formula within the XPath expression textbox.
    [(number(ddwrt:FormatDateTime(ddwrt:FormatDate(string(@Created),1033,1),1033,'yyyyMMdd'))>=number(ddwrt:FormatDateTime(ddwrt:FormatDate(string($StartDate),1033,1),1033,'yyyyMMdd'))) and (number(ddwrt:FormatDateTime(ddwrt:FormatDate(string(@Created),1033,1),1033,'yyyyMMdd')) <= number(ddwrt:FormatDateTime(ddwrt:FormatDate(string($EndDate),1033,1),1033,'yyyyMMdd')))]
    
11. Confirm the filter changes and save the page.

And...voila! If all went well, you should be set to filter your list on multiple parameter values. This procedure should also work within SharePoint Designer 2007 with a couple tweaks. First, you won't need to bother putting the page in advanced edit mode (SPD 2007 will let you add components outside the WebPartZones by default). Second, you'll use the data source library pane to interact with the Data View rather than the various wizards provided by SPD 2010. This article should provide a useful reference when implementing this solution within SPD 2007: http://msdn.microsoft.com/en-us/library/cc300163%28v=office.12%29.aspx

There's been much published about how to achieve a 1-many relationship using SharePoint lists such as this one (one parent to multiple children). This method works great for exhibiting master/detail-type relationships. However, I was tasked with generating a knowledge base list to which our internal IT professionals could publish content and reference for information related to various server records within our SharePoint server list for troubleshooting or informational purposes. Since any single server record could have multiple related knowledge base articles, and any knowledge base article could potentially apply to multiple server records, I needed to emulate an (automated) many-to-many relationship between the server and knowledge base list. Given the limited resources available describing how to achieve this behavior, I decided to post my solution.

My task called for a ‘Knowledge Base’ column within the server list which would contain a hyperlink to a page displaying all of the knowledge base articles pertaining to that particular server record. Here’s how I achieved such behavior:

1. Create a multiple-value look-up column within the knowledge base list to server record values.
2. Create a KnowledgeBase.aspx page (within SharePoint Designer) and drag/drop a knowledge base list onto the page. (See the article above for more detailed instruction).
3. Add a data view parameter to the list called ‘ServerQS’ with value ‘ServerID’.
• Select the list within the design view
• Click the grey box with the ‘<’ symbol that appears to the right of the list (to access Common Data View Tasks)
• Click ‘Parameters’
• Click ‘New Parameter’
• Enter ‘ServerQS’ for the parameter name
• Select ‘Query String’ as the parameter source and enter ‘ServerID’ as the query string variable.
• Click OK

4. Filter the Data View by using the ServerID query string value:
• Navigate to the Common Data View Tasks pane again.
• Select ‘Filter’
• Check the ‘Add XSLT Filtering’ box and click the associated ‘Edit’ button.
• Choose ‘All’ from the ‘Select a function category:’ dropdown.
• Select the ‘contains’ function, you should see the textbox above populate with your selection.
• Expand the dsQueryResponse > Rows > Row tree in the left-hand navigation and select the server look-up column created earlier (I called mine ‘Network Appliance Hostname’).
• Paste ‘$ServerQS’ as the second parameter in the contains() function within the textbox.
• Click OK.
• Save/publish your page.
5. Test your reference page:
• Create a new knowledge base article and select arbitrary server look-up values.
• Visit your new page URL providing one of the look-up values as a query string parameter: KnowledgeBase.aspx?ServerID=TestServer.
• Confirm your newly created knowledge base articles are displaying.
*Note: The ‘Common Data View Tasks’ pane also provides a plethora of formatting settings for the result set such as sorting, grouping. In addition, the ‘Change Layout’ option, ‘General ‘ tab contains a toolbar option allowing users to dynamically implement their own sorting, grouping, and filtering preferences within the page.
6. Add the knowledge base hyperlink (look-up) column to the server list:
• Create a calculated column within the server list.
• Provide the following formula, where ‘ServerListNameColumn’ equals the name of the column used to uniquely identify your server records (and the value provided as the ServerID query string parameter).