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

FileMaker ExecuteSQL Guide

Learn how ExecuteSQL works, fix common errors, and test queries instantly.

Test Your ExecuteSQL Query See how it works

SQLite engineSchema autocompleteNo server neededInstant error feedbackReal examples

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. Use LIMIT instead of FETCH FIRST n ROWS ONLY is another common field-adjacent 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 ?.

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.

Stop guessing why your ExecuteSQL returns ?

Test your query in FM Dojo's SQL Fiddle and get the actual error — not a question mark.

Test Your ExecuteSQL Query Free