The Forgotten Superpower of FileMaker 2026: SQL CRUD

Sep 28, 2026 · @Dimitris Kokoutsidis

The superpower that almost nobody uses

FileMaker 2026 can build a relational schema from plain SQL text. That covers tables, fields, relationships, indexes, auto-enter values, validation and external container storage. You need no plugin, no Manage Database clicks and no clipboard XML. The script step is Execute SQL, and the ODBC driver it talks through is Claris’s own, and free.

The step itself is old: it originated in FileMaker 6.0 or earlier. The FileMaker 13 SQL Reference (2013) already listed CREATE TABLE, ALTER TABLE, CREATE INDEX and DROP INDEX.

It is forgotten because most of us met SQL in FileMaker through the ExecuteSQL() calculation function, which supports only SELECT. The script step with almost the same name does everything else, but only against an ODBC data source. The trick in this article is to make the file an ODBC data source for itself.

FileMaker 2026 changes two things that make it worth a second look. A FOREIGN KEY now builds the relationship in the graph; in 2025 it could not. And the User Name and Password boxes are now calculations, so a credential can come from a variable instead of being typed into the step.

The scripts come from two files in the companion repository, DimitrisKok/filemaker-2026-sql-crud on GitHub: filemaker/Execute_SQL_Example_2026_v1_0.fmp12 (Examples 1 to 12) and filemaker/Execute_SQL_Example_2025_v1_0.fmp12 (Example 1, for 2025). Every one is verified in FileMaker. The repository also publishes SHA-256 hashes for every file in CHECKSUMS.sha256; check your download against them before you open it. The full step reference, including its clipboard XML, is on fmscript.org.

What “from scratch” and “CRUD” mean, exactly

SQL builds the schema inside an existing file. It does not build the file itself. Here is the line between what SQL does and what still needs a person in Manage Database or Layout mode.

SQL creates	SQL does not create
Tables, and their table occurrences	The .fmp12 file
Fields of every basic type, containers included	Layouts, and fields placed on them
Relationships, from FOREIGN KEY (FileMaker 2026 only)	Modification auto-enters (date, time, timestamp, account)
Creation auto-enters, from DEFAULT	Calculation and summary fields
Validation, from VARCHAR(n)	Scripts, value lists, privilege sets
Indexes, one column at a time	Multi-column indexes
External container storage, open or secure	The container base directory it points at

The CRUD in the title is complete, but it takes two tools. The Execute SQL step has nowhere to put a result: it runs a statement and reports success or an error, and returns no rows.

Operation	Tool
Create, Update, Delete, rows and schema	The Execute SQL script step
Read, from the current file	The ExecuteSQL() calculation function (Example 9)
Read, from an external source, as records	Import Records from ODBC

The function takes only SELECT, reads the current file’s tables, and returns text, or ? on any error.

Before the first script: the manual setup

“No plugins” is true. “No setup” is not. These steps must be in place on every machine that runs the scripts, and each missing one fails with its own error. Do them by hand once, in this order, so you know what each one does.

Give the account the fmxdbc extended privilege. In Manage Security, edit the privilege set and enable Access via ODBC/JDBC. Without it, the driver refuses the connection.
Turn on ODBC/JDBC sharing. In FileMaker Pro, choose File > Sharing > Enable ODBC/JDBC and set it to On. The privilege alone only allows access; sharing actually opens it. On FileMaker Server, enable the ODBC/JDBC connector in Admin Console instead.
Install the FileMaker ODBC client driver. It is free from Claris and is not a plugin. The driver uses port 2399, which must stay reserved for it. On macOS you also need an ODBC driver manager; the examples were captured on Windows.
Create the loopback DSN. Make a system DSN named odbcSelf2026 that points at localhost and at your copy of the 2026 file (odbcSelf2025 for the 2025 file). On Windows, use the 64-bit ODBC Data Source Administrator, to match 64-bit FileMaker Pro.
Set the container base directory. Example 1 stores three container fields externally. In File > Manage > Containers, add [database location]/Execute_SQL_Example_2026_v1_0/ as a base directory. Without it, CREATE TABLE fails with error 1408 and ODBC detail 8315.
For Examples 10 and 11 only, build the MariaDB source. Run fixtures/gemini_mariadb_fixture.sql from the repository, as a MariaDB administrator, to create the gemini database, its persons table and a demonstration account. Point a DSN named gemini at it.

Use your ODBC administrator’s test, if it has one; a pass there means steps 1 to 4 are right.

The DSN is the part that travels badly. It lives on the machine, not in the file. Send the .fmp12 to a client and the scripts fail on their computer until someone builds the same DSN there.

Where the step runs also matters. It works in FileMaker Pro, not in FileMaker Go. On FileMaker Server, WebDirect, the Data API and Custom Web Publishing it runs only with With dialog: Off, because nothing there can show a dialog, so the credentials must be saved with the step.

Example 1: CREATE TABLE, in full

One statement creates a 27-field table, its table occurrence, its relationship to Customers and three external containers. Here is the script exactly as captured, comments included. Every later example uses the same frame around a different SQL text.

# ============================================
# Execute SQL — Example 1. CREATE TABLE — a table, its fields and a relationship, from SQL.
# Captured from: Execute_SQL_Example_2026_v1_0.fmp12
# ============================================

# 🔑 THE EXAMPLES ARE COMPLETE CRUD. Examples 1-8 create, change and remove. Example 9
# reads one row with the ExecuteSQL calculation function. Example 12 removes the example table.
# Import Records from ODBC is the separate path for importing an external query result as records.
#
# 🚨 RUN THIS ONCE PER SET. A table with the same name must not already exist, so a second run
# fails — until Example 12 drops the table, which lets the set start again from here.
#
# 🔑 THE NAMES ARE ALL IN DOUBLE QUOTES. A name that does not begin with a letter — `__pk…`,
# `_fk…`, `_static…` — must be a quoted identifier. The rest are quoted for consistency.
# ============================================

Enter Browse Mode [ Pause: Off ]

# ⚠️ With error capture on, FileMaker shows no alert of its own. Every dialog below is the
# script's own, built from the two error values captured after the step.
Set Error Capture [ On ]

