BackgroundIn 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.![]() Smart Search ImplementationCome up with sample real-world searches clients might do. Something like this:
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?).
From this, break down the tables being queried:
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) > 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' " /> |
Quandis Business Objects 3 > Use Cases >