Write your first FileMaker ExecuteSQL query

ExecuteSQL is useful when you need a small result without changing the user’s current found set. Start with one table occurrence and a predictable question: which contacts are in Hanoi?

This walkthrough uses the ExecuteSQL calculation function, available from FileMaker Pro 12. It is different from the Execute SQL script step for external data sources. FileMaker Pro is required and sold separately.

1. Prepare three fictional records.

In a practice file, use a table occurrence named Contacts, with text fields ContactID, FullName and City. If your names differ, adapt the query identifiers before running it.

Sample records

ContactID   FullName      City
00042       Maya Chen     Hanoi
00043       Alex Rivera   Da Nang
00044       Sam Tran      Hanoi

Keep these values free of separators and line breaks for this first exercise. ExecuteSQL returns text, so the format of that text is part of your design.

2. Describe the selection in SQL.

SQL query

SELECT "ContactID", "FullName"
FROM "Contacts"
WHERE "City" = ?
ORDER BY "FullName"

The question mark is a placeholder for a value supplied separately. Quoted identifiers name the table occurrence and fields. The ORDER BY makes the output order deliberate; without it, do not depend on a particular record sequence.

Paste the SQL into the SQL formatter to inspect it. The tool does not know whether these identifiers exist in your file.

3. Wrap the query in ExecuteSQL.

Use this calculation in Data Viewer or as a Set Variable value. The backslash escapes each identifier quote inside the FileMaker text literal:

FileMaker calculation

ExecuteSQL (
    "SELECT \"ContactID\", \"FullName\"
     FROM \"Contacts\"
     WHERE \"City\" = ?
     ORDER BY \"FullName\"" ;
    " | " ; "¶" ; "Hanoi"
)

Use the SQL formatter’s ExecuteSQL template if you prefer to generate the quoting. The last argument supplies the first placeholder. If you add a second placeholder, add its value afterward in the same order.

4. Compare the result.

Expected result

00042 | Maya Chen
00044 | Sam Tran

The field separator is | ; the row separator is a return. Changing the bound city to Da Nang should return only Alex. A city with no matches should return empty text.

A ? result signals a query problem rather than “no contacts.” Check table occurrence names, field names, quote escaping, the number of arguments and SQL syntax. Reduce the query to one field and add the clauses back one at a time.

5. Keep these boundaries in mind.

Parameters are values. A placeholder cannot stand in for a table name, field name or sort direction. Keep identifiers controlled rather than taking them from arbitrary input.
Found sets are separate. A find on the current layout does not automatically restrict this query. Put the desired conditions in WHERE. Normal access privileges still matter.
The result is text. A separator inside a name can make later splitting ambiguous. This is not an escaped CSV export; choose a format appropriate for your real data.
This function reads. It does not insert or update records. Use a deliberate FileMaker workflow for changes.

Next, replace the literal city argument with a variable or field. Test apostrophes and non-ASCII characters as values; do not concatenate them into the SQL. For a production query, measure performance with realistic data and review identifier dependencies when renaming fields.

PUT IT INTO PRACTICE

Try it with Filesoft.

Open the SQL formatter

Want a guided learning path? Explore the free FileMaker courses.

Continue reading

Reference: ExecuteSQL. Examples are learning aids; check them in your own FileMaker working copy.

Write your first FileMaker ExecuteSQL query.