Tag: Internal SQL

ExecuteSQL, Level: Intermediate, SQL, Version: FM 12 or later

FM 12 ExecuteSQL, part 1

Kevin Frank

FileMaker 12 offers many intriguing new features, including new windowing capabilities, significant charting and container field improvements, and a brand new design surface. But to my way of thinking, all of these pale in comparison to the exciting possibilities offered by the new ExecuteSQL function.

What’s so great about ExecuteSQL? In a nutshell, it allows us to natively (without a plug-in) perform queries against FileMaker tables using SQL (structured query language). This means that we can now write code to answer questions that previously required adding new table occurrences and special-purpose fields to serve as relational predicates.

Incidentally, if you’re curious about SQL in general, here are some good starting points:

And of course you’ll want to grab a copy of the FileMaker 12 ODBC/JDBC Guide, which offers tantalizing glimpses of what FileMaker is capable of SQL-wise under the hood. Much of the information in Chapter 7 is directly relevant to the ExecuteSQL function.

Continue reading “FM 12 ExecuteSQL, part 1” →

Level: Intermediate, SQL, Version: FM 11 or later

FileMaker 11 Internal SQL Changes, part 2

Kevin Frank

Welcome back for a second action-packed episode, as we continue exploring changes to the FileMaker 11 internal SQL parser. We ended part 1 by looking at the INSERT/SELECT construction which, as you may recall, provides the SQL equivalent of a FileMaker “add new records” import from one table to another.

Actually, when you use INSERT/SELECT, if you wish, the source and target tables can be the same table (or, rather, table occurrence), which once in a while can come in handy, and is something you cannot do via a regular FileMaker import. For that matter, you can also use INSERT/SELECT to map a single source field to multiple target fields, which is another thing a regular FileMaker import cannot do… but I digress.

Continue reading “FileMaker 11 Internal SQL Changes, part 2” →

Level: Advanced, SQL, Version: FM 11 or later

FileMaker 11 Internal SQL Changes, part 1

Kevin Frank

March 9, 2010 is a date I recall quite clearly: not only was it the release date for  FileMaker 11; it was also the day a bunch of my FileMaker SQL code broke, due to changes in the FM 11 internal SQL parser.

If you’re new to this subject, or perhaps a bit rusty, I have written about internal SQL (a.k.a. FQL) on various occasions over the last year, and there are a number of important points that I won’t discuss today, because they have already been mentioned in one or more of these articles:

What follows is by no means exhaustive, but merely what I’ve documented. There is no comprehensive source of information about FQL, although chapter 7 of the FileMaker 11 OBDC and JDBC Guide is a good place to start (just remember that not everything you read there will apply to FQL).

Continue reading “FileMaker 11 Internal SQL Changes, part 1” →