Smart Search: Google-like Searching

QBO3's Smart Search feature enable Google-like searching across records based on search terms. Each module can have it's own set of search terms defined. For example:
  • Loan: users may enter text including loan number (Loan.Loan), address (Property.Address), borrower name (Borrower.Borrower), or social security number last 4 (Borrower.USSSN)
  • Person: users may enter text include a username (Person.Person) or a last name (Person.LastName)
  • Contact: users may enter a name (Contact.Contact), address (Contact.Address), phone number (ContactMethod.ContactValue), or email address (ContactMethod.ContactValue)
  • etc.
Assume loan #987654321 includes a John Smith living at 123 Main Street.  When a user enters 'Smith 123 Main Street' in the Loan home pages' Smart Search box (found in the QBO main menu):
  • The following search terms will be searched against the Loan, Address, and Borrower tables:
    • Smith (match Borrower.Borrower)
    • 123 (match Loan.LoanID, Loan.Loan)
    • Main
    • Street
    • 123 Main (will match Property.Address)
    • Main Street
    • 123 Main Street (will match Property.Address)
    • ...
  • Loans with a borrower named Smith will be included in the results
  • Loans with an address starting with 123 Main will be included in the results
  • Loans with a LoanID of 123 will be included in the results
  • Loans matching Smith and 123 Main Street will show up at the top (because they match more terms than other loans)
You can configure customized search terms. For example, assume we wanted to allow searching by an investor loan number (Loan.InvestorLoanID). From Design > Configuration > Modules > Loan, click on the Search Terms tab and create a new term like this:


SELECT 
Loan.LoanID, -- This is the ID of a matching Loan row
Term.Row, -- This tells us which term was matched
9, -- This is a power-user defined 'weight' of the term
'Loan', -- This is the table that holds the matching term
LoanID, -- This is the ID of the row of the table that holds the matching term
'InvestorLoanID', -- This is the column that holds the matching term
Loan.InvestorLoanID -- This is the matching term
FROM @Term AS Term -- @Term is build automatically by QBO from the words the user entered
INNER JOIN Loan  -- Join to whatever you want to here
ON Loan.InvestorLoanID LIKE Term.HintValue + '%' -- Assume a % for this use case
WHERE 
Term.Words = 1 -- For InvestorLoanID, we don't need to try to match terms that have spaces
AND LEN(Term.Term) > 5 -- Let's not try to match '123' from an address - too many matches! 

Let's assume we wish to allow a user to search for a loan by a title committment number (Title.CommittmentNumber):

SELECT 
Loan.LoanID,
Term.Row,
7, -- Less important than an InvestorLoanID when calculating weight
'Title', -- This is the table that holds the matching term
TitleID, -- This is the ID of the row of the table that holds the matching term
'CommittmentNumber', -- This is the column that holds the matching term
Title.CommittmentNumber  -- This is the matching term
FROM @Term AS Term
INNER JOIN Title
ON Title.CommittmentNumber = Term.HintValue -- Exact match in this case
AND Title.Object = 'Loan'
INNER JOIN Loan
ON Loan.LoanID = Title.ObjectID
WHERE 
Term.Words = 1 -- For CommittmentNumber, we don't need to try to match terms that have spaces

Now our title users are very happy; they can search by loan number or committment number. However, our non-title users are now annoyed, because the committment numbers happen to look a lot like loan numbers. For example, you may have a Loan # 987654321 and a Title # 987654321.  Both will show up in the search results, though the InvestorLoanID will be weighted higher than the Title.  One can require a user to provide a hint in order to trigger a search term by changing the WHERE clause a bit:

WHERE 
Term.Words = 1 -- For CommittmentNumber, we don't need to try to match terms that have spaces
AND ISNULL(Term.HintTerm, 'Other') = 'Title'

For a user to search for loans by committment number, they must type in 'title:987654321'. 
Comments