# 🔑 odbcSelf2026 IS A SYSTEM DSN THAT POINTS BACK AT THIS FILE, through FileMaker's own 26.0.3
# ODBC driver. Execute SQL talks to ODBC data sources, not to FileMaker files — so the file
# reaches itself as one.
#
# ⚠️ `With dialog: Off` NEEDS `Save credentials: On`. Nothing can prompt for a user name or
# password, so the credentials have to be stored with the step.
#
# 🔑 BOTH CREDENTIALS ARE QUOTED — "Admin", "admin". User Name and Password are CALCULATIONS,
# so a literal value is a quoted string.
#
# 🔑 THE FOREIGN KEY BUILDS THE RELATIONSHIP. `FOREIGN KEY REFERENCES "Customers"("__pkCustomerID")`
# creates the relationship in the relationships graph — Customers must already exist, and the two
# columns must be the same type. A relationship that would close a cycle fails with error 8201.
#
# ℹ️ `DEFAULT` takes a constant or one of the named values: CURRENT_USER, CURRENT_DATE,
# CURRENT_TIME, CURRENT_TIMESTAMP. A constant becomes auto-enter data — `DEFAULT 1` is
# Auto-enter: "1" on `_staticOne`.
#
# 🔑 THE SCALE BECOMES A TRUNCATE. `NUMERIC(2,2)` and `DECIMAL(2,3)` make Number fields with an
# auto-enter calculation that replaces the existing value:
#     Percentage_1   Truncate ( Percentage_1 ; 2 )
#     Percentage_2   Truncate ( Percentage_2 ; 3 )
# ⚠️ TRUNCATED, NOT ROUNDED — 0.129 stored in Percentage_1 becomes 0.12. Only the scale (the
# second number) is carried; the precision (the first) sets nothing.
#
# ℹ️ `VARCHAR(20)` becomes a validation, "Maximum number of characters = 20". Bare `VARCHAR`
# sets no limit.
#
# ⚠️ SQL HAS NO MODIFICATION DEFAULT. `DEFAULT` fills a value once, when the record is created —
# so ModificationDate, ModificationTime and ModificationTimestamp are created as plain Date, Time
# and Timestamp fields. Their modification auto-enter has to be set by hand in Manage Database.
#
# ℹ️ Photo_1-4 use the four binary type names — BLOB, VARBINARY, LONGVARBINARY, BINARY VARYING.
# Each one makes a container field, stored in the file.
#
# 🔑 Photo_5, Photo_6 AND Photo_7 STORE EXTERNALLY. `EXTERNAL '<path>'` names the folder, relative
# to the file's own location, and then one of:
#     OPEN '<folder>'          open storage, in that folder inside the path
#     SECURE                   secure storage
#     SECURE FEWER_FOLDERS     secure storage, "With fewer folders"
#
# 🚨 THE PATH MUST ALREADY BE A BASE DIRECTORY IN MANAGE CONTAINERS, or the step fails with
# 1408 and ODBC 8315, "Invalid container base directory specified". Here it is
# [database location]/Execute_SQL_Example_2026_v1_0/ — so the SQL says
# 'Execute_SQL_Example_2026_v1_0/', with no leading folder. The path uses forward slashes.
Execute SQL [
    With dialog: Off;
    ODBC Data Source: odbcSelf2026;
    User Name: "Admin";
    Password: "admin";
    Save credentials: On;
    SQL Text:
        CREATE TABLE "CustomerContactsSQL"
        ("__pkCustomerContactID" INT PRIMARY KEY,
        "_fkCustomerID" INT FOREIGN KEY REFERENCES "Customers"("__pkCustomerID"),
        "_staticOne" NUMERIC DEFAULT 1,
        "_staticZero" NUMERIC DEFAULT 0,
        "CreatedBy" VARCHAR DEFAULT CURRENT_USER,
        "CreationDate" DATE DEFAULT CURRENT_DATE,
        "CreationTime" TIME DEFAULT CURRENT_TIME,
        "CreationTimestamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        "ModificationDate" DATE,
        "ModificationTime" TIME,
        "ModificationTimestamp" TIMESTAMP,
        "Percentage_1" NUMERIC(2,2),
        "Percentage_2" DECIMAL(2,3),
        "FirstName" VARCHAR(20),
        "LastName" VARCHAR,
        "Company" VARCHAR,
        "Street" VARCHAR,
        "City" VARCHAR,
        "State" VARCHAR(2),
        "Zip" VARCHAR,
        "Photo_1" BLOB,
        "Photo_2" VARBINARY,
        "Photo_3" LONGVARBINARY,
        "Photo_4" BINARY VARYING,
        "Photo_5" BLOB EXTERNAL 'Execute_SQL_Example_2026_v1_0/' OPEN 'CustomerContactsSQL/Photo_5/',
        "Photo_6" BLOB EXTERNAL 'Execute_SQL_Example_2026_v1_0/' SECURE,
        "Photo_7" BLOB EXTERNAL 'Execute_SQL_Example_2026_v1_0/' SECURE FEWER_FOLDERS)
    ]
# ------------------------------------------------------
# Check for errors and log if needed
# ------------------------------------------------------
# 🔑 BOTH ERROR VALUES, CAPTURED IN ONE STEP. Get ( LastError ) is FileMaker's code;
# Get ( LastErrorDetail ) is the ODBC state — the driver's own reason, which the code alone does
# not give. Reading both inside one Let means the second read cannot see a reset value.
#
# ℹ️ $noError is never set. The Let's result is thrown away; only its two assignments matter.
Set Variable [ $errorSet[1] ; Value: Let( [
$error = Get(LastError) ;
$errorDetail = Get( LastErrorDetail )
] ;
$noError
) ]

If [ $error = 0 ]

# ⚠️ SQL MAKES THE TABLE, ITS OCCURRENCE AND THE RELATIONSHIP — NEVER A LAYOUT. This layout
# already exists in the file; the dialog below asks you to point it at the new occurrence and
# place the new fields, which is why Manage Layouts opens next.
Go to Layout [ “CustomerContactsSQL” (CustomerContactsSQL) ]

