Does ExecuteSQL use Table Occurrence names or base table names?

ExecuteSQL uses base table names only — not Table Occurrence names from the relationship graph. This is a common source of errors when a TO has been renamed.

ExecuteSQL

Stop Guessing Why Your ExecuteSQL Returns `?`

Every FileMaker developer has stared at a ? with no idea why. FM Dojo's SQL Fiddle runs your query and shows the actual error. Build with schema on one side, copy results as a ready-to-paste ExecuteSQL() calculation, and catch FileMaker-specific mistakes before they reach production.

View Plans

See how it works

SQLite engineCopy as ExecuteSQLLIMIT warningsCalculation evaluatorContext-aware testing

ExecuteSQL mental model

Base table SQL, separated from the relationship graph

Relationship graph

Customers UI→Orders Portal

≠

ExecuteSQL uses base tables

SELECT total FROM Orders WHERE customer_id = ?

In the Code Editor

The SQL Builder Does the Annoying Parts

The public guide explains ExecuteSQL. The FM Dojo Code Editor gives you the working surface: schema on one side, query on the other, a run button, and an export button that formats the query as a FileMaker ExecuteSQL() calculation.

Build with schema beside the query

Define or paste a mock schema, write the query, and run it in-browser before you put it into a FileMaker calculation.

Copy as ExecuteSQL

Once the SQL works, copy it as a FileMaker ExecuteSQL()calculation. Date literals are converted into safe placeholders with arguments.

Catch FileMaker-specific SQL mistakes

The builder calls out FileMaker quirks, including the common LIMITmistake. Use FETCH FIRST n ROWS ONLY instead.

New evaluator

Test the calculation around the SQL too

ExecuteSQL usually lives inside a bigger FileMaker calculation. The new FileMaker Calculation Evaluator lets you paste JSON, XML, text, fields, and variables as context, then evaluate deterministic FileMaker calculations without guessing what the result will be.

Unsupported functions, missing fields, and missing variables are shown clearly, and the evaluator includes a capabilities panel so you can see what it can run today.

Data / context

{
  "fields": {
    "Invoices::Total": 1200,
    "Invoices::Paid": 300
  },
  "variables": {
    "$threshold": 500
  }
}

Calculation

Let ( [
  balance =
    Invoices::Total
    - Invoices::Paid
] ;
  If ( balance > $threshold ;
    "Follow up" ;
    "OK"
  )
)

Result: Follow up

What developers say

“I just asked it for syntax I could paste into the Data Viewer to transform some JSON. At first the code it generated wasn't working, so I told it the error — at which point it a) told me what mistake it had made, and then b) produced working code. And this solved a real-world challenge for me.”

Kevin Frank

The Basics

What Is FileMaker's ExecuteSQL?

ExecuteSQL allows SQL queries inside FileMaker calculations. Introduced in FileMaker 12, it is commonly used for reporting, aggregation, and faster data queries that bypass the relationship graph.

// Basic syntax

ExecuteSQL (

"SELECT name FROM customers WHERE id = ?" ;

"" ; // field separator

"" ; // row separator

customer_id // ? argument

)

Reference

ExecuteSQL Syntax

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

Parameter	Description
sqlQuery	SQL SELECT statement
fieldSeparator	Separator inserted between returned fields in each row
rowSeparator	Separator inserted between rows
arguments	Values substituted for `?` placeholders in the query, in order

Common Mistakes

Common ExecuteSQL Errors

Using TO names instead of base table names

ExecuteSQL uses base table names, not Table Occurrence names. If your TO is Contacts_portal, the query must reference Contacts.

Incorrect quoting in SQL statements

String literals in FileMaker SQL must use single quotes inside the query string. Mixing up FileMaker's double-quote strings with SQL single-quote literals is a common source of errors.

Using incorrect field references

Field names must match exactly as defined in the table — including case on some systems. Using LIMIT instead of FETCH FIRST n ROWS ONLY is another common query mistake.

Missing parameter placeholders (?)

If your query contains ? placeholders, each one must have a corresponding argument passed after the rowSeparator, in order. A mismatch returns ?.

View Plans

Examples

Real ExecuteSQL Examples

Count records

ExecuteSQL (

"SELECT COUNT(*) FROM Customers" ;

"" ;

)

Filtered query with ?

ExecuteSQL (

"SELECT name FROM Customers

WHERE country = ?" ;

"" ;

"USA"

)

Multiple fields, custom separators

ExecuteSQL (

"SELECT name, city FROM Customers" ;

" | " ; // field sep

¶ // row sep

)

Performance

When ExecuteSQL Is Faster Than Relationships

ExecuteSQL can be faster for reporting, aggregate calculations, and cross-table queries where traditional relationships are slower or complex. Because it bypasses the relationship graph and runs directly against base tables, it avoids the overhead of TO chains and portal filtering — especially for COUNT, SUM, and multi-table JOIN queries.

Debugging

Debugging ExecuteSQL

1
Test SQL queries outside FileMaker using FM Dojo's SQL Fiddle — it shows real error messages instead of ?
2
Verify you're using base table names, not Table Occurrence names
3
Check your field and row separators — an empty string is often the right choice for single-value results
4
Check parameter placeholders — every ? needs a corresponding argument passed after the rowSeparator

FAQ

Common Questions

Why does ExecuteSQL return '?'

A ? means the query failed. Common causes: using a Table Occurrence name instead of the base table name, a syntax error, an unsupported keyword like LIMIT, or a missing parameter placeholder. FM Dojo's SQL Fiddle shows the actual error message.

Why does ExecuteSQL return empty results?

Empty results mean the query ran successfully but found no matching records. Check your WHERE clause conditions, verify field names are correct, and confirm the data you expect actually exists in the table.

Does ExecuteSQL respect FileMaker relationships?

No. ExecuteSQL queries base tables directly, bypassing the relationship graph. This makes it powerful for cross-table reporting and aggregates — but you must use base table names, not Table Occurrence names.

FM Dojo Toolkit

More Tools for FileMaker Developers

ExecuteSQL query validator

Test queries against a live SQLite sandbox before deploying.

FileMaker JSON tools

Build and validate JSONSetElement and JSONGetElement calls with AI.

FileMaker script builder

Write and export FileMaker scripts as native clipboard XML.

AI assistant for FileMaker

Ask anything about FileMaker — scripts, calculations, schema, relationships.