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:
12.1
12.2
12.3
12.4
12.5
13.0
13.1
13.2
13.3
13.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 |
This function was deprecated. Use FM.ExecuteFileSQL instead.
Parameter | Description | Example | Flags |
---|---|---|---|
SQL Statement | SQL Statement as a Text string | ||
Column Delimiter | 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). |
"" | Optional |
Record Delimiter | Record Separator, only used with SELECT statement. Default is return character, which has unicode code point 13. You can pass delimiter as text (one character only) or as unicode code point (as number). |
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")
This function checks for a license.
Created 18th August 2014, last changed 14th April 2023