Show Custom Dialog [
    Title: "SQL Action Status";
    Message: "Table and customer relationship created successfully." & ¶ & 
             "Please assign the TO created to the Layout and add the Table Fields";
    Default Button: "OK", Commit: "Yes"
]

Open Manage Layouts

Open Manage Database

Else

    Show Custom Dialog [
        Title: "Error: " & $error;
        Message: "Error: " & $error & ¶ & error.Text ( $error ) & ¶ & $errorDetail;
        Default Button: "OK", Commit: "No"
    ]

    Exit Script [ Result: -1 ]
End If

# ============================================
# Execute SQL — Example 1. CREATE TABLE — a table, its fields and a relationship, from SQL.
# Captured from: Execute_SQL_Example_2026_v1_0.fmp12
# ============================================

# 🔑 THE EXAMPLES ARE COMPLETE CRUD. Examples 1-8 create, change and remove. Example 9
# reads one row with the ExecuteSQL calculation function. Example 12 removes the example table.
# Import Records from ODBC is the separate path for importing an external query result as records.
#
# 🚨 RUN THIS ONCE PER SET. A table with the same name must not already exist, so a second run
# fails — until Example 12 drops the table, which lets the set start again from here.
#
# 🔑 THE NAMES ARE ALL IN DOUBLE QUOTES. A name that does not begin with a letter — `__pk…`,
# `_fk…`, `_static…` — must be a quoted identifier. The rest are quoted for consistency.
# ============================================

Enter Browse Mode [ Pause: Off ]

# ⚠️ With error capture on, FileMaker shows no alert of its own. Every dialog below is the
# script's own, built from the two error values captured after the step.
Set Error Capture [ On ]

# 🔑 odbcSelf2026 IS A SYSTEM DSN THAT POINTS BACK AT THIS FILE, through FileMaker's own 26.0.3
# ODBC driver. Execute SQL talks to ODBC data sources, not to FileMaker files — so the file
# reaches itself as one.
#
# ⚠️ `With dialog: Off` NEEDS `Save credentials: On`. Nothing can prompt for a user name or
# password, so the credentials have to be stored with the step.
#
# 🔑 BOTH CREDENTIALS ARE QUOTED — "Admin", "admin". User Name and Password are CALCULATIONS,
# so a literal value is a quoted string.
#
# 🔑 THE FOREIGN KEY BUILDS THE RELATIONSHIP. `FOREIGN KEY REFERENCES "Customers"("__pkCustomerID")`
# creates the relationship in the relationships graph — Customers must already exist, and the two
# columns must be the same type. A relationship that would close a cycle fails with error 8201.
#
# ℹ️ `DEFAULT` takes a constant or one of the named values: CURRENT_USER, CURRENT_DATE,
# CURRENT_TIME, CURRENT_TIMESTAMP. A constant becomes auto-enter data — `DEFAULT 1` is
# Auto-enter: "1" on `_staticOne`.
#
# 🔑 THE SCALE BECOMES A TRUNCATE. `NUMERIC(2,2)` and `DECIMAL(2,3)` make Number fields with an
# auto-enter calculation that replaces the existing value:
#     Percentage_1   Truncate ( Percentage_1 ; 2 )
#     Percentage_2   Truncate ( Percentage_2 ; 3 )
# ⚠️ TRUNCATED, NOT ROUNDED — 0.129 stored in Percentage_1 becomes 0.12. Only the scale (the
# second number) is carried; the precision (the first) sets nothing.
#
# ℹ️ `VARCHAR(20)` becomes a validation, "Maximum number of characters = 20". Bare `VARCHAR`
# sets no limit.
#
# ⚠️ SQL HAS NO MODIFICATION DEFAULT. `DEFAULT` fills a value once, when the record is created —
# so ModificationDate, ModificationTime and ModificationTimestamp are created as plain Date, Time
# and Timestamp fields. Their modification auto-enter has to be set by hand in Manage Database.
#
# ℹ️ Photo_1-4 use the four binary type names — BLOB, VARBINARY, LONGVARBINARY, BINARY VARYING.
# Each one makes a container field, stored in the file.
#
# 🔑 Photo_5, Photo_6 AND Photo_7 STORE EXTERNALLY. `EXTERNAL '<path>'` names the folder, relative
# to the file's own location, and then one of:
#     OPEN '<folder>'          open storage, in that folder inside the path
#     SECURE                   secure storage
#     SECURE FEWER_FOLDERS     secure storage, "With fewer folders"
#
# 🚨 THE PATH MUST ALREADY BE A BASE DIRECTORY IN MANAGE CONTAINERS, or the step fails with
# 1408 and ODBC 8315, "Invalid container base directory specified". Here it is
# [database location]/Execute_SQL_Example_2026_v1_0/ — so the SQL says
# 'Execute_SQL_Example_2026_v1_0/', with no leading folder. The path uses forward slashes.
Execute SQL [
    With dialog: Off;
    ODBC Data Source: odbcSelf2026;
    User Name: "Admin";
    Password: "admin";
    Save credentials: On;
    SQL Text:
        CREATE TABLE "CustomerContactsSQL"
        ("__pkCustomerContactID" INT PRIMARY KEY,
        "_fkCustomerID" INT FOREIGN KEY REFERENCES "Customers"("__pkCustomerID"),
        "_staticOne" NUMERIC DEFAULT 1,
        "_staticZero" NUMERIC DEFAULT 0,
        "CreatedBy" VARCHAR DEFAULT CURRENT_USER,
        "CreationDate" DATE DEFAULT CURRENT_DATE,
        "CreationTime" TIME DEFAULT CURRENT_TIME,
        "CreationTimestamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        "ModificationDate" DATE,
        "ModificationTime" TIME,
        "ModificationTimestamp" TIMESTAMP,
        "Percentage_1" NUMERIC(2,2),
        "Percentage_2" DECIMAL(2,3),
        "FirstName" VARCHAR(20),
        "LastName" VARCHAR,
        "Company" VARCHAR,
        "Street" VARCHAR,
        "City" VARCHAR,
        "State" VARCHAR(2),
        "Zip" VARCHAR,
        "Photo_1" BLOB,
        "Photo_2" VARBINARY,
        "Photo_3" LONGVARBINARY,
        "Photo_4" BINARY VARYING,
        "Photo_5" BLOB EXTERNAL 'Execute_SQL_Example_2026_v1_0/' OPEN 'CustomerContactsSQL/Photo_5/',
        "Photo_6" BLOB EXTERNAL 'Execute_SQL_Example_2026_v1_0/' SECURE,
        "Photo_7" BLOB EXTERNAL 'Execute_SQL_Example_2026_v1_0/' SECURE FEWER_FOLDERS)
    ]
