XMod Pro comes with built in template filtering which for many cases is all you need to deliver data that the user can drill down on for themselves. However, this functionality is limited and I find that nearly always a project comes along where the client wants a custom search facility with the results sorted in a very particular way.

Imagine this scenario then: You are using the Document Exchange Professional module to store documents on a server and you have built a series of lists of documents for various committees in your clients organisation. Your client wants to be able to search the archive for documents but they want the user to be able to select a number of search criteria. There may be document type categories, year of publication, working group and some key words. The built in XMod Pro template filter functionality provided by the <SearchSort> method is too limited for this requirement. With this method you can only provide filtering based on a single value entered by the user in a text box. For example:

FilterExpression="title LIKE '%{0}%' or description like '%{0}%'"

The above filter expression looks for a subset of data in the template where the title field OR description field match the text entered by the user into the search box. You cannot ask the user for two values or use a series of drop downs or check boxes. Also, if you enter the text “big cat” you will find any records that contain that string “big cat” in the given field(s). You will not find any records that have cat and big in them separately.

So, what can you do? Well, you can use an XMod Pro form to create a search filter. One great feature of XMod Pro forms is that they can be used without being connected to a database. You do not have to provide a <Submitcommand> to send the data to a table. If this is left blank the form will submit anyway and perform any other task you assign to it such as sending as email or refreshing the page. If you use jQuery or plain JavaScript you can also do all sorts of other useful things such as call a web service with Ajax to get some data and manipulate the form based on the results. But you should remember that being able to save form data means that you can also save searches. It might be useful to log user search for analysis.

Here is an example of a search/filter form I built that refreshes a page and sends the search parameters to filter a template. The template receives the parameters and sends them to a stored procedure.

In earlier versions I used JavaScript to capture the parameters from the form controls and put them on the URL before refreshing the page but these days I use the redirect method of the submit button. Take a look at the code:-

<AddForm ClientName="SearchDocuments">

<SelectCommand CommandText="SELECT @dt AS DocTypeID, @st AS stText, @cid AS GroupID, @y AS DocDate">
<parameter name="dt" value='[[Url:dt]]' defaultvalue="6,7,8,9,10,14,15,16,17,22,23" />
<parameter name="st" value='[[Url:st]]' />
<parameter name="cid" value='[[Url:cid]]' defaultvalue="3,4,5,11,12,13,18,19,21" />
<parameter name="y" value='[[Url:y]]' />
</SelectCommand> 


<ControlDataSource id="dsYear" CommandText="
SELECT DISTINCT
substring(Value,1,4) AS DocDate
FROM DMX_EntryAttributes
WHERE DMX_EntryAttributes.AttributeId=2
ORDER BY DocDate DESC" />


<ControlDataSource id="dsCommittees" CommandText="
SELECT 
DMXC.id AS GroupID,
DMXC.parentid,
vieworder,
DMXST.objectID AS shortlistID,
DMXST.SubTypeKey,
DMXST.Text AS CategoryName

FROM DMX_Categories DMXC
INNER JOIN DMX_ShortTexts DMXST ON DMXC.id=DMXST.objectID
WHERE DMXST.SubTypeKey='NAME'
AND DMXC.parentid=1  /* under the groups top level category */
AND DMXST.TypeKey='CAT'
AND Text &lt;> 'Groups'
AND Text <> 'Document Types'
AND Text <> 'Public'
ORDER BY GroupID" />

<ControlDataSource id="dsDocType" CommandText="
SELECT 
DMXC.id AS DocTypeID,
DMXC.parentid,
vieworder,
DMXST.objectID AS shortlistID,
DMXST.SubTypeKey,
DMXST.Text AS CategoryName

FROM DMX_Categories DMXC
INNER JOIN DMX_ShortTexts DMXST ON DMXC.id=DMXST.objectID
WHERE DMXST.SubTypeKey='NAME'
AND DMXC.parentid=2  /* under the document types top level category */
AND DMXST.TypeKey='CAT'
AND text &lt;> 'Groups'
AND Text <> 'Document Types'
ORDER BY DocTypeID" />

<Panel defaultButton="srchButt">
<table class="searchDocs" cellpadding="3" cellspacing="3" width="100%">
<tbody>
<tr>
<td align="right" width="110px" class="member_only_document_search_header">Working Group:  </td>
<td align="left" colspan="2">  
<DropDownList id="ddlCommittee" DataSourceID="dsCommittees" DataField="GroupID" DataValueField="GroupID" DataTextField="CategoryName" AppendDataBoundItems="true" width="250px">  
<ListItem value="3,4,5,11,12,13,18,19,21">All documents</ListItem> 
</DropDownList>
</td>
</tr>

