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.
Thanks Anders. Though I’ve used ExecuteSQL function, I was unclear about the arguments parameter. Your example was very insightful.
Thanks, Daniel. It’s a neat feature of the ExecuteSQL function.
Thanks for the great article. I’m trying to use the sum() and avg() functions but they don’t work.
Ex. “Select sum(price) from invoice_lines” returns ?.
It’s odd, because count(), max(), min(), they all work fine in the same statement. Perhaps sum and avg are not supported. But that doesn’t make a lot of sense to me. Maybe I’m missing something.
Thanks for your great article. It is clear about the parameter and the escape requirement for reserved words and field name with spaces.
I am hoping the SQL Statement can support not just Select clause very soon.
Jose, I was able to get sum( field ) to work. Did you check the field type and the TO name? Price is not a reserved word so it should aggregate the contents if that field across the table.
…had an unrelated problem, but for me the problem was the field names in FM, which I called ‘From’ and ‘To’, which SQL couldn’t handle.
Changing them to ‘ValidFrom’ and ‘ValidTo’ solved my problem.
Thanks!
Jason,
I agree. Update and insert would be great options. Perhaps in future versions. It’s possible there are concerns from FMI about trying to update container or calculation fields.
Thanks Anders. I am new to FileMaker.
I am wandering if I can find anywhere FileMaker solution (project) containing example of code.
I appreciate your help.
Thanks,
Yury,
I would start with FileMaker, Inc. web site. If you join their Tech Net there are resources. Also, each FileMaker Pro application comes with templates that contain some sample code. It depends on what you are looking for, as each FileMaker solution generally has different fields and code, written for specific purposes.
Anders
2 Questioins:
1. Can you use ExecuteSQL on FM12 tables? When I took a look at it, it seemed to require a connection to an external SQL d/b.
2. If you use ExectueSQL on an external d/b, does it execute on the server or does it download the entire table and do the selecting locally as it did in previous versions?
Thanks.
Anders,
Thanks for confirming that sum() works, and for the tips. I must have something wrong then. Let’s see.
Jose,
I know that Avg, Count. Max, Min, Sum all work – I am not sure of any other functions that work with FileMaker’s SQL version.
You should be able to test this in the Data Viewer, or in a script that sets a field or global variable with the result. Look at the field type and contents as well.
Anders
Martin,
1. With the ExecuteSQL() function you use this on tables within FileMaker. There is an Execute SQL script step (note the space) that is used on external tables.
2. I am not sure I follow – the Execute SQL script should only query the external database through the ODBC connection. You can ‘download’ the table via ESS, which links the data, or you can import via an ODBC data source using a Select statement.
Anders
This is the most succinct example I have seen on how to use ExecuteSQL but I am a bit worried…
I currently use MS Access / SQL a lot but looking for an alternative so I can build ‘Apps’ for the iPad. I am currently evaluating FMP12 Pro / Go as a quick ‘in’ and so far really impressed but concerned that ExecuteSQL can’t INSERT, UPDATE etc. How is this possible without a plugin (…that won’t work in GO)?
Thanks, Keith. This is not possible without a plugin. It might be possibly to hook up a PHP page that can execute a FileMaker script that uses another SQL-able plugin to INSERT. This adds moving parts to the process, parts that can fail due to the hibernating elements of Fm Go solutions. For FM Go solutions you probably want to stick with native options, but for now that excludes SQL aside from SELECT.
Aggregations are not generally permitted in select and where clauses, although there are some exceptions, you usually have to use the GROUP BY / HAVING variant.
SELECT Name
FROM TestScore
GROUP BY Name
HAVING Score = Min (score);
Theo, I would think that aggregations are a normal part of the select clause. Either you select sum(value) or max(value) or count(value) etc. by itself, or if you include additional fields/columns you include the group by, such as “select City, count(Name) from Contacts group by City”. This would show the number of people per city in the contacts table, rather than each person.
Aggregate functions would a be a topic by itself, I think, and possible one I’ll tackle in a future post, as you could also include some conditional clauses and joins to make it more interesting.
I still find myself wanting to base both layouts and portals on SQL Queries, not just TO’s! This would MUCH simplify the bizzare Anchor/Buoy and long strings of one-to-many TO fracturing. When is FileMaker “Pro” going to allow this?? All other databases I’ve ever worked on utilize this… even little ole’ Access.
Bob, since FileMaker Pro 12 is the first version with SQL exposed natively this might be a future possibility, but only if FileMaker,Inc. knows there is a demand for such a feature. My suggestion is to let them know via their feedback page.
Thanks Anders, very helpful!
Bob Hedges says:
September 10, 2012 at 10:48 PM
I still find myself wanting to base both layouts and portals on SQL Queries, not just TO’s! This would MUCH simplify the bizzare Anchor/Buoy and long strings of one-to-many TO fracturing. When is FileMaker “Pro” going to allow this?? All other databases I’ve ever worked on utilize this… even little ole’ Access.
If I understand Bob correctly, this can be accomplished by first creating a view in MySQL, then creating an instance of the view in FMP. (The Actual ODBC manager provides an option to include views as well as tables).
This way MySQL does the heavy lifting with complex joins, etc while basically creating a flat table for FMP to handle. In short, views collapse anchor/bouy relationships into a single instance making everything far, far simpler and far, far faster.
Thanks Chris. That would certainly work if querying an SQL source outside FileMaker, and I think is recommended for narrowing down the data source before looking at it from FileMaker. However, in this instance the focus is more on SQL queries within FileMaker. With FileMaker Pro 12 we now can write an SQL query to look at FileMaker tables (or rather, table occurences). We might imagine a portal based on a query, returning the columns (and maybe headers) from the query, rather than then fields via a relationship, if I understand Bob’s scenario correctly. That would remove even further the need for additional TOs in the graph, although I am not sure we can do away with those completely, since it would remove the Go To Related Record process that’s such an integral part of FileMaker.
… forgot to mention, for blazing speeds, index the search fields in MySQL.
Indeed! Indexing is a topic we’ll cover in future posts, as there’s some interesting things going on with FileMaker, SQL queries, and indexing.
I’m a total newbie to Filemaker. Does joins (LEFT, RIGHT, INNER / OUTER) work in die Filemaker SELECT statements? If not, how would you get information from multiple related tables (I assume foreign and primary keys exists in Filemaker)?
Hi Chavoux. Yes, Joins work in FileMaker. Primary and foreign keys are created by the developer in each table. These usually are defined as serial numbers or UUIDs. Traditional joins work, such as:
select […] from tableA as a join tableB as b on a.primary = b.foreign
You also can use inner joins, left or right outer joins, or left joins – listing all the examples might be something to review in a future post, but “JOIN tablea ON tableb.field = tableb.field” also work, for instance (with correct table and field names instead of these placeholders).
It’s possible not every type of join works in FMP’s SQL language.
The main caveat with FileMaker 12’s SQL is this applies to SELECT only – there is no INSERT, UPDATE, DELETE at the moment. Also, SQL is not a 100% substitute for FileMaker’s native methods, as there are occasional performance considerations, and other design reasons why native FileMaker might work better in certain circumstances. But it’s a great tool to have along with the other functions and scripting methods.
ok, the native execute SQL finally is a big tool mandatory for every SQL database, I mean but, from my point of view, FM have a lot of things to eveolve.
1) is impossible use nested query SELECT FROM (SELECT
2) is impossible to view the result inside a layout, in other words, is impossible use an SQL SELECT as datasource for a format, like other db application, access or openoffice, does.
3) over the top, in filemaker for every field in every layout is mandatory to link itself with a field in a table, where in other db application there is a code-behind (an intermediate layer) for control and elaborate the information, like ther point before.
Dear Anders,
Thank you for explaining the SQL insert. If you want to generate a table using a query, where do you input it? I am trying to count unique entries and group them by another field, but cannot combine the report/layout feature with my SQL code.
Jerome, with the native SQL you can’t create tables, only select. There are some plugins that allow you to insert records. However, you could use the virtual list technique and loop through your result, then set the grouped data into the virtual table.
I have blog post on how to create virtual tables, which has been around for a few years and was originated by Bruce Robertson. You can see my take on it at http://mightydata.com/virtual-list-in-3-easy-steps/
Anders
Thanks Anders. Though I’ve used ExecuteSQL function, I’ve had a great deal of difficulty getting date queries to work correctly, but you’re example shows how to put it into arguments parameter which just isn’t documented worth a hoot in filemaker’s help or other areas.
Glad to hear it helped, Peter!
—
Anders
Hi,
I use to protect fields name containing spaces with double quotes….but how can I protect double quote in field name ?
In my case then field is named Journaliste “indispensable”
It is stupid but it is impossible to change it.
Any idea ?
Regards
Emmanuel