# ------------------------------------------------------
# Check for errors and log if needed
# ------------------------------------------------------
# 🔑 BOTH ERROR VALUES, CAPTURED IN ONE STEP. Get ( LastError ) is FileMaker's code;
# Get ( LastErrorDetail ) is the ODBC state — the driver's own reason, which the code alone does
# not give. Reading both inside one Let means the second read cannot see a reset value.
#
# ℹ️ $noError is never set. The Let's result is thrown away; only its two assignments matter.
Set Variable [ $errorSet[1] ; Value: Let( [
$error = Get(LastError) ;
$errorDetail = Get( LastErrorDetail )
] ;
$noError
) ]

If [ $error = 0 ]

# ⚠️ SQL MAKES THE TABLE, ITS OCCURRENCE AND THE RELATIONSHIP — NEVER A LAYOUT. This layout
# already exists in the file; the dialog below asks you to point it at the new occurrence and
# place the new fields, which is why Manage Layouts opens next.
Go to Layout [ “CustomerContactsSQL” (CustomerContactsSQL) ]

Show Custom Dialog [
    Title: "SQL Action Status";
    Message: "Table and customer relationship created successfully." & ¶ & 
             "Please assign the TO created to the Layout and add the Table Fields";
    Default Button: "OK", Commit: "Yes"
]

Open Manage Layouts

Open Manage Database

Else

    Show Custom Dialog [
        Title: "Error: " & $error;
        Message: "Error: " & $error & ¶ & error.Text ( $error ) & ¶ & $errorDetail;
        Default Button: "OK", Commit: "No"
    ]

    Exit Script [ Result: -1 ]
End If

The SQL clauses map onto FileMaker options you already know. Open Manage Database after the run and check each one:

SQL clause	What FileMaker creates
`INT PRIMARY KEY`	Number field; the reference does not say what PRIMARY KEY sets, so check its validation
`FOREIGN KEY REFERENCES`	A relationship in the graph (2026 only)
`DEFAULT 1`, `DEFAULT CURRENT_USER`	Creation auto-enter (data, or account name)
`NUMERIC(2,2)`	Number field with a `Truncate ( ; 2 )` auto-enter
`VARCHAR(20)`	Text field; maximum 20 characters validation
`BLOB`, `VARBINARY`, `LONGVARBINARY`, `BINARY VARYING`	Container, stored in the file
`BLOB EXTERNAL … OPEN` / `SECURE`	Container with open or secure external storage

The Claris CREATE TABLE page documents the relationship and the external storage rules. The truncate behavior of the scale is not stated there; it was verified in the example file.

The same table in FileMaker 2025

The 2025 file runs the same statement with two changes, and both are 2025’s limits. Everything else, the defaults, the truncate and the external containers, behaves the same.

	FileMaker 2026	FileMaker 2025
Credentials	`User Name: "Admin"` — calculations, so a literal is quoted	`User Name: Admin` — plain text, unquoted
The foreign key	`INT FOREIGN KEY REFERENCES "Customers"("__pkCustomerID")` builds the relationship	`INT` only: a plain Number field, no relationship

So in 2025 the relationship is drawn by hand, and the 2025 script’s success dialog says so. Two traps follow from the credential change. A variable such as $user typed into the 2025 box is taken as those five characters, and the login fails. And Get ( AccountName ) unquoted in 2026 is evaluated as a function: the login becomes the current account.

One more trap when moving steps between versions: FileMaker’s own paste from 2025 into 2026 can turn a saved-off Save credentials back on, with no credentials stored. The step then prompts for them at run time, which breaks any server-side run.

Examples 2 to 12: the part that changes

Examples 2 to 8 and 12 keep Example 1’s frame: Enter Browse Mode, Set Error Capture, the same DSN and credentials, and the same two-value error check. Below is only what differs: the header notes and the Execute SQL step. Examples 9 to 11 are different in shape and are shown with more of their body. The full scripts are in the repository.

Example 2: INSERT, ten records in one statement

# 🔑 ONE STATEMENT, TEN RECORDS. `VALUES` takes one parenthesised list per record, separated by
# commas. Run Example 1 first: this writes into the table it creates.
#
# ℹ️ Text values are in SINGLE quotes; names are in DOUBLE quotes. A single quote inside a value
# is written twice — 'Don''t'.
#
# ℹ️ The key and the foreign key are given explicitly, 1 to 10. Columns not named in the list are
# not set by this statement.
Execute SQL [ … SQL Text:
        INSERT INTO "CustomerContactsSQL"
        ("__pkCustomerContactID", "_fkCustomerID", "FirstName", "LastName",
        "Company", "Street", "City", "State", "Zip")
        VALUES
        (1, 1, 'Anna', 'Meyer', 'Aster Systems', '12 Linden Street', 'Berlin', 'BE', '10115'),
        (2, 2, 'Nikos', 'Pappas', 'Attica Data', '25 Ermou Street', 'Athens', 'AT', '10563'),
        (3, 3, 'Sofia', 'Rossi', 'Forma Studio', '8 Via Roma', 'Rome', 'RM', '00184'),
        (4, 4, 'Lucas', 'Martin', 'Clair Solutions', '41 Rue Victor Hugo', 'Paris', 'PA', '75016'),
        (5, 5, 'Emma', 'Jensen', 'Nordic Works', '17 Havnegade', 'Copenhagen', 'CP', '1058'),
        (6, 6, 'Oliver', 'Smith', 'Bridge Software', '30 King Street', 'London', 'LD', 'SW1A 2AA'),
        (7, 7, 'Maria', 'Garcia', 'Iberia Services', '22 Calle Mayor', 'Madrid', 'MD', '28013'),
        (8, 8, 'Jan', 'Novak', 'Bohemia Labs', '14 Karlova', 'Prague', 'PR', '11000'),
        (9, 9, 'Eva', 'Kovacs', 'Danube Systems', '9 Vaci Street', 'Budapest', 'BU', '1052'),
        (10, 10, 'Lena', 'Andersson', 'Scandia Group', '6 Storgatan', 'Stockholm', 'ST', '11151')
    ]

