Portal Filtering with ExecuteSQL
The new world of FileMaker development has been opened up due to the ExecuteSQL function. The days of many different extra table occurences should be behind us.
Yes, there will still be the occasional user interface related table occurrence which is still necessary. However, your advantages with using ExecuteSQL should dictate that it be leveraged to its full extent when possible - especially in an increasingly mobile computing world.
The real trick for some developers is advancing what they know and letting go of older knowledge. As an example, I'm periodically faced with FileMaker developers who remember their development days when working with FileMaker versions 3 through 6. I've seen FileMaker 10/11/12 solutions, new solutions in fact, which were developed with many more files than is necessary.
This same thing applies to ExecuteSQL. In order to take advantage of the newer technology (which, in this case, is quite old, but still valid, technology) you have to learn new things.
Hopefully, that's exactly what this video will do for you. If you've not yet taken the step to learning how SQL within FileMaker can benefit your own solution, then absorb as much as you can from this video!
I've also included some links on the article page to various SQL resources.
Comments
Multiple Search Terms
Nice use of SQL here, but how would you account for multiple search terms? Could you do it in a single field or would you need a separate field for each term?
SQL can search any number of fields
SQL, of course, is not limited to any one field. You can search across as many fields (and tables) all at once.
Now, if I'm reading your comment correctly, you're referring to searching the same field using multiple terms. Such as searching the accident summary field for both 'eastbound' and 'northbound'.
If this is the case, and you still want to use one single search field, then you need to create a 'search method' the user can follow. By a 'search method' I'm referring to syntax. So, for example, searching for multiple terms might come in the form of "eastbound, northbound". Where the comma is the obvious delimiter.
Using this syntax, you would simply parse out the number of terms and modify your SQL accordingly.
Because the sql string is within a Let() function you can substitute in anything you want, including additional AND requests.
Within a loop within your search script you would simple append as many ANDs to the initial WHERE and you're able to support as many terms on the same field as desired.
WHERE accident_summary LIKE '%searchValue1%'
AND accident_summary LIKE '%searchValue2%'
AND accident_summary LIKE '%searchValue3%'.... etc.
Hope that helps out! - Matt
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Yep. That's what I meant.
Yep. That's what I meant. That looks like a reasonable solution. Thanks.
WAN performance
Matt,
I have found that even simple queries can really fall apart performance wise when there are even just 10's of thousands of records accessed across the WAN.
Have you tested this on the WAN? I may do so myself with a larger record set. Also, do you have any methods for improving SQL performance on the WAN?
The whole trick to better performance
It's all a matter of what's loaded by FileMaker across the wire. What I've "heard" is that the ExecuteSQL function is actually just a wrapper for what FileMaker normally does behind the scenes with its own query methods - it just breaks away from the graph and context.
This means it's actually not a true implementation of SQL where the only data it pulls is the data you request.
So, for example, in the file provided, even though you're only making a SELECT against the id field, FileMaker is still pulling the whole record behind the scenes.
What this means is that it boils down to the complexity of your schema and the amount of data a record contains.
So... my solution to any mobile attempt would be to make mirror tables, if necessary, and use record checksums on the data. If a mobile solution - over the WAN - only needs 1/5 of the data which is stored within a table, then you have two options. 1) Pair down your table (schema) or 2) Make a mirror table for a distinct purpose.
These types of decisions always come down to the solution usage and desired objectives.
The biggest advantage of ExecuteSQL is the separation from context and being able to keep a solution more trim (easier maintenance). The performance issue is always going to come down to the amount of data being pulled. If you fill a car engine with more oil than it can handle, then it won't be as performant.
Same thing applies with FileMaker.
-- Matt Petrowsky - ISO FileMaker Magazine Editor