A spotlight-like Portal Filter with the DoSQL plug-in – part II


Warning: copy(): Filename cannot be empty in C:\inetpub\blog.myfmbutler.com\wp-content\plugins\mytube\mytube.php on line 220

Portal Filter

A couple of weeks ago, we showed you how to create a spotlight-like portal filter using the DoSQL plugin. At the same time it was an example of how you can implement an OR relationship, without overloading your database with complex calculations.
This time we are going to expand the demo with a powerful search option.

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

Scenario
Our FileMaker solution contains a table with contacts. We want to create a spotlight-like portal filter where we can search by first name OR by last name OR by state OR by county. This time we want the user to be able to choose which fields to search, similar to the search options of e.g. Mail.app.

Mail

Step 1
Download the example file of part 1 of this post.
To enable a search only on selected fields, we create a value list "Fields" and populate them with the values: First Name, Last Name, State, County.

Portal Filter

In the table "portalfilter" we create a new global text field "fields" and add it onto the layout.

portalfilter2_1_002

Download step 1

Step 2
In the second step we are going to figure out which fields we have to search. Let's change the calculation of the field IDs:

Let
(
[
theNumberofFields = ValueCount(fields)
;
searchField1 = GetValue(fields ; 1)
;
searchField2 = GetValue(fields ; 2)
;
searchField3 = GetValue(fields ; 3)
;
searchField4 = GetValue(fields ; 4)
;
searchFieldName1 = Case(searchField1 = "First Name" ; "firstname" ; searchField1 = "Last Name" ; "lastname" ; searchField1 = "State" ; "state" ; searchField1 = "County" ; "county" ; "")
;
searchFieldName2 = Case(searchField2 = "First Name" ; "firstname" ; searchField2 = "Last Name" ; "lastname" ; searchField2 = "State" ; "state" ; searchField2 = "County" ; "county" ; "")
;
searchFieldName3 = Case(searchField3 = "First Name" ; "firstname" ; searchField3 = "Last Name" ; "lastname" ; searchField3 = "State" ; "state" ; searchField3 = "County" ; "county" ; "")
;
searchFieldName4 = Case(searchField4 = "First Name" ; "firstname" ; searchField4 = "Last Name" ; "lastname" ; searchField4 = "State" ; "state" ; searchField4 = "County" ; "county" ; "")

...

This could be implemented more elegantly with a recursive custom function, but for now let's leave it like this. The next thing we have to do, is to change our SQL statement:

...
theRowSeparator = mFMb_DoSQL_SetRowSeparator( 13 ) // Return
;
theSQL = "SELECT id FROM contact"
;
theSQL = theSQL & If(not IsEmpty(filter) and theNumberofFields>0 ; " WHERE " )
;
theSQL = theSQL & If(not IsEmpty(filter) and theNumberofFields >0 ; "lower(" & searchFieldName1 & ") LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) )
;
theSQL = theSQL & If(not IsEmpty(filter) and theNumberofFields >1; " OR lower(" & searchFieldName2 & ") LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) )
;
theSQL = theSQL & If(not IsEmpty(filter) and theNumberofFields >2; " OR lower(" & searchFieldName3 & ") LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) )
;
theSQL = theSQL & If(not IsEmpty(filter) and theNumberofFields >3; " OR lower(" & searchFieldName4 & ") LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) )
;
theIDs = If( theNumberofFields ≠ 0 ; mFMb_DoSQL( theSql ) )
]
;
theIDs
)

If all fields are selected, the SQL statement will look like this:
SELECT id FROM contact WHERE lower(firstname) LIKE '%an%' OR lower(lastname) LIKE '%an%' OR lower(state) LIKE '%an%' OR lower(county) LIKE '%an%'

But, if only the field "firstname" is selected, the SQL statement will look like this:
SELECT id FROM contact WHERE lower(firstname) LIKE '%an%'

Download step 2

Step 3: Custom Function approach
In the previous steps we have changed the example file to add a search option for the fields. If you look at the calculation it's becoming complex. In this step we will create a custom function that makes the implementation of this technique in other files much easier.

mfb_DoSQLPortalFilter( theTablename; theIDname ; theFields; theFilter )

In our example it will look like:
mfb_DoSQLPortalFilter( "contact"; "id" ; "firstname¶lastname¶; "an" )

This function will return the SQL statement:
SELECT id FROM contact WHERE lower(firstname) LIKE '%an%' OR lower(lastname) LIKE '%an%'

This custom function requires a list of field names, so we need to change the value list "fields". Create a new table with two text fields "fieldname" and "label".

Portal Filter

Now we have to change the value list "fields":

portalfilter2_3_002

The only thing we still need to do, is to change or formula of the field "IDs":
Let
(
[
theRowSeparator = mFMb_DoSQL_SetRowSeparator( 13 ) // Return
;
theSQL =mfb_DoSQLPortalFilter ( "contact" ; "id" ; fields ; filter )
;
theIDs = mFMb_DoSQL( theSql )
]
;
theIDs
)

Portal Filter

Download the example file.

Download the custom function as myFMbutler Clip Manager Clip.

One Response to “A spotlight-like Portal Filter with the DoSQL plug-in – part II”

  1. SHpartners / myFMbutler Blog » Blog Archive » A spotlight-like Portal Filter with DoSQL Plug-in: Part III Says:

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

Leave a Reply

You must be logged in to post a comment.