# 🔑 ONE STATEMENT, TEN RECORDS. `VALUES` takes one parenthesised list per record, separated by
# commas. Run Example 1 first: this writes into the table it creates.
#
# ℹ️ Text values are in SINGLE quotes; names are in DOUBLE quotes. A single quote inside a value
# is written twice — 'Don''t'.
#
# ℹ️ The key and the foreign key are given explicitly, 1 to 10. Columns not named in the list are
# not set by this statement.
Execute SQL [ … SQL Text:
        INSERT INTO "CustomerContactsSQL"
        ("__pkCustomerContactID", "_fkCustomerID", "FirstName", "LastName",
        "Company", "Street", "City", "State", "Zip")
        VALUES
        (1, 1, 'Anna', 'Meyer', 'Aster Systems', '12 Linden Street', 'Berlin', 'BE', '10115'),
        (2, 2, 'Nikos', 'Pappas', 'Attica Data', '25 Ermou Street', 'Athens', 'AT', '10563'),
        (3, 3, 'Sofia', 'Rossi', 'Forma Studio', '8 Via Roma', 'Rome', 'RM', '00184'),
        (4, 4, 'Lucas', 'Martin', 'Clair Solutions', '41 Rue Victor Hugo', 'Paris', 'PA', '75016'),
        (5, 5, 'Emma', 'Jensen', 'Nordic Works', '17 Havnegade', 'Copenhagen', 'CP', '1058'),
        (6, 6, 'Oliver', 'Smith', 'Bridge Software', '30 King Street', 'London', 'LD', 'SW1A 2AA'),
        (7, 7, 'Maria', 'Garcia', 'Iberia Services', '22 Calle Mayor', 'Madrid', 'MD', '28013'),
        (8, 8, 'Jan', 'Novak', 'Bohemia Labs', '14 Karlova', 'Prague', 'PR', '11000'),
        (9, 9, 'Eva', 'Kovacs', 'Danube Systems', '9 Vaci Street', 'Budapest', 'BU', '1052'),
        (10, 10, 'Lena', 'Andersson', 'Scandia Group', '6 Storgatan', 'Stockholm', 'ST', '11151')
    ]

Open the new records and check _staticOne, CreatedBy and CreationTimestamp. The INSERT never names them, so a value there comes from the auto-enter that DEFAULT created in Example 1.

Example 3: UPDATE, one record by its key

# 🚨 THE WHERE CLAUSE IS WHAT LIMITS IT. `WHERE "__pkCustomerContactID" = 1` changes one record;
# without it, the same SET is applied to every record in the table.
Execute SQL [ … SQL Text:
        UPDATE "CustomerContactsSQL"
        SET "Company" = 'Aster Systems GmbH',
        "Street" = '18 Friedrichstrasse',
        "City" = 'Berlin',
        "State" = 'BE',
        "Zip" = '10117'
        WHERE "__pkCustomerContactID" = 1
    ]

# 🚨 THE WHERE CLAUSE IS WHAT LIMITS IT. `WHERE "__pkCustomerContactID" = 1` changes one record;
# without it, the same SET is applied to every record in the table.
Execute SQL [ … SQL Text:
        UPDATE "CustomerContactsSQL"
        SET "Company" = 'Aster Systems GmbH',
        "Street" = '18 Friedrichstrasse',
        "City" = 'Berlin',
        "State" = 'BE',
        "Zip" = '10117'
        WHERE "__pkCustomerContactID" = 1
    ]

Example 4: DELETE, one record by its key

# 🚨 THE WHERE CLAUSE IS WHAT LIMITS IT. Without it this empties the table.
#
# ⚠️ THERE IS NO UNDO. A record deleted through SQL is gone, as with Delete Record/Request.
Execute SQL [ … SQL Text:
        DELETE FROM "CustomerContactsSQL"
        WHERE "__pkCustomerContactID" = 10
    ]

# 🚨 THE WHERE CLAUSE IS WHAT LIMITS IT. Without it this empties the table.
#
# ⚠️ THERE IS NO UNDO. A record deleted through SQL is gone, as with Delete Record/Request.
Execute SQL [ … SQL Text:
        DELETE FROM "CustomerContactsSQL"
        WHERE "__pkCustomerContactID" = 10
    ]

Example 5: CREATE INDEX

# 🔑 THE INDEX IS THE FIELD'S OWN INDEXING OPTION. On a text column FileMaker sets Indexing to
# Minimal and ticks "Automatically create indexes as needed" — so the change is visible in
# Manage Database, which is why the script opens it on success.
#
# ℹ️ One column only; multi-column indexes are not supported. FileMaker indexes on demand anyway —
# this builds the index now.
Execute SQL [ … SQL Text:
        CREATE INDEX ON "CustomerContactsSQL" ("LastName")
    ]

# 🔑 THE INDEX IS THE FIELD'S OWN INDEXING OPTION. On a text column FileMaker sets Indexing to
# Minimal and ticks "Automatically create indexes as needed" — so the change is visible in
# Manage Database, which is why the script opens it on success.
#
# ℹ️ One column only; multi-column indexes are not supported. FileMaker indexes on demand anyway —
# this builds the index now.
Execute SQL [ … SQL Text:
        CREATE INDEX ON "CustomerContactsSQL" ("LastName")
    ]

On a text field the index is Minimal; on any other type it is All.

Example 6: DROP INDEX, the reverse of Example 5

# 🔑 DROPPING SETS INDEXING TO NONE AND CLEARS "Automatically create indexes as needed". The
# field stays; only its index goes. The script opens Manage Database so the change can be seen.
Execute SQL [ … SQL Text:
        DROP INDEX ON "CustomerContactsSQL" ("LastName")
    ]

