Searching: Vendor Module Smart Search

posted Jul 6, 2012, 9:11 AM by Eric Patrick   [ updated Jul 6, 2012, 9:12 AM ]

Background

In QBO 2 vendor module , when Client (e.g. ort@quandis.com) logs in, they can search different vendors. You will see the left hand side, it has all kind of filters and logic in the searching; it is using the rBrokerSearch.sql stored procedure to get this job done.

For the QBO 3 vendor module, we want to approach the same searching (even GUI wise may even add more search filters later on). In the meantime, we still want to keep the smart search box on the top. How can I add my logic for those search filters and logic to the Smart Search? Also there are more fields need to be pulling out (e.g. data from Geography) If you look at the rBrokerSearch.sql, a lot of logic involved with Collections, Geography, etc.

Smart Search Implementation

Come up with sample real-world searches clients might do. Something like this:
  1. Show me all agents that cover Tustin, CA
  2. I need a BPO agent that covers 92610
  3. I remember working with a broker named Cassidy in CA last year; is he in the system?
  4. Look up the broker whose email address is epatrick@quandis.com
  5. Show me agents that do Appraisals or Inspections in Orange County
From a list such as this, create the likely search string that should get you the desired results (e.g. what do you expect a client to enter into the Smart Search box?).
  1. "Tustin, CA"
  2. "BPO 92610"
  3. "Cassidy CA"
  4. "epatrick@quandis.com"
  5. "Orange County, CA Appraisal Inspection"
From this, break down the tables being queried:
  1. "Tustin, CA": Geography
  2. "BPO 92610": Collection, Geography
  3. "Lai CA": Contact, Geography
  4. "epatrick@quandis.com": ContactMethod
  5. "Orange County, CA Appraisal Inspection": Geography, Collection
From this list, create a search term for each table being considered:

Contact

Searching the entire Contact table can be expensive, so the LastName term should be limited to words with more than 3 letters. 

<SearchTerm Name="LastName" Table="Contact" Column="LastName" Clause=" 
SELECT Broker.BrokerID, Term.Row, 8, 'Contact', ContactID, 'LastName', Contact.LastName 
FROM @Term AS Term  
INNER JOIN Contact WITH (NOLOCK) 
ON Contact.LastName = Term.HintValue 
INNER JOIN Broker WITH (NOLOCK)
ON Broker.ContactID = Contact.ContactID
WHERE 
ISNULL(Term.HintTerm, 'name') = 'name'
AND Term.Words = 1
AND (
Term.HintTerm = 'name'
OR LEN(Term.HintValue) &gt; 3 
)
"/>

ContactMethod

<SearchTerm Name="Email" Table="ContactMethod" Column="ContactValue" Clause=" 
SELECT Broker.BrokerID, Term.Row, 10, 'ContactMethod', ContactMethodID, 'ContactValue', ContactMethod.ContactValue 
FROM @Term AS Term  
INNER JOIN ContactMethod WITH (NOLOCK) 
ON ContactMethod.ContactValue LIKE Term.HintValue + '%' 
INNER JOIN Broker WITH (NOLOCK)
ON Broker.ContactID = ContactMethod.ContactID
WHERE ISNULL(Term.HintTerm, 'email') = 'email' 
AND CHARINDEX(Term.HintTerm, '@') > 0
AND Term.Words = 1
" />

Geography

Searching the Geography table is perhaps the most resource intensive search. As a result, we don't want to search for cities matching 'someone@company.com'. We can achieve this by pre-qualifying the search term to ensure it looks like a zip code.

<SearchTerm Name="PostalCode" Table="Geography" Column="PostalCode" Clause=" 
SELECT Geography.ObjectID, Term.Row, 3, 'Geography', GeographyID, 'PostalCode', Geography.PostalCode
FROM @Term AS Term  
CROSS JOIN dbo.GeographyList('Broker', Term.HintTerm) AS Geography
WHERE ISNULL(Term.HintTerm, 'zip') = 'zip' 
AND LEN(Term.HintTerm) = 5
AND Term.Words = 1
AND ISNUMERIC(Term.HintValue) = 1
" />

Additional terms may be added to search for City, County or State.

Collection

In the Vendor Module, Brokers can be part of a 'BPO', 'Appraisal', 'Inspection', or other product-based collections. At first glance, if a user searches for 'BPO', it may seem desirable to return all brokers that a member of the 'BPO' collection. However, in the production Vendor Module, this would return thousands of brokers, which is not a particularly useful result. If a user searches for 'BPO 92610', however, we can assume them want to view brokers that perform BPOs in the 92610 zip code. Brokers that cover 92610 would be added to the smart search result by the Geography search term described above.  Instead of 'BPO' causing additional brokers to be added, we want the term 'BPO' to simply weigh brokers who do BPOs more heavily than those who don't.

In short, we limit our results to the brokers that are already part of the result set.

<SearchTerm Name="Product" Table="CollectionMember" Column="CollectionMemberID" Clause=" 
SELECT Geography.ObjectID, Term.Row, 2, 'Geography', GeographyID, 'PostalCode', Geography.PostalCode
FROM @Term AS Term  
INNER JOIN Collection WITH (NOLOCK)
ON Collection.Collection = Term.HintTerm
AND Collection.SourceObject = 'Broker'
INNER JOIN CollectionMember WITH (NOLOCK)
ON CollectionMember.CollectionID = Collection.CollectionID
INNER JOIN @Result AS Result
ON Result.ID = CollectionMember.SourceObjectID
WHERE ISNULL(Term.HintTerm, 'product') = 'product' 
" />


Comments