<tr>
<td align="right" width="110px" class="member_only_document_search_header">Document Type:  </td>
<td align="left"  colspan="2">  
<DropDownList id="ddlDocType" DataSourceID="dsDocType" DataField="DocTypeID" DataValueField="DocTypeID" DataTextField="CategoryName" AppendDataBoundItems="true" width="250px">  
<ListItem value="6,7,8,9,10,14,15,16,17,22,23">All document types</ListItem> 
</DropDownList>
</td>
</tr>

<tr>
<td align="right" width="110px" class="member_only_document_search_header">Year:  </td>
<td align="left" colspan="2">  
<DropDownList id="ddlYear" DataSourceID="dsYear" DataField="DocDate" DataValueField="DocDate" DataTextField="DocDate" datatype="string" AppendDataBoundItems="true" width="250px">
<ListItem value="%">All Years</ListItem>  
</DropDownList>
</td>
</tr>

<tr>
<td align="right" width="110px" class="member_only_document_search_header">Keywords:  </td>
<td align="left"  colspan="2"> 
<textbox id="txtString" datafield="stText" datatype="string" width="250px" /> 
</td>
</tr>
<tr>
<td width="110px"></td>
<td align="left">
<div class="searchDocs"><AddButton id="srchButt" cssClass="searchDocsButt" text="Search Library" Redirect='[[join("/Members/DocumentLibrary.aspx?dt={3}&cid={0}&y={1}&st={2}",[[GroupID]],[[DocDate]],[[stText]], [[DocTypeID]] )]]' RedirectMethod="post" />
<AddButton id="ShowsAllButt" cssClass="showAllDocs" text="Show All Documents" Redirect='/Members/DocumentLibrary.aspx?dt=6,7,8,9,10,14,15,16,17,22,23&st=&y=&cid=3,4,5,11,12,13,18,19,21' RedirectMethod="post" />
</td></div></td>
<td>
</td>
</tr>
</tbody></table>

</Panel>
 
</AddForm>

In this case I’ve used URL parameters because I find it easier to debug while I’m building it. But you could use the POST method instead and send the parameters hidden from the user. In the template you would use the [[Form:paramname]] request token to collect them. In the above example the stored procedure uses some complicated dynamic SQL to handle the parameters.

One idea that I have been pondering for a while is how to provide some kind of keyword search. That is, if the user enters ‘John Smith’ or ‘Smith John’ then they get the same results. Imagine some books with these titles: “Big Book of Cats” and “Big Cats of Africa”. Now if your search filter used the WHERE clause and passed the key words which might be “big cats” into a LIKE ‘%big cats%’ query you would only get the second book. In some cases this might be the desired result but it does not represent a key word search. What you have is a string pattern match. What your statement really needs to look for is WHERE title like ‘%big%’ or title like ‘%cats%’. You cannot do this with a fixed SQL statement in XMod Pro unless you know how many key words the user is going to enter. So you will have to use a stored procedure and then build some dynamic SQL that strips out the key words and builds a search query.

Now personally I hate dynamic SQL it’s a really horrible language to work with. I use it because I have to. But is there another way? Well, if you find it please let me know!

In another blog post for multi-select lists I created a separate linking table that stores key words for book titles. See this previous post. In this example I have a list of book titles. The user can choose from a drop down list of key words. These key words are then stripped out and inserted into a separate table. Using a separate table linking table for the key words we can then use some SQL like this.

CREATE PROCEDURE [dbo].[CD_getBooksFromKeywords] 

@keywordlist varchar(500)

AS

SET @keywordlist = @keywordlist + ''''
SET @keywordlist = '''' + @keywordlist
SET @keywordlist=replace(@keywordlist,',',''',''')

PRINT @keywordlist

DECLARE @sql nvarchar(max)
SET @sql ='select distinct(title) FROM Cd_booktitles BT
INNER JOIN Cd_titlekeywords TKW
ON BT.ID=TKW.BookID
INNER JOIN CD_keywords k
ON TKW.keywordID=k.id
WHERE k.keyword in (' + '' +  @keywordlist + '' + ')'

EXEC(@sql)


GO

This takes a single parameter which is a delimited list of key words. e.g. “cat,house,fish,dog”. It’s pretty crude at this stage. It surrounds each word with a single quote and then passes the list of words into a query. The query will find any book title that contains one or more of the key words. This query assumes you are giving the user a list of key words to choose from. i,e. a drop down list. Hence the delimiter. But you can elaborate on this and make it work for a text box. In that event you would have to handle any bad characters the user enters and strip out any key words you don’t want to deal with. You can do all this kind of stuff with dynamic SQL. You could even build a CLR function with VB.NET or C# to process your string of words before passing it to your query. If you wanted the query to use AND you could also do it with dynamic SQL.

For example, you might want to make sure every key word the user entered existed in the book title. In that event you would have to build the SQL dynamically based on each keyword. So that your final query might read something like this: WHERE keyword=’cat’ AND keyword=’dog’ and keyword=’fish’. All of this and more is possible. You just have to get stuck into dynamic SQL and get used to the horror of the single quote!

Good luck and thanks for reading.