posted Mar 19, 2012, 2:29 PM by Eric Patrick   [ updated Jun 14, 2012, 8:33 AM ]
QBO 3 provides two basic search patterns:
  • Smart searches: the user keys data into a single text box, and our software "figures out" what they mean to search for, and
  • Advanced searches: the user is presented with an element for each searchable field

Smart Searches

A Smart Search comprises three phases:
  1. Breaking the string of text a user entered into a table of "terms",
  2. Checking each term against some column in some table, building a results list, and
  3. Returning all records matching the results list
For example, take:

Valuation.ashx/SmartSearch?SmartSearch=123 Main Street

The "terms" generated by "123 Main Street" include:
  • 123
  • Main
  • Street
  • 123 Main
  • Main Street
  • 123 Main Street
The Valuation.config file defines four search terms to check:
  • Address: searches Property.Address, with results "weighted" at 9
  • ValuationID: searches Valuation.ValuationID, with results "weighted" at 10
  • Valuation: searches Valuation.Valuation , with results "weighted" at 10
  • Loan: searches Loan.Loan, with results "weighted" at 8
For the "123 Main Street" search against a demo database, the results would include (assuming the associated rows exist):
  1. ValuationID '123' (matches the term '123') 
  2. Valuation '123' (most clients use longer valuation file names)
  3. Addresses matching '123 Main Street%', or '123 Main%'
  4. Loan '123' (most clients use longer loan file names)
Smart Search Hints

Smart Search results will occasionally return unexpected (and perhaps undesirable) results. The most common example is returning a row by matching an identity column. It is very useful for a power user to search by Loan.LoanID, Valuation.ValuationID or Contact.ContactID, but users searching for Loan.Loan = '12345' probably don't intend to look for Loan.LoanID = 12345.

This issue can be addressed by "hints". Let's assume that we're looking for someone with the last name of 'Patrick', living in the zip code '12345'. We do not intend to match people living at 12345 Patrick Street, nor do we want to see ContactID 12345.
  • no hints: '12345 Patrick'
  • hints: 'zip:12345 last:Patrick'
For the smart search string '12345 Patrick', the terms built will be:

 RowTermWords HintTerm HintValue 
 1123451NULL 12345
 2Patrick1NULL Patrick 
 312345 Patrick NULL12345 Patrick 

For the smart search string 'zip:12345 last:Patrick', the terms built will be:

 RowTermWords HintTerm HintValue 
 3zip:12345 last:Patrick zip12345 last:Patrick 

The HintTerm and HintValue columns returned by the dbo.SearchTerms function allows us to tweak the Smart Search clauses, like this:

<SearchTerm Name="PostalCode" Table="Contact" Column="PostalCode" Clause="
  SELECT Contact.ContactID, Term.Row, 6, 'Contact', ContactID, 'PostalCode', Contact.PostalCode 
  FROM @Term AS Term  
    INNER JOIN Contact WITH (NOLOCK) ON Contact.PostalCode = Term.HintValue 
  WHERE ISNULL(Term.HintTerm, 'zip') = 'zip' 
    AND (LEN(Term.HintValue) = 5 OR LEN(Term.HintValue) = 9)
" />

Note the WHERE clause includes ISNULL(Term.HintTerm, 'zip') = 'zip'; this ensures that a term the user hints as something other than a zip code will not be searched for as if it were a zip code.  E.g. 'id:12345' would not return any rows matching zip code 12345 (unless the ContactID 12345 happened to be in zip code 12345!)

For search terms like Contact.ContactID, we can modify the clause to require a hint:

<SearchTerm Name="ContactID" Table="Contact" Column="ContactID" Clause="
SELECT Contact.ContactID, Term.Row, 10, 'Contact', ContactID, 'ContactID', Contact.ContactID 
FROM @Term AS Term  
  INNER JOIN Contact WITH (NOLOCK) ON Contact.ContactID = Term.HintValue 
WHERE Term.HintTerm = 'id' 
  AND Term.Words = 1  
  AND ISNUMERIC(Term.HintValue) = 1 " 

This way, '12345 Woodlawn':
  • includes zip code 12345
  • includes the address starting with '12345 Woodlawn'
  • excludes ContactID 12345
Searching 'zip:12345 Woodlawn':
  • includes zip code 12345
  • excludes the address starting with '12345 Woodlawn' (because the user intends 12345 to mean only the zip code)
  • excludes ContactID 12345
But we can still do: 'id:12345':
  • include ContactID 12345
  • excludes zip code 12345
Terms that affect the Order By only

Some columns are not distinct enough to serve as a search term, such as Contact.FirstName. The number of 'Patrick's (as a first name) may be very large; far fewer have Patrick as a last name. As a result, we don't want to waste time table scanning the Contact table for FirstName = 'Patrick'. However, given a result set including say 120 contacts with the last name of 'Patrick', it is often useful to specify the FirstName of 'Sophia', and have her at the top of the list.

This can be achieved as follows:

<SearchTerm Name="FirstName" Table="Contact" Column="FirstName" Clause="
SELECT Contact.ContactID, Term.Row, 8, 'Contact', ContactID, 'FirstName', Contact.FirstName 
FROM @Term AS Term 
    ON Contact.ContactID IN (SELECT ID FROM @Result) 
      AND Contact.FirstName LIKE Term.HintValue + '%' 
WHERE LEN(Term.HintValue) &gt; 3 

In this case, the Contact join is immediately being limited to the Contacts that have already been added to the Smart Search result set (e.g. the 120 Contacts with a LastName of 'Patrick').

Advanced Searches

While "Smart Search" attempts to simplify the QBO search experience for 99% of searches, QBO 3 vastly simplifies advanced searching with our SqlPattern class. If you want to just find 123 Main Street by address, without also getting ValuationID 123 in the search results:

Valuation.ashx/Search?Property.Address=123 Main Street

or, more interestingly, find Valuations starting with '201203' in Foothill Ranch:

Valuation.ashx/Search?Property.City=Foothill Ranch&Valuation=201203%

User Interface

The QBO 3 main menu includes a control that combines both Smart Search and Advanced Search functionality. Each page rendering a main menu can specify what kind of searching is being done by calling an xsl:template. For example, Valuation.Home.xslt:

<xsl:call-template name="MainMenu">
<xsl:with-param name="SearchOptions">{url:'Templates/Mortgage/Valuation.Filter.xslt'}</xsl:with-param>

The SearchOptions parameter allows the developer to control the behavior of the "global" search box. In the example above, the global search box will present the user with a Valuation-specific advanced search criteria.

The "MainMenu" template exists in Theme.xslt, and includes this snippet:

<form class="navbar-search pull-right" onsubmit="return false;">
<div class="controls" data-behavior="Filter.Search" data-filter-search-options="{$SearchOptions}">
<div class="input-append">
<input class="span2" type="text" placeholder="{$SearchPrompt}"/>
<span class="add-on filterPopup">
<i class="icon-arrow-down"></i>

The text box (<input> tag above) allows the user to invoke a Smart Search by entering text and pressing Enter.

The icon (<i class="icon-arrow-down"></i>) allows the user to invoke an Advanced search by rendering an "filter" XSLT into a div tag just below the search box. If a user enters any string and presses Enter, a 'search' event will be raised:

api.fireEvent('search', ['SmartSearch', {SmartSearch: inputElement.value});

This, by itself, does nothing. However, if a Search panel exists on the page, and is specified to listen for a 'search' event, the panel will invoke a Smart Search.

<div id="search" class="span12" data-behavior="ObjectBind" data-objectbind-options="{{ 'class': 'qbo3.ValuationObject', 'listen': ['search'] }}">.</div>