Part 2 ( Search, the "SQL Way" )
Part 1 - Search in FileMaker
In Part 1 ( Search, the "FileMaker Way") we discussed a scripting methodology we developed to dynamically assemble FileMaker search requests and then execute them either locally or on a remote FileMaker Server. In this blog we discuss how we approached the abiltiy to do the same thing using SQL.
Back in April 2012 FileMaker Inc. introduced FileMaker 12, bringing to the platform (among other features) native ExecuteSQL functionality. This feature made it possible to query any table in a FileMaker solution independent of "context" by simply using the "SELECT" SQL statement and leveraging inner/outer joins, etc.
That same year, at the DevCon2012 in Miami Beach, SeedCode's free tool "SQL Explorer" won "Solution of the Year" award. Many more tips were soon published for exploiting the possiblities of SQL SELECT, which helped many developers significantly reduce the complexity and improve the performance of their solutions. Then in 2014, came a big caveat: in FileMaker, Execute SQL runs safest "server-side".
The Problem: Where's "Perform SQL on Server"?
At DevCon2014 in San Antonio, we learned from Wim Decorte's "Deep-Dive Scripting" session that the ExecuteSQL function in FileMaker will cause a major performance penalty under certain circumstances if it is executed locally. It turns out that when Execute SQL is run locally on a hosted file, if a record is open for access from the same table that you are querying on the same device that you are querying from, then Execute SQL performance suffers greatly, especially on large data sets.
The trouble is there is no such alternative function as "Perform Execute SQL on Server". The obvious workaround is to write a script containing your ExecuteSQL query and then nest it inside another script to "Perform Script On Server". It was "Attack of the (search) Clones" all over again.
The Solution: "Keep using the (parameter) force"
The solution came to us as clearly as when tackling FileMaker style searching: what if we typed the entire ExecuteSQL statement as a parameter and then just "Evaluate" it on the server? Then, we could just use one search script to dynamically specify any Execute SQL search we want. Let's take a look.
Example:
Say your looking for all the full names of all members of a company called "Fermentum" in your contacts database. Simply type your request in "Universal Search" format:
"LayoutName" = name of the layout to search from (optional, provides "context").
"PSoS" = Perform Script On Server ( True or False ) True is highly recommended.
"bounceBack" = return to the original layout or remain on the search layout ( True or False , only applicable if "LayoutName" was important, as in when additional operations and script steps will follow.
"Scriptresult" = Calculation to be evaluated by "Universal Script". This is where the Execute SQL magic needs to happen. Type your SELECT function in here in quotes and, if you formatted it correctly, it will evalute and "Universal Script" will return the results.
Here's an example of the search we're talking about:
#Array ( "LayoutName" ; 1 ; "Contacts" ) &
#Array ( "PSoS" ; 1 ; True ) &
#Array ( "bounceBack" ; 1 ; False ) &
#Array ( "Scriptresult" ; 1 ;
"Let ( [¶
¶
myQuery =¶
"SELECT " & "Name_Full_c" & ¶
" FROM " & "Contacts" & ¶
" WHERE lower(" & "Company" & ") LIKE ?"¶
¶
] ;¶
//calculation¶
ExecuteSQL ( myQuery ; "" ; "" ; Lower("Fermentum") )¶
¶
) //end Let¶"
)
//LayoutName is optional
Now in English
1. Navigate to the "Contacts" table (unnessary, but it may be important for you if you need to gather data for use in your SELECT statement).
2. Run the search remotely on the server ( offload my search to the server )
3. Don't revert back to the original screen the search started from
4. Find me all the Full Names of all contacts where "Company" matches "Fermentum" (case insensitive).
That's it, it's that simple, the best part is you can use the same script over and over again and all you have to change is the "Scriptresult, and maybe the "LayoutName".
Use cases
Execute SQL using "Universal Search" is particularly useful for offloading search and update operations to your FileMaker Server (using "Perform Script on Server"). It returns text (primary keys, etc.) which you can utilize to perform additonal operations. It can also be useful for returning quick answers to complex searches back to your user almost instantly, with no search progress bar display.
DOWNLOAD
You can download "Universal Search" from modularfilemaker.org by clicking here.
The geeky fine print
There is no debugging tool for litteral text, such as when you insert a formula or function within quotes inside the "Evaluate" function. You have to debug your Execute SQL function separately first if you expect to get search results.
Komentáře