Keyword search

A Keyword searcher can be a clean alternative for filters with too many distinct values or a replacement for too many filter cards on your dashboard. With 1 keyword searcher you can replace as many filter cards as you want.

Let me show you how easy it is to build them and which ones have the better performance on a live database connection.

The obvious way of building a keyword search would be by using a parameter (what else?).

All scenarios below return the same result and the performance results can be found at the end of this post.

Scenario 1: By concatenation

Step1: Create a parameter “Keyword search”

Step2: Create a calculated field that contains all fields on which you want to run your wildcard searcher.

IFNULL([SalesOrderNumber],"") + "/" + 
IFNULL([CustomerPONumber],"") + "/" + 
IFNULL([CurrencyName],"") + "/" +
IFNULL([LastName],"") + "/" +
IFNULL([FirstName],"") + "/" +
IFNULL([EnglishEducation],"") + "/" +
IFNULL([Color],"") + "/" +
IFNULL([EnglishProductName],"") + "/" +
IFNULL([EnglishProductSubcategoryName],"") + "/" +
IFNULL([EnglishProductSubcategoryName],"")

You should include any field in this formula on which you want your keyword searcher to work.

Step3: Use Concatenate Field in your filter (Condition by formula)

001. Concatenate Filter

Scenario2 : By contains

Step1: Create a parameter “Keyword search”

Step2: Create a calculated field “Contains Field” with following formula

[Keyword search] = ""
OR CONTAINS([SalesOrderNumber],[Keyword search])
OR CONTAINS([CustomerPONumber],[Keyword search])
OR CONTAINS([CurrencyName],[Keyword search])
OR CONTAINS([LastName],[Keyword search])
OR CONTAINS([FirstName],[Keyword search])
OR CONTAINS([EnglishEducation],[Keyword search])
OR CONTAINS([Color],[Keyword search])
OR CONTAINS([EnglishProductName],[Keyword search])
OR CONTAINS([EnglishProductSubcategoryName],[Keyword search])
OR CONTAINS([EnglishProductSubcategoryName],[Keyword search])

Again, you should include any field on which you want to search on. The result of the calculated “Contains Field” returns True / False

Step3: Add Contains Field to the filters and select the “True” value

Scenario 3: By Find

Step1: Create a parameter “Keyword search”

Step2: Create a calculated field “Found Keyword”

[Keyword search] = ""
OR FIND([SalesOrderNumber],[Keyword search]) > 0
OR FIND([CustomerPONumber],[Keyword search]) > 0
OR FIND([CurrencyName],[Keyword search]) > 0
OR FIND([LastName],[Keyword search]) > 0
OR FIND([FirstName],[Keyword search]) > 0
OR FIND([EnglishEducation],[Keyword search]) > 0
OR FIND([Color],[Keyword search]) > 0
OR FIND([EnglishProductName],[Keyword search]) > 0
OR FIND([EnglishProductSubcategoryName],[Keyword search]) > 0
OR FIND([EnglishProductSubcategoryName],[Keyword search]) > 0

Again, you should include any field on which you want to search on.

The  result of the calculated “Found Keyword” field returns True / False

Step3: Add the “Found Field” to the filter and select the “True” value

Performance results

002. Results

So in my case the Find scenario has a performance increase of nearly 75%. Worth the testing if you ask me.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s