If you’ve been frustrated with ExecuteSQL’s cryptic “?” symbol, ExecuteSQLe’s feedback is a welcome change.
John Newhoff
FileMaker’s ExecuteSQL function has been available for quite some time and is a powerful tool in your developer’s tool kit. While FileMaker’s native relational model is simple and intuitive, the ExecuteSQL function introduces an additional level of flexibility, enabling developers to access data across tables without creating extra table occurrences or relationships. This ability is especially useful in complex databases where managing the complexity of the relationship graph is essential.
With ExecuteSQL, you can access data dynamically, make reporting more efficient, and simplify your graph. But like any powerful tool, it comes with a few quirks that we’ll identify here. We’ll also discuss two custom functions we use at Portage Bay that make queries more resilient against schema changes.
What’s New with ExecuteSQLe in FileMaker 21.1?
The recent release of FileMaker 21.1 represents a significant enhancement to SQL functionality with the new ExecuteSQLe function. One of the biggest limitations has been addressed – error reporting. Unlike ExecuteSQL, ExecuteSQLe provides the return of clear error messages, enabling developers to troubleshoot queries much more effectively. If you’ve been frustrated with ExecuteSQL’s cryptic “?” symbol, ExecuteSQLe’s feedback is a welcome change.
Advantages of Using ExecuteSQL
- Flexible data querying and simplified graph
- Enhanced reporting without extra layouts or schema complexity
- Access to FileMaker metadata
- Server-side efficiency with PSoS
1. Flexible Data Querying and Simplified Graph
One of the primary benefits of ExecuteSQL is that it allows you to query data from any table in your FileMaker database without requiring a direct relationship in the relationship graph. This is a significant advantage for complex systems where additional table occurrences can clutter the graph and create dependencies. With ExecuteSQL, developers can keep the graph clean and manageable while accessing necessary data dynamically.
2. Enhanced Reporting Without Extra Layouts or Schema Complexity
ExecuteSQL offers a powerful approach to reporting. While traditional FileMaker setups often require dedicated layouts or additional relationships to pull together specific data, ExecuteSQL creates ad-hoc reports with a single SQL query. Gather the data you need without building extra structures. ExecuteSQL works especially well with Virtual List reporting to enable complex reporting with limited schema complexity. In large solutions, cutting down on layout clutter and reducing relationships makes everything faster and easier to manage.
3. Access to FileMaker MetaData
By using ExecuteSQL, you have access to metadata regarding your FileMaker solution, all of the table names and field names in your solution are available to you with simple SQL queries (See Claris documentation). We wrote about one use of this metadata in a blog post about synching to a MySQL database from FileMaker. FileMaker offers some functions for getting similar data, but again, the SQL queries allow you to access this information without creating any layouts and regardless of context.
4. Server-Side Efficiency with PSoS
ExecuteSQL is especially powerful when combined with Perform Script on Server (PSoS, an approach we often implement here at Portage Bay to improve performance. As we’ve covered, running ExecuteSQL via PSoS can reduce client-side load and improve responsiveness. As always, care should be taken when pushing scripts to the server. Any bugs or overuse of server resources won’t just affect one user, it will affect all.
Key Considerations and Cautions When Using ExecuteSQL
- ExecuteSQL offers a lot of power, but it also has nuances. Here are a few things to be mindful of:
- Error handling with the new ExecuteSQLe function
- Static references to tables and fields
- Performance issues with complex queries or queries on large tables
- FileMaker’s unique SQL implementation
1. Error Handling with the New ExecuteSQLe Function
The new ExecuteSQLe function resolves the error handling problems experienced with the old ExecuteSQL function. I doubt that the old function will be used any longer, other than for backward compatibility. For reference, and for people new to SQL in FileMaker, FileMaker’s old ExecuteSQL function lacks detailed error reporting.
If an error occurs in the query, it simply returns a question mark (“?”), making it difficult to troubleshoot. Unlike standard FileMaker scripts, which provide clear error messages, ExecuteSQL requires more detective work when something goes awry.
Whether using the new ExecuteSQLe or the older function, testing your queries with a tool like RazorSQL for making direct queries to the FileMaker Server ODBC engine can really simplify and help you test and fine-tune your SQL queries outside the FileMaker calculation dialog.
2. Static References to Tables and Fields
ExecuteSQL requires text references to table and field names, which means that if any table or field is renamed, the query will break. This static referencing can be a headache when schema changes are necessary, as each query must be updated manually.
We use two custom functions at Portage Bay – GFN (for fields) and GTN (for tables) – to create SQL-safe dynamic referencing. Which helps to reduce maintenance time and query fragility. See below for details on these custom functions.
3. Performance Issues with Complex Queries or Queries on Large Tables
ExecuteSQL queries are efficient for many needs, but care must be taken. FileMaker pulls the data over to the client to perform the query, so queries on large tables can be a significant performance problem. Likewise, more complex queries involving multiple joins can slow down performance.
When considering ExecuteSQL it is important to think about the size of the dataset being queried and to consider server-side processing with PSoS, as noted earlier.
4. FileMaker’s Unique SQL Implementation
ExecuteSQL in FileMaker doesn’t support the entire SQL standard, and some SQL functions used in other systems may not be available. The most important limitation to be aware of is that ExectueSQL only supports the SQL Select statement. You cannot use the Insert, Update, or Delete statements with ExecuteSQL. See the Claris help documentation for more details.
Multiple third-party plugins are available which give you access to Insert/Update/Delete functionality. Understanding FileMaker’s SQL implementation and limitations will save valuable time and potential frustration.
Using RazorSQL or another SQL testing tool to validate queries beforehand can help avoid syntax and compatibility issues before deploying them in FileMaker.
“You can write complex SQL queries in other SQL implementations that will likely not work in FileMaker”
Custom Functions for SQL-Safe Dynamic Referencing: GFN and GTN
Two Custom Functions We Rely on to Make Queries More Resilient
To avoid issues with static referencing, we rely on two simple custom functions at Portage Bay: GFN (GetFieldName) and GTN (GetTableName).
These functions help maintain query integrity when tables or fields are renamed, reducing maintenance time and making your ExecuteSQL queries more resilient. Here are the definitions, from the original 2012 FileMaker Hacks blog post where we found them.
By using GFN and GTN, your ExecuteSQL queries will automatically adjust to schema changes, saving time and reducing the risk of broken queries.
Custom Function: GFN
Accepts Parameter: field
/* GFN [for GetFieldName] returns only the name of the field, without the relationship portion, as opposed to GetFieldName() which also returns the relationship. This is to be used in SQL queries to protect the field references from breaking if the field is renamed.
The Quote() function "escapes" problematic characters and SQL reserved words, if any -- otherwise, no harm done. */
Let ( [
a = GetFieldName ( field ) ;
b = Substitute ( a ; "::" ; ¶ )
] ;
Quote ( GetValue ( b ; 2 ) )
) // end let
```Custom Function: GTN
Accepts Parameter: field
/* GTN [for GetTableName] returns only the name of the relationship, i.e., the table occurrence (TO) name, as opposed to GetFieldName() which returns the relationship + the field name. This is to be used in SQL queries to protect the TO references from breaking if the TO is renamed.
The Quote() function "escapes" problematic characters and SQL reserved words, if any -- otherwise, no harm done. */
Let ( [
a = GetFieldName ( field ) ;
b = Substitute ( a ; "::" ; ¶ )
] ;
Quote ( GetValue ( b ; 1 ) )
) // end let
```Final Thoughts
ExecuteSQL is a powerful tool for FileMaker developers, offering enhanced data flexibility, simplified reporting, and reduced dependency on the relationship graph. However, it is essential to stay mindful of its limitations, such as limited error handling and static references. Custom functions like GFN and GTN can make ExecuteSQL queries more resilient, and using PSoS can offload processing to the server for more responsive performance.
Now with FileMaker 21.1’s new ExecuteSQLe function, you have even more options for efficient SQL-driven solutions. ExecuteSQLe improves error handling, making it a compelling option for developers looking to build robust, data-centric solutions in FileMaker.
Please feel free to contact us with any questions you have or to share your experience with custom functions. We’re always eager to discuss, assist, and learn more!
FAQ
To use ExecuteSQL, use the syntax:
ExecuteSQL ( “SELECT column FROM table WHERE condition” ; separator ; row separator )
It returns data from a table based on the SQL query you provide. You can use it in calculations, scripts, or custom functions.
If no records are found, ExecuteSQL returns an empty result (“”), not an error. If the query returns multiple rows and you’re using a single result, it will return only the first row.
Yes, ExecuteSQL supports multi-table queries using JOIN operations. For example:
ExecuteSQL ( “SELECT table1.column FROM table1 JOIN table2 ON table1.id = table2.id WHERE table2.condition” )
Ensure proper SQL join syntax for correct results.
No, ExecuteSQL is a read-only function. It can retrieve data, but cannot modify, update, or delete records. For data manipulation, you must use FileMaker’s native scripting commands like Set Field, Replace Field Contents, or ExecuteSQL in conjunction with other FileMaker script steps.
To handle special characters, ensure you properly escape single quotes by doubling them (e.g., ‘O”Reilly’). For other special characters, you might need to use specific encoding or consider using FileMaker’s Quote() function for fields that contain special characters.
ExecuteSQL performance can be impacted by complex queries, large datasets, or inefficient joins. To optimize performance, ensure indexes on fields involved in joins or where conditions, reduce unnecessary complexity in queries and minimize the number of calls to ExecuteSQL in scripts or calculations.
To return multiple values, ExecuteSQL will return all matching rows, separated by a specified row separator. For example:
ExecuteSQL ( “SELECT name FROM users WHERE active = 1” ; “,” ; ¶ )
This will return a list of names, separated by commas, with each name on a new line.
Yes, ExecuteSQL can be used in calculation fields, but keep in mind that its performance can be slower in complex calculations. It’s best suited for retrieving single values or lists from related tables, not for frequent or large queries in calculation fields.
Yes, ExecuteSQL can handle aggregate functions such as COUNT(), SUM(), AVG(), etc. For example:
ExecuteSQL ( “SELECT COUNT(*) FROM table WHERE condition” )
This allows you to retrieve summary data directly without needing to use FileMaker’s summary fields.
If the query is invalid, ExecuteSQL returns an empty result (“”). However, it does not generate an error message. You should validate your SQL queries and test them in FileMaker’s Data Viewer or a more SQL-friendly tool to ensure correct syntax.
About the Author
John Newhoff’s business background and years of database design, computer, and network configuration, and troubleshooting experience allow him to see beyond the immediate problem to the long-term solution. John is the Senior Partner of Portage Bay, our lead 4th Dimension developer, and creator of sophisticated cross-platform solutions.