# 🔑 DROPPING SETS INDEXING TO NONE AND CLEARS "Automatically create indexes as needed". The
# field stays; only its index goes. The script opens Manage Database so the change can be seen.
Execute SQL [ … SQL Text:
        DROP INDEX ON "CustomerContactsSQL" ("LastName")
    ]

Example 7: ALTER TABLE ADD COLUMN

# 🔑 ONE COLUMN PER STATEMENT. ALTER TABLE changes a single column each time it runs.
#
# ℹ️ The new field appears in Manage Database, which the script opens on success. It still has to
# be placed on a layout by hand.
Execute SQL [ … SQL Text:
        ALTER TABLE "CustomerContactsSQL"
        ADD COLUMN "Email" VARCHAR(254)
    ]

# 🔑 ONE COLUMN PER STATEMENT. ALTER TABLE changes a single column each time it runs.
#
# ℹ️ The new field appears in Manage Database, which the script opens on success. It still has to
# be placed on a layout by hand.
Execute SQL [ … SQL Text:
        ALTER TABLE "CustomerContactsSQL"
        ADD COLUMN "Email" VARCHAR(254)
    ]

Example 8: ALTER TABLE DROP COLUMN, the reverse of Example 7

# 🚨 THE FIELD AND ITS DATA GO TOGETHER, AND THERE IS NO UNDO. This removes the Email field
# Example 7 added, and whatever was typed into it.
#
# ℹ️ One column per statement, as with ADD.
Execute SQL [ … SQL Text:
        ALTER TABLE "CustomerContactsSQL"
        DROP COLUMN "Email"
    ]

# 🚨 THE FIELD AND ITS DATA GO TOGETHER, AND THERE IS NO UNDO. This removes the Email field
# Example 7 added, and whatever was typed into it.
#
# ℹ️ One column per statement, as with ADD.
Execute SQL [ … SQL Text:
        ALTER TABLE "CustomerContactsSQL"
        DROP COLUMN "Email"
    ]

Example 9: READ, with the ExecuteSQL function

This one is not the Execute SQL step at all. It is how the set reads, and it changes nothing: no record, found set or layout.

# 🔑 ExecuteSQL IS A CALCULATION FUNCTION, NOT THE EXECUTE SQL SCRIPT STEP. It reads tables
# in the current FileMaker file and returns text without changing the current record, found set,
# or layout.
#
# ℹ️ The question mark in the SQL is a parameter placeholder. The final argument, 1, supplies
# its value without concatenating data into the query text.
#
# 🔑 " | " separates fields. ¶ separates rows. This query returns one row, so the result is:
# Aster Systems GmbH | 18 Friedrichstrasse | 10117
# ============================================

Set Variable [ $customerContact[1] ; Value: ExecuteSQL (
    "SELECT \"Company\", \"Street\", \"Zip\"
     FROM \"CustomerContactsSQL\"
     WHERE \"__pkCustomerContactID\" = ?" ;
    " | " ; ¶ ;
    1
) ]

If [ $customerContact = "?" ]
    Show Custom Dialog [ … "ExecuteSQL returned ?. Check the table, field names, and query syntax." … ]
    Exit Script [ Result: -1 ]

# 🔑 ExecuteSQL IS A CALCULATION FUNCTION, NOT THE EXECUTE SQL SCRIPT STEP. It reads tables
# in the current FileMaker file and returns text without changing the current record, found set,
# or layout.
#
# ℹ️ The question mark in the SQL is a parameter placeholder. The final argument, 1, supplies
# its value without concatenating data into the query text.
#
# 🔑 " | " separates fields. ¶ separates rows. This query returns one row, so the result is:
# Aster Systems GmbH | 18 Friedrichstrasse | 10117
# ============================================

Set Variable [ $customerContact[1] ; Value: ExecuteSQL (
    "SELECT \"Company\", \"Street\", \"Zip\"
     FROM \"CustomerContactsSQL\"
     WHERE \"__pkCustomerContactID\" = ?" ;
    " | " ; ¶ ;
    1
) ]

If [ $customerContact = "?" ]
    Show Custom Dialog [ … "ExecuteSQL returned ?. Check the table, field names, and query syntax." … ]
    Exit Script [ Result: -1 ]

Neither DSN nor fmxdbc is involved: the function runs inside the file, under the current account’s privileges. On any error it returns ?, which is what the If tests.

Example 10: a calculated INSERT, into MariaDB

The same step, pointed at a different database. gemini is a DSN for MariaDB, and the SQL is built by a calculation from FileMaker values.

# 🔑 THE DATA SOURCE IS AN EXTERNAL DATABASE. `gemini` is a DSN for MariaDB, not for this
# file. Build it with fixtures/gemini_mariadb_fixture.sql, which creates the `persons` table and
# a local demonstration account.

# ⚠️ THAT ACCOUNT CAN SELECT, INSERT AND UPDATE `persons` — NOTHING ELSE. It cannot delete a row
# or change the schema, so Examples 10 and 11 insert and update, and stop there.

# The global email identifies this run. Example 11 uses it to update exactly this record.
Set Variable [
    $$calculatedSqlEmail[1];
    Value: "calculated.sql." & Lower ( Get ( UUID ) ) & "@example.test"
]
Set Variable [ $firstName[1] ; Value: "Codex" ]
Set Variable [ $lastName[1] ; Value: "O'Connor" ]

Set Error Capture [ On ]

# 🚨 THE ESCAPING IS NOT OPTIONAL. The step has no parameters, so every value goes into the SQL
# text as it is. An unescaped O'Connor ends the SQL string at the apostrophe and the statement
# fails — or, with the wrong value, does something else.
#
# 🔑 TWO KINDS OF QUOTE. The double quotes are FileMaker's: they delimit the text pieces of the
# calculation. The single quotes inside them are SQL's: they end up around each value.
#
# ℹ️ THE EMAIL IS UNIQUE PER RUN. `email` is a UNIQUE KEY in `persons`, so a fixed address would
# fail on the second run; Get ( UUID ) makes each one new.
Execute SQL [
    With dialog: Off;
    ODBC Data Source: gemini;
    User Name: "ai2fm_example";
    Password: "ai2fm-demo-only";
    Save credentials: On;
    Calculated SQL Text:
        Let ( [
            sqlFirstName = Substitute ( $firstName ; "'" ; "''" ) ;
            sqlLastName = Substitute ( $lastName ; "'" ; "''" ) ;
            sqlEmail = Substitute ( $$calculatedSqlEmail ; "'" ; "''" )
        ] ;
            "INSERT INTO persons (first_name, last_name, email) VALUES ('" &
            sqlFirstName & "', '" &
            sqlLastName & "', '" &
            sqlEmail & "')"
        )
    ]

