|Mac & Win
Execute the SQL Statement against a FileMaker database.
|The target database name.
Can be empty to not limit query to one database.
Using a table in another database file may require you adding the other file as an external data source and adding the external table to your relationship graph to grant access.
|SQL Statement as a Text string
|Columns Separator, only used with SELECT statement.
Default is tab character, which has unicode code point 9.
You can pass delimiter as text (one character only) or as unicode code point (as number).
|Record Separator, only used with SELECT statement.
Default is return character, which has unicode code point 13.
You can pass delimiter as text or as unicode code point.
|Optional, pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement.
Returns result of SQL expression or error.
DescriptionExecute the SQL Statement against a FileMaker database.
This function requires FileMaker 11 or newer. Please use FM.ExecuteSQL on FileMaker 10 or older.
There must be an occurrence of the Table on the current tables' Graph in the file you specified. If filename is empty, you use current file. SELECT returns the the records in as a tabbed delimited text string. All other SQL commands return empty text.
Use FM.ExecuteSQL.LastError to check for errors.
Supported Commands: SELECT UPDATE (see limitation below) DELETE INSERT CREATE TABLE DROP TABLE CREATE INDEX DROP INDEX WHERE
String comparisons are Case Sensitive! WHERE First_Name="todd" will not find records that have "Todd" in the first Name Field. You can wrap the field you are searching for with "lower" like this WHERE lower(First_Name)="todd" KNOWN LIMITATIONS Schema editing Record Locking Errors are Not Reported! This means you must exercise caution when using UPDATE. CAUTION! This is a very powerful function!
You can put field names in double quotes and string values in single quotes.
Use FM.ExecuteSQLOnIdle for special commands which change database like ALTER TABLE.
Use FM.ExecuteSQL.LastErrorMessage and FM.ExecuteSQL.LastError to see error message.
Error 8309 means you did pass wrong data type, like string for a number.
You can make queries against FILEMAKER_TABLES and FILEMAKER_FIELDS to query information about fields in your solution. The functions FM.QueryBaseTableNames, FM.QueryFieldsForBaseTableName, FM.QueryFieldsForTableName and FM.QueryTableNames provide simplified functions for some queries.
FILEMAKER_TABLES and FILEMAKER_BASETABLES tables contains:
FILEMAKER_FIELDS table contains:
3. FieldType (the SQL data type, not the FileMaker data type)
5. FieldClass (Normal, Summary, Calculated)
Some standard SQL functions work in FileMaker including CHR, COALESCE, LEFT, LENGTH, LOWER, LTRIM, MID, RIGHT, RTRIM, SPACE, SUBSTR, SUBSTRING, TRIM and UPPER. There may be more!
If you need longer delimiters, please use FM.SQL.Execute with FM.SQL.Text to use multiple characters for column and record delimiters.
This function takes variable number of parameters. Pass as much parameters as needed separated by the semicolon in FileMaker.
Please repeat Params parameter as often as you need.
Select records using delimiters
SELECT records with a Where clause
Insert records with parameters 2 and 3:
Create Record with values via SQL
Set value for all records of a table:
Set timestamp to today:
Update 5 fields:
Create an index:
Select with parameter:
Query value of a repeating field with :
Set value of repeating field:
Query only first row:
Insert with parameters:
Set repeating global field values:
Set timestamp field:
Query tables and fields metadata:
Get list of fields in all tables:
Query list of table names and IDs:
Find table name for a given table ID:
Query field type of a field:
Query list of record IDs for a given table:
Look for a control character in the text and return record IDs:
Update all values in a field based on a query:
Add container field by using BLOB as type:
Get all field names and IDs for a table:
Find newer records via RowID in Contacts table:
Use JOIN in SQL:
Delete all records in a table:
Queries base table name from an ID:
Queries base table ID from an name:
- Version 12.2
- Version 12.0
- Added FM.ExecuteFileSQLValue function.
- CURL/Batch Download
- CURL/Email/Batch Emailer
- CURL/Email/IMAP Email
- Mac and iOS/Events/Events Sync
- SQL in FileMaker/Custom Functions to Log Scriptcalls
- Third Party/FileMaker Snippet Storage
- Utility functions/Create Sort Script
- New records without layout change in FileMaker
- New in MBS FileMaker Plugin 12.0
- Sending email with a huge custom function
- Find new records with RowID
- Combined Components with MBS FileMaker Plugin
- Record Creation Script Trigger
- Custom Functions to Log Script Calls and maintain call stack
- Looping over records in FileMaker with error checking
- FileMaker Magazin Links
- Generate sort script automatically
This function checks for a license.
Created 18th August 2014, last changed 12nd February 2024