Topics
All
MacOS
(Only)
Windows
(Only)
Linux
(Only, Not)
iOS
(Only, Not)
Components
Crossplatform Mac & Win
Server
Client
Old
Deprecated
Guides
Examples
Videos
New in version:
10.1
10.2
10.3
10.4
10.5
11.0
11.1
11.2
11.3
11.4
Statistic
FMM
Blog
Execute the SQL Statement against the current FileMaker File.
Component | Version | macOS | Windows | Linux | Server | iOS SDK |
FM FMSQL | 1.2 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameter | Description | Example | Flags |
---|---|---|---|
SQL Statement | SQL Statement as a Text string | ||
Column Delimiter | Columns Separator, only used with SELECT statement. Default is TAB | "" | Optional |
Record Delimiter | Record Separator, only used with SELECT statement, Default is Return | Optional |
Returns result of SQL expression or error.
SELECT records
MBS("FM.ExecuteSQL";"select * from Contacts")
INSERT record
MBS("FM.ExecuteSQL";"INSERT INTO Contacts (FirstName, LastName) Values('Bob', 'Miller')")
Select records using delimiters
MBS("FM.ExecuteSQL";"select * from Contacts"; "#"; "|")
SELECT records with a Where clause
MBS("FM.ExecuteSQL"; "SELECT First_Name, Last_Name from Contacts WHERE ID_Contact_PK ='CN10013'")
Get list of fields in all tables:
MBS("FM.ExecuteSQL";"SELECT * FROM FileMaker_Fields")
Create Record with values via SQL
Set Variable [$r; Value:MBS( "FM.ExecuteSQL"; "INSERT INTO \"Export records\" (\"First Name\", \"Last Name\", \"Street\", \"Zip\", \"City\", \"Country\", \"Phone\") VALUES ('MyFirst', 'MyLast', 'MyStreet', 'MyZip', 'MyCity', 'MyCountry', 'MyPhone')")]
If [MBS("IsError")]
Show Custom Dialog [MBS( "FM.ExecuteSQL.LastErrorMessage" )]
End If
Set value for all records of a table:
MBS( "FM.ExecuteSQL"; "UPDATE \"My Table\" SET \"My Field\" = 'Hello' ")
Set timestamp to today:
MBS( "FM.ExecuteSQL"; "UPDATE \"my Table\" SET \"Change Time\" = TODAY WHERE \"ID\" = '12345' ")
Update 5 fields:
MBS( "FM.ExecuteSQL"; "UPDATE \"my Table\" SET \"Number Field" = 1, \"Text Field" = 'test', \"Dest Field\" = \"Source Field\", \"Hour Field\" = CAST(HOUR(curtime) AS VARCHAR), \"Empty FIeld\" = NULL WHERE \"ID\" = '12345' ")
Select with passing variable as part of expression:
MBS("FM.ExecuteSQL"; "SELECT First_Name, Last_Name from Contacts WHERE ID_Contact_PK ='" & $Variable & "'")
Update date field:
MBS( "FM.ExecuteSQL"; "UPDATE myTable SET myDateField = CAST('2015-08-15' as DATE) " )
Delete all data in a table:
$Result = MBS( "FM.ExecuteSQL"; "DELETE FROM \"Prova\";")
Create an index:
MBS( "FM.ExecuteSQL"; "CREATE INDEX ON tablename.fieldname" )
Drop an index:
MBS( "FM.ExecuteSQL"; "DROP INDEX ON tablename.fieldname" )
Value in List of values:
MBS( "FM.ExecuteSQL"; "select * from \"Anlagen\" where \"Modell\" in ('test', 'abc')")
Delete all records from Table People:
# no where condition, so deletes all records!
MBS( "FM.ExecuteSQL"; "DELETE FROM \"People\"")
Query value of a repeating field with []:
MBS( "FM.ExecuteSQL"; "SELECT test[2] FROM Kontakte")
Set value of repeating field:
MBS( "FM.ExecuteSQL"; "UPDATE Kontakte SET test[2] = 'test'")
Query only first row:
MBS( "FM.ExecuteSQL"; "SELECT * FROM ProductImages FETCH FIRST 1 ROWS ONLY")
Truncates table in FileMaker 15 or newer:
MBS( "FM.ExecuteSQL"; "TRUNCATE TABLE Kontakte")
Query field ID for a field with given name and table name:
MBS("FM.ExecuteSQL"; "SELECT FieldID FROM FileMaker_Fields WHERE FieldName='Vorname' and TableName='Kontakte' ")
Query repetition count of global field by name:
MBS("FM.ExecuteSQL"; "SELECT FieldReps FROM FILEMAKER_FIELDS WHERE FieldName='Wiederholen' ")
Query field name and repetition count for all fields in a table:
MBS("FM.ExecuteSQL"; "SELECT FieldReps, FieldName FROM FILEMAKER_FIELDS WHERE TableName='Aufgaben' AND FieldType like '%global%' ")
Query list of record IDs for a given table:
MBS("FM.ExecuteSQL"; "SELECT ROWID FROM MyTable")
Concat two texts with plus and use CAST to make number to text:
MBS( "FM.ExecuteSQL"; "SELECT 'A' + CAST(123 AS VARCHAR) FROM Contacts")
Created 18th August 2014, last changed 6th October 2020
FM.ExecuteFileSQLOnIdle - FM.ExecuteSQL.LastError
Feedback: Report problem or ask question.