# 🔑 THE DATA SOURCE IS AN EXTERNAL DATABASE. `gemini` is a DSN for MariaDB, not for this
# file. Build it with fixtures/gemini_mariadb_fixture.sql, which creates the `persons` table and
# a local demonstration account.

# ⚠️ THAT ACCOUNT CAN SELECT, INSERT AND UPDATE `persons` — NOTHING ELSE. It cannot delete a row
# or change the schema, so Examples 10 and 11 insert and update, and stop there.

# The global email identifies this run. Example 11 uses it to update exactly this record.
Set Variable [
    $$calculatedSqlEmail[1];
    Value: "calculated.sql." & Lower ( Get ( UUID ) ) & "@example.test"
]
Set Variable [ $firstName[1] ; Value: "Codex" ]
Set Variable [ $lastName[1] ; Value: "O'Connor" ]

Set Error Capture [ On ]

# 🚨 THE ESCAPING IS NOT OPTIONAL. The step has no parameters, so every value goes into the SQL
# text as it is. An unescaped O'Connor ends the SQL string at the apostrophe and the statement
# fails — or, with the wrong value, does something else.
#
# 🔑 TWO KINDS OF QUOTE. The double quotes are FileMaker's: they delimit the text pieces of the
# calculation. The single quotes inside them are SQL's: they end up around each value.
#
# ℹ️ THE EMAIL IS UNIQUE PER RUN. `email` is a UNIQUE KEY in `persons`, so a fixed address would
# fail on the second run; Get ( UUID ) makes each one new.
Execute SQL [
    With dialog: Off;
    ODBC Data Source: gemini;
    User Name: "ai2fm_example";
    Password: "ai2fm-demo-only";
    Save credentials: On;
    Calculated SQL Text:
        Let ( [
            sqlFirstName = Substitute ( $firstName ; "'" ; "''" ) ;
            sqlLastName = Substitute ( $lastName ; "'" ; "''" ) ;
            sqlEmail = Substitute ( $$calculatedSqlEmail ; "'" ; "''" )
        ] ;
            "INSERT INTO persons (first_name, last_name, email) VALUES ('" &
            sqlFirstName & "', '" &
            sqlLastName & "', '" &
            sqlEmail & "')"
        )
    ]

MariaDB receives 'O''Connor', which it stores as O'Connor. Unescaped, the apostrophe would end the SQL string halfway through the name.

Example 11: a calculated UPDATE, of that record

# 🔑 THE PAIR TO EXAMPLE 10. It updates the record Example 10 inserted, found by the email that
# script kept in $$calculatedSqlEmail — a global variable, so it survives between the two scripts.
If [ IsEmpty ( $$calculatedSqlEmail ) ]
    Show Custom Dialog [ … "Run Execute SQL Example 10 first." … ]
    Exit Script [ Result: -1 ]
End If

Set Variable [ $newLastName[1] ; Value: "O'Connor-Smith" ]
Set Variable [ $newCity[1] ; Value: "Athens" ]

Set Error Capture [ On ]

# 🚨 THE WHERE CLAUSE IS WHAT LIMITS IT. Without it, every row in `persons` would be updated.
Execute SQL [ … ODBC Data Source: gemini; … Calculated SQL Text:
        Let ( [
            sqlLastName = Substitute ( $newLastName ; "'" ; "''" ) ;
            sqlCity = Substitute ( $newCity ; "'" ; "''" ) ;
            sqlEmail = Substitute ( $$calculatedSqlEmail ; "'" ; "''" )
        ] ;
            "UPDATE persons SET last_name = '" &
            sqlLastName & "', city = '" &
            sqlCity & "' WHERE email = '" &
            sqlEmail & "'"
        )
    ]

# 🔑 THE PAIR TO EXAMPLE 10. It updates the record Example 10 inserted, found by the email that
# script kept in $$calculatedSqlEmail — a global variable, so it survives between the two scripts.
If [ IsEmpty ( $$calculatedSqlEmail ) ]
    Show Custom Dialog [ … "Run Execute SQL Example 10 first." … ]
    Exit Script [ Result: -1 ]
End If

Set Variable [ $newLastName[1] ; Value: "O'Connor-Smith" ]
Set Variable [ $newCity[1] ; Value: "Athens" ]

Set Error Capture [ On ]

# 🚨 THE WHERE CLAUSE IS WHAT LIMITS IT. Without it, every row in `persons` would be updated.
Execute SQL [ … ODBC Data Source: gemini; … Calculated SQL Text:
        Let ( [
            sqlLastName = Substitute ( $newLastName ; "'" ; "''" ) ;
            sqlCity = Substitute ( $newCity ; "'" ; "''" ) ;
            sqlEmail = Substitute ( $$calculatedSqlEmail ; "'" ; "''" )
        ] ;
            "UPDATE persons SET last_name = '" &
            sqlLastName & "', city = '" &
            sqlCity & "' WHERE email = '" &
            sqlEmail & "'"
        )
    ]

The result, in MariaDB: last_name is O'Connor-Smith and city is Athens.

Example 12: DROP TABLE, the one Claris does not document

# 🚨 THIS REMOVES THE TABLE AND EVERY RECORD IN IT, AND THERE IS NO UNDO. That is why it is the
# last example: every other example works on this table, so it goes only after all of them.
#
# 🔑 IT ALSO RESETS THE SET. With the table gone, Example 1 can create it again, and the examples
# can be run from the start.
#
# ⚠️ DROP TABLE IS NOT IN THE CLARIS SQL REFERENCE. There is no page for it and no mention of it.
# It works — this example is FM-verified — but nothing documents it.
Execute SQL [ … SQL Text:
        DROP TABLE "CustomerContactsSQL"
    ]

