Probably one of the most talked about features in FileMaker Pro 12, at least from a developer perspective, is the new ExecuteSQL() calculation function. To users interacting with FileMaker records, this is largely a hidden feature. Yet this powerful new option impacts developers in several ways.
Previously, developers used plugins to expose the SQL underpinnings of FileMaker. Now, a portion of SQL has become native to FileMaker. This portion is limited to the “Select” statement. Those same plugins remain viable options for developers who seek to insert, update, or delete records.
The programming language SQL (Structured Query Language, see Wikipedia entry) centers on the query, or Select statement, to pull data from one or more tables. For developers new to SQL there are several books or internet resources available, yet the most concise and immediately useful tool is probably FileMaker’s own ODBC and JDBC guide.
The SELECT Statement
Starting with the most elemental of queries, and breaking down the Select query into multiple lines helps us visualize the different components of the query. We can then add more pieces as we master each section. We start with selecting a field from a table.
SELECT some_field_name FROM some_table_name
It’s important to note that this statement selects the field name, not the value in the field. So if we said, “select Department from Contacts” we would get a list of all the departments in every record from the Contact table. You can also include mathematical operations or modify strings in the query. To count values you can use count(*) or count(field_name) for all records in the first instance, or only records that contain a value in the field_name.
The WHERE Clause
The Select statement works well in its basic fashion, but what we’re really after is the Where clause, combined with the ability to interact with multiple tables in one query. Rather than building complex relationships in the graph to accomplish the same result, we’re now able to solve this through SQL.
When FileMaker Pro 11 introduced portal filtering we were able to simplify our graph and remove several Table Occurrences, and now with ExecuteSQL the graph can focus more on pure relationships, as this function is context independent. We can query table occurrences from anywhere in FileMaker, without having to navigate to specific layouts or rely on additional table occurrence groups.
The next step along the SQL path is adding the Where clause and additional tables.
SELECT some_field_name FROM some_table_name WHERE a_condition_applies
The condition serves to limit the Select statement, as without it you would select all the records in the table. So, a simple Select statement with a Where clause would look like “select Department from Contacts where City = ‘Dallas'”.
ExecuteSQL Syntax
The way the ExecuteSQL function behaves plays a key role in writing SQL statements. According to the calculation dialog, the syntax is:
ExecuteSQL( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments… } )
The sqlQuery is the query string – the select statement. Since this is text, we write this inside double quotes. The fieldSeparator and rowSeparator default to comma and return, so unless we need the result to appear in a different format we can simply use double quotes. The arguments is optional, but will be discussed further below. In this query we can count all the records in the state of Texas, using single quotes for the literal value of TX.
ExecuteSQL( "select count(*) from Contacts where State = 'TX'"; ""; "" )
Pages 55-57 of the ODBC guide list several reserved words. A few of these words might appear in your solutions as field names, such as “order” or “size”, or “First” and “Last” instead of FirstName or LastName (or NameFirst and NameLast). These words will need to be escaped in queries. In addition, field names with spaces also need to be escaped.
ExecuteSQL( "select \"Order\" from Orders where \"Product Name\" = 'FileMaker'" ;"";"")
Unlike MySQL, for example, the ExecuteSQL function will show just ? in the result if there is an error in your query, with no hint as to what caused the error.
Using the Arguments Parameter
The arguments option allows the use of multiple arguments in the sqlQuery string. These arguments use the question mark as a placeholder, and are then listed in order in the argument section.
ExecuteSQL( " select count(*) from Books where zkf_authorID = ? and datePublished < ? ; ""; ""; Author::zkp_AuthorID ; Get( CurrentDate ) – 365)
Rather than using conditions outside the current record (i.e. State = ‘TX’), here we input values from a specific record, to see which books were published within a year for the author of the current book record.
The FROM Clause
The ability to consider multiple tables falls under the From clause, but also in this action we must point back to the fields in the Select clause. These fields can be prefixed with the table name or alias. Adding an alias is useful as it reduces clutter in the query. With a table called Customers we can change this to from Customers C, and prefix the field as C.NameFirst in the Select statement. To query across multiple tables we “join” these with an equal sign (=), which will include only matching rows in the results.
SELECT C.NameFirst, C.NameLast, O.DateOrdered FROM Customers C, Orders O WHERE C.custID = O.custIDf
Wrapping Up
The new ExecuteSQL function opens up a multitude of possibilities. In scripting you can build queries across tables, populating variables for use in virtual list techniques. In charting you can gather data for dashboards in the same way, without having to jump to various different layouts and loop through records to summarize them. For tooltips you can provide information specific to the current record that looks across related tables. All this can be accomplished without adding a single new table occurrence.
