FileMaker Explore | The Support Group: A little bit of SQL in FileMaker 12

One of my favorite new features of FileMaker Pro 12 is the ExecuteSQL function, which allows you to perform a SELECT SQL query against your FileMaker tables. ExecuteSQL allows you to use SQL to create calculations that otherwise might be very difficult or cumbersome. For example, in the past, if you wanted to get the list of values that had been used anywhere in the field areaCode in your contact table, you had to first create a value list using that field, then create the calculation: ValueListItems ( Get ( FileName ) ; “AreaCodes” ). It worked, but it was a bit of a hack to have to create that extra value list.

Now, all you need is:

ExecuteSQL ( "SELECT DISTINCT areaCode FROM contact" ; "" ; "" ) /* The last two empty parameters tell FileMaker to use the default comma and carriage return as column and row separators */

Okay, so it does require learning a bit of SQL (check out the W3Schools’ SQL Tutorial). But many queries are straightforward, and using SQL this way can simplify the relationship graph.

Say I wanted to get all the area codes used in Massachusetts. Using the old value list method, I would need to create a field somewhere to store the abbreviation “MA”, create a relationship from that field to the contact::state field, and define my value list to use only related values starting from the table with the new field. With ExecuteSQL, all I need is to update the formula to:

ExecuteSQL ( "SELECT DISTINCT areaCode FROM contact WHERE UPPER(state)=?" ; "" ; "" ; "MA" ) /* The fourth parameter is used to fill in the ? in the query. It could be a field instead of a text constant */

Here’s one of my favorites: often it’s useful to know the name of the base table (the ones define on the Tables tab of the Manage Database dialog) that is the source for a table occurrence (the tables that appear in the graph). You may have lots of table occurrences, named leads, students, personsOfInterest, relatives, and so one, but each is really an instance of the contact table. Outside the Manage Database dialog, any reference to a table is really a reference to a table occurrence (so Get ( LayoutTableName ) returns leads, for example, not contact).

In the past, it would be necessary to name each consistently to include the underlying base table (so contact_leads, contact_students, contact_personsOfInterest, etc.), and then parse the table occurrence name. Be careful changing table names! But with SQL, you can use the following to get the name of the base table of the current layout:

ExecuteSQL ( "SELECT BaseTableName FROM FileMaker_Tables WHERE TableName=?" ; "" ; "" ; Get ( LayoutTableName ) )

 
FileMaker_Tables is a special table you can query via SQL to get a list of all table occurrences, and the BaseTableName column tells you the name of the source table.

You can replace Get ( LayoutTableName ) with any table occurrence name to get it’s base table. I like the following calculation field that stores the name of the table as data, which can be extremely useful when exporting and importing data:

Let ( [ FQFN = GetFieldName ( Self ) ; tableOccurrence = GetValue ( Substitute ( FQFN ; "::" ; ¶ ) ; 1 ) ] ; ExecuteSQL ( "SELECT BaseTableName FROM FileMaker_Tables WHERE TableName=?" ; "" ; "" ; tableOccurrence ) )

The two variables in the Let function first get the fully qualified field name (including table occurrence name) of the calculation, an then parses out just the table occurrence name for use in the query. Since the calculation never makes reference to anything but Self, you can copy and paste this calculation field to any table without needing any editing.

What’s your favorite use of ExecuteSQL?