ExecuteSQL advanced patterns

Expert

Write multi-table JOINs, subqueries, and CASE expressions in ExecuteSQL to query FileMaker data without relationships or portals.

What you'll learn

How to write a two-table INNER JOIN in ExecuteSQL
How to use a subquery in a WHERE clause
How to use SQL CASE for conditional column values
FileMaker-specific SQL limitations to avoid

ExecuteSQL in FileMaker supports a subset of ANSI SQL that goes well beyond simple SELECT statements. JOIN, subqueries, CASE expressions, and string functions give you powerful data retrieval options that are impossible or impractical with pure relationship-based calculations. Knowing the supported SQL dialect -- and its limitations -- is essential before writing complex queries.

1/4

Two-table INNER JOIN

Join two base tables using their FileMaker field names. Table and field names are case-sensitive in the SQL string.

FileMaker Script

// List client names with their outstanding balances
ExecuteSQL (
  "SELECT c."Name", SUM(i."Amount") AS total
   FROM "Clients" c
   INNER JOIN "Invoices" i ON c."id" = i."ClientId"
   WHERE i."Status" = 'Outstanding'
   GROUP BY c."id", c."Name"
   ORDER BY total DESC" ;
  "," ;  // field separator
  "|"   // row separator
)

Boolean calculations and truthiness in FileMaker SQL aggregation in FileMaker calculations