- ValuationID '123' (matches the term '123')
- Valuation '123' (most clients use longer valuation file names)
- Addresses matching '123 Main Street%', or '123 Main%'
- 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:
| Row | Term | Words | HintTerm | HintValue |
| 1 | 12345 | 1 | NULL | 12345 |
| 2 | Patrick | 1 | NULL | Patrick |
| 3 | 12345 Patrick | 2 | NULL | 12345 Patrick |
For the smart search string 'zip:12345 last:Patrick', the terms built will be:
| Row | Term | Words | HintTerm | HintValue |
| 1 | zip:12345 | 1 | zip | 12345 |
| 2 | last:Patrick | 1 | last | Patrick |
| 3 | zip:12345 last:Patrick | 2 | zip | 12345 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
INNER JOIN Contact WITH (NOLOCK)
ON Contact.ContactID IN (SELECT ID FROM @Result)
AND Contact.FirstName LIKE Term.HintValue + '%'
WHERE LEN(Term.HintValue) > 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:
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.
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.