A spotlight-like Portal Filter with DoSQL Plug-in: Part III

In the previous posts about this topic, we created a spotlight-like portal filter with the DoSQL plug-in.
Last time we created a custom function to simplify the implementation.
This time we will enhance that custom function so you can implement a spotlight-like portal filter in your solution without the need of any SQL knowledge.

Download the myFMbutler DoSQL plug-in here.
If you have not read the previous post, you can do it here.

Scenario
In our first article, we were searching for all records that CONTAIN our condition. To obtain this we used the % wild card before and after the condition. The search criterion "€œan"€ will return "€œan"€, "€œanna"€ and "€œjan"€.
Wouldn't it be nice, if could extend this with other search options like "€œbegins with"€, "€œends with"€ and "œliteral"€ ?

Step 1: The LIKE condition
The LIKE condition is used to specify a search for a pattern. % is a wild card and can be used both before and after the pattern.

"Contains" example
SELECT id FROM contact WHERE firstname LIKE '%an%'
The search criterion "€œan"€ will return "€œan"€, "€œanna"€ and "€œjan"€.

"ÂœBegins with" example
SELECT id FROM contact WHERE firstname LIKE 'an%'
The search criterion "€œan"€ will only return "€œan"€ and "€œanna"€.

"ÂœEnds with" example
SELECT id FROM contact WHERE firstname LIKE '%an'
The search criterion "€œan" will only return "€œan" and "jan".

"Literal" example€
SELECT id FROM contact WHERE firstname LIKE 'an'
The search criterion "an" will only return "an".

Step 2: The custom function
Now we are going change our custom function, and add an extra parameter "condition".

mfb_DoSQLPortalFilter( theTablename; theIDname ; theFields; theFilter ; theCondition )

In our example it will look like:
mfb_DoSQLPortalFilter( "contact"; "id" ; "firstname¶lastname¶"; "an" ; "contains" )
This function will return the SQL statement:
SELECT id FROM contact WHERE lower(firstname) LIKE '%an%' OR lower(lastname) LIKE '%an%'

And the function:
mfb_DoSQLPortalFilter( "contact"; "id" ; "firstname¶lastname¶"; "an" ; "begins with" )
will return:
SELECT id FROM contact WHERE lower(firstname) LIKE 'an%' OR lower(lastname) LIKE 'an%'

Download the custom function as a myFMbutler Clip Manager Clip.
Download the example file.

Now the custom function is ready and can easily be implemented in your solution.

Leave a Reply

You must be logged in to post a comment.