Learning Claris FileMaker

FileMaker introduced a very different kind of capability with the ExecuteSQL function.

First introduced in FileMaker 12, it allows you to perform “select” queries against your FileMaker data, as opposed to the more familiar “Find” functionality available in FileMaker.

SQL (Structured Query Language) is a standard used with other, more traditional, database servers. Using SQL, SELECTS on indexed data is blazing fast and efficient.

It allows you to construct relationships in SQL that do not necessarily exist in your application otherwise.

There have been many useful techniques that have been demonstrated since its introduction.

This post will detail and explain yet another technique of the ExecuteSQL function - Search as You Type.

When scripting in FileMaker I pay particular attention to the following:

Code clarity: make sure your code is humanly readable; clean and documented;
Reuse elements: declare a variable once, use it whenever you need it;
Keep your code compact: if you don't need it, don't put it in or leave it in;
Generate a sub-script if you have to use the same set of lines in multiple places;
Speed: make it as fast as possible; and
Error handling: make sure errors are caught and appropriately handled.
You don't have to adhere to all of the above requirements, but your process will work better if you do. Recently I had a situation where speed was not sufficient for the process I was working on so I had to rethink the approach.

Editor’s note:  (FileMakerhacks blog)

Today I’m pleased to present a guest article and accompanying demo file written by Joel Englander on using ExecuteSQL + a global multi-line key as an alternative to FileMaker’s built-in portal filtering mechanism, with dynamic portal sorting included as an added bonus.

Learn the basics – and some great tips – of ExecuteSQL :

- Using Let statements 

- Basic syntax 

- Which is faster:  ExecuteSQL or FileMaker search? 

- What makes ExecuteSQL massively slower? 

- Debugging the question mark you get with an error 

- Which custom functions to use to improve coding time with ExecuteSQL

 - Sorting, complex finds, and more