# 🚨 THIS REMOVES THE TABLE AND EVERY RECORD IN IT, AND THERE IS NO UNDO. That is why it is the
# last example: every other example works on this table, so it goes only after all of them.
#
# 🔑 IT ALSO RESETS THE SET. With the table gone, Example 1 can create it again, and the examples
# can be run from the start.
#
# ⚠️ DROP TABLE IS NOT IN THE CLARIS SQL REFERENCE. There is no page for it and no mention of it.
# It works — this example is FM-verified — but nothing documents it.
Execute SQL [ … SQL Text:
        DROP TABLE "CustomerContactsSQL"
    ]

The current SQL Reference lists CREATE TABLE, TRUNCATE TABLE, ALTER TABLE, CREATE INDEX and DROP INDEX. DROP TABLE is not among them. Treat it as working but unsupported: it may change in any release without notice.

The security cost of this superpower

Every capability in this article is also an exposure. Read this section before you enable ODBC/JDBC on anything that holds real data.

fmxdbc is a second door into the file. Turning it on opens a channel that runs SQL against the file over port 2399, outside the FileMaker layer entirely. Any ODBC client that reaches that port with a valid account can run the statements these scripts run, the schema-changing ones included. Claris warns plainly that shared data can be updated and deleted by other applications. Enable it per privilege set, never for all users, and turn sharing off when the schema work is done.

The saved password is in the step, and the step can be copied. With the dialog off, the credentials must be saved with the step. The examples save "Admin" / "admin", a Full Access account: fine for a demo file, a finding in production. Saved also means readable. Copy the step to the clipboard and the password is right there in the XML, as the password attribute of the step’s Profile element. Anyone who can open the Script Workspace and copy a step can read it.

FileMaker 2026 lets the secret leave the script text. User Name and Password are now calculations, so they can be $user and $password, set at run time. The step then stores the calculation, not the value. The secret still has to live somewhere, so this moves the problem rather than solving it, but it moves it out of every copy of the script.

Least privilege, as Examples 10 and 11 show it. Their MariaDB account can select, insert and update one table, and nothing else. A bug or a hostile value in those scripts cannot delete a row or drop the table. Give the loopback DSN the same treatment: a dedicated account with only what the script needs, never Admin.

Calculated SQL Text is an injection surface. The step has no parameters, so every value goes into the statement as text. A single quote in a value closes the SQL string, and a crafted value can close it and change what the statement does, for example widening its WHERE clause. Doubling quotes with Substitute ( value ; "'" ; "''" ) protects values inside single quotes, which is what Examples 10 and 11 do for every one. It does nothing for a number or a name built into the SQL outside quotes: check those first, with GetAsNumber for a number and a fixed list for a table or field name.

DROP TABLE is undocumented and unguarded. It runs, it removes the table and every record in it, and there is no undo and no confirmation. Because it is not in the reference, no reviewer reading the reference will think to defend against it. A hostile or careless SQL string reaching an fmxdbc-enabled file can drop a table as easily as it can select from one.

Full Access is not required. The DDL statements run for an account without Full Access, as long as its privilege set has fmxdbc. So the account’s rank does not protect the schema; the privilege set does. Give each saved-credential connection its own dedicated privilege set, with fmxdbc and only the access that script needs, and keep fmxdbc off every other set.

The defensive summary is short. Off by default. Per privilege set, never all users. A dedicated least-privilege account for any saved-credential connection, never Admin. In 2026, credentials from variables, not literals. Every value in a calculated statement escaped or checked. Sharing off once the schema is built. This attack surface deserves a longer treatment on cyber-fm.eu.

Testing the set: run order and what to check

The examples are a cycle. Example 1 builds the table, 2 to 9 work on it, 10 and 11 are a pair against MariaDB, and 12 removes the table so 1 can run again. Run them in order the first time.

#	Statement	Run when	Check after
1	CREATE TABLE	Table absent	Table, occurrence, relationship, auto-enters in Manage Database
2	INSERT	After 1	10 records; DEFAULT columns filled
3	UPDATE	After 2	Record 1 shows the Berlin GmbH address
4	DELETE	After 2	Record 10 gone; 9 remain
5	CREATE INDEX	After 1	LastName indexing set to Minimal
6	DROP INDEX	After 5	LastName indexing set to None
7	ADD COLUMN	After 1	Email field present
8	DROP COLUMN	After 7	Email field gone
9	SELECT, via ExecuteSQL()	After 3	Dialog shows `Aster Systems GmbH \| 18 Friedrichstrasse \| 10117`
10	Calculated INSERT, MariaDB	`gemini` DSN built	New `persons` row, last name `O'Connor`
11	Calculated UPDATE, MariaDB	After 10, same session	That row: `O'Connor-Smith`, `Athens`
12	DROP TABLE	Last	Table absent; ready for Example 1 again

Two rules matter more than the rest. A second run of Example 1 fails while the table exists; that error is expected, and Example 12 resets it. And Example 11 needs Example 10 in the same session, because the record is found by a global variable that Example 10 sets.

When a step fails, the error code alone tells you little. Read the detail:

Value	Returns
`Get ( LastError )`	1408, “Extended error (ODBC)”: the driver rejected something
`Get ( LastErrorDetail )`	The driver’s own message, for example `(8315): Invalid container base directory specified`

That is why every example reads both values in one Let, right after the step, with error capture on. Without error capture, FileMaker shows its own alert and the script never sees the detail.

Batteries included, but read the manual

The superpower is real. A relational schema from plain text, full CRUD across two built-in tools, and the same step reaching MariaDB or any other ODBC source. No plugins, only Claris’s own driver. It has been in the product for years; FileMaker 2026 makes it better, with relationships from FOREIGN KEY and credentials that no longer have to be typed into the script.

The catches are equally real. Setup is per machine, and the DSN does not travel in the file. The door you open reaches past the FileMaker security layer, so it belongs off by default and open only while you need it. And every value you build into a statement is yours to escape.

Download the two example files from the GitHub repository, check them against CHECKSUMS.sha256, and work on a copy. Do the setup steps once, and run the cycle from Example 1 to Example 12. The scripts will tell you, dialog by dialog, whether your setup is right. The complete step reference, clipboard XML included, is on fmscript.org.

Axelar