FileMaker Pro 12 Adds Native SQL Queries

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.

About Anders Monsen

Anders Monsen is the Architect of Integration for MightyData. Anders is a certified FileMaker Developer 9-12, Authorized Trainer 9-12 and FileMaker Developer Conference speaker. He's a runner and triathlete, and spends his other free time driving his two kids to various practices and games.
This entry was posted in FileMaker and tagged , , , . Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.

29 Responses to FileMaker Pro 12 Adds Native SQL Queries

  1. Thanks Anders. Though I’ve used ExecuteSQL function, I was unclear about the arguments parameter. Your example was very insightful.

  2. Anders Monsen says:

    Thanks, Daniel. It’s a neat feature of the ExecuteSQL function.

  3. Jose E Lopez Rosario says:

    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.

  4. Jason Gan says:

    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.

  5. Anders Monsen says:

    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.

  6. Anders Monsen says:

    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.

  7. Yury Udis says:

    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

  8. 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.

  9. Jose E Lopez Rosario says:

    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

  10. 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

  11. Keith Dennis says:

    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.

  12. Theo Gantos says:

    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);

  13. Anders Monsen says:

    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.

  14. Bob Hedges says:

    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.

    • Anders Monsen says:

      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.

  15. Chris says:

    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.

    • Anders Monsen says:

      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.

  16. Chris Millet says:

    … forgot to mention, for blazing speeds, index the search fields in MySQL.

    • Anders Monsen says:

      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.

  17. Chavoux says:

    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.

  18. Nick says:

    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.

  19. Jerome says:

    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.

  20. 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://www.mightydata.com/blog/virtual-list-in-three-easy-steps/

    Anders

  21. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>