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:
9.4
9.5
10.0
10.1
10.2
10.3
10.4
10.5
11.0
11.1
Statistic
FMM
Blog
FM.InsertRecord
Inserts a new record in a table in one line.
Component | Version | macOS | Windows | Linux | Server | FileMaker iOS SDK |
FM FMSQL | 5.1 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameters
Parameter | Description | Example |
---|---|---|
FileName | The file name of where the table is inside. Can be empty to look for the table in all files. | Get(FileName) |
TableName | The name of the table to insert record into. Can be ID of table, so we lookup name by ID. Can be result of GetFieldName() function as we remove field name automatically. |
"Assets" |
FieldName | A field name to set. Can be ID of field, so we lookup name by ID. Can be result of GetFieldName() function as we remove table name automatically. |
"Model" |
FieldValue | A field value to use for setting the field in the parameter before. Data type of parameter must match the data type of the field. |
"Test" |
... | More pairs of field names and values follow. |
Result
Returns OK or error.
Description
Inserts a new record in a table in one line.You pass to this function table name (and optional filename).
Then you pass one pair of field name and value for each field you like to set in the new record.
As we can't know the new ID assigned for the record, you can help yourself by passing in an UUID for a field and later select that to get the auto assigned primary key (or have the UUID be the primary key, so you don't need a query).
See also FM.UpdateRecord to insert new record and FM.InsertOrUpdateRecord to update or insert record.
With plugin version 7.2 or later, you can specify fields and table via IDs and the plugin will lookup them for you at runtime. Table IDs and table names must be unique across all open files for this to work correctly.
You can use FM.ExecuteSQL.LastSQL function to see later what SQL was created and run.
Examples
Insert a record into Assets table:
MBS("FM.InsertRecord"; ""; "Assets"; "Model"; "TestModell"; "Item"; "MyItem"; "Serial Number"; "1234"; "In Service Date"; GetAsDate( "31.05.1996"))
Insert a record with 5 fields:
MBS("FM.InsertRecord"; $filename; $tablename; $field1; $value1; $field2; $value2; $field3; $value3; $field4; $value4; $field5; $value5)
Insert with table and field ID instead of names:
Set Variable [ $r ; Value: MBS("FM.InsertRecord"; Get(FileName); "1065089"; "3"; "Hello") ]
Insert with using GetFieldName for field names:
MBS( "FM.InsertRecord"; ""; "Contacts";
GetFieldName ( Contacts::First ); "Joe";
GetFieldName ( Contacts::Last ); "Miller";
GetFieldName ( Contacts::Group ); "Best Customers" )
Build dynamically the evaluate statement with parameters:
# Our input parameters with field name list
Set Variable [ $FieldNames ; Value: FieldNames ( Get(FileName) ; Get(LayoutName) ) ]
#
# We build parameters for our Evaluate call
Set Variable [ $Params ; Value: "" ]
Set Variable [ $ParamIndex ; Value: 0 ]
#
Set Variable [ $ParamIndex ; Value: $ParamIndex + 1 ]
Set Variable [ $P[$ParamIndex] ; Value: Get(FileName) ]
Set Variable [ $Params ; Value: $Params & "; $P[" & $ParamIndex & "]" ]
#
Set Variable [ $ParamIndex ; Value: $ParamIndex + 1 ]
Set Variable [ $P[$ParamIndex] ; Value: Get(LayoutTableName) ]
Set Variable [ $Params ; Value: $Params & "; $P[" & $ParamIndex & "]" ]
#
# We loop over feidl list
Set Variable [ $Count ; Value: ValueCount ( $FieldNames ) ]
Set Variable [ $FieldIndex ; Value: 0 ]
Set Variable [ $Types ; Value: "" ]
#
Loop
# get field name and value
Set Variable [ $FieldIndex ; Value: $FieldIndex + 1 ]
Set Variable [ $Fieldname ; Value: GetValue($FieldNames; $FieldIndex) ]
Set Variable [ $Value ; Value: GetField ( $Fieldname) ]
#
# Check typ to filter fields we don't want
Set Variable [ $Typ ; Value: FieldType ( Get(FileName) ; $Fieldname ) ]
If [ Position ( $Typ; "Global"; 1; 1 ) ≥ 1 or Position ( $Typ; "Summary"; 1; 1 ) ≥ 1 or Position($Typ; "storedCalc"; 1; 1) ≥ 1 or $Fieldname = "ID" ]
# ignore global, statistic and unsaved formula field
// Show Custom Dialog [ "Typ" ; $FieldName & ": " & $typ ]
Else
# We store in $P[] our parameters for evaluate and in $Params the parameters for Evaluate
Set Variable [ $Types ; Value: $Types & ¶ & $FieldName & ": " & $Typ ]
Set Variable [ $ParamIndex ; Value: $ParamIndex + 1 ]
Set Variable [ $P[$ParamIndex] ; Value: $Fieldname ]
Set Variable [ $Params ; Value: $Params & "; $P[" & $ParamIndex & "]" ]
Set Variable [ $ParamIndex ; Value: $ParamIndex + 1 ]
Set Variable [ $P[$ParamIndex] ; Value: $Value ]
Set Variable [ $Params ; Value: $Params & "; $P[" & $ParamIndex & "]" ]
End If
#
Exit Loop If [ $FieldIndex = $count ]
End Loop
#
# now build Evaluate command and show it
// Show Custom Dialog [ "Types" ; $Types ]
Set Variable [ $Function ; Value: "FM.InsertRecord" ]
Set Variable [ $command ; Value: "MBS($Function" & $params & ")" ]
Show Custom Dialog [ "Command" ; $Command ]
If [ Get ( LastMessageChoice ) = 1 ]
# And run it!
Set Variable [ $result ; Value: Evaluate($Command) ]
Show Custom Dialog [ "Result" ; $Result ]
End If
Insert with automatic table name:
MBS("FM.InsertRecord";
""; // empty for current file
""; // empty as given with first field
GetFieldName ( Anlagen::Seriennummer ); // field name queried
"12345") // and value
CountScriptCall custom function:
If($$LogScriptCalls;
Let ( [
FileName = Get(FileName);
ScriptName = Get(ScriptName);
// try to insert new record
r = MBS( "FM.InsertRecord"; FileName; "ScriptCallCounter"; "Counter"; 1; "ScriptName"; ScriptName; "FileName"; FileName );
// r shows OK or "[MBS] ERROR: (504): Field failed unique value validation test" in case of duplicate
// check for error: 504
p = Position ( r ; "(504)" ; 1 ; 1 );
// update existing call on error
r = If( p > 0; MBS( "FM.ExecuteFileSQL"; FileName; "UPDATE \"ScriptCallCounter\" SET \"Counter\" = \"Counter\" + 1 WHERE \"ScriptName\" = ? AND \"FileName\" = ?"; 9; 13; ScriptName; FileName ); r)
]; r ); "")
LogFunctionCall custom function:
If($$LogScriptCalls; Let ( [
// query some values about current context
FileName = Get(FileName);
ScriptName = Get(ScriptName);
TableName = Get(LayoutTableName);
AccountName = Get(AccountName);
LayoutName = Get(LayoutName);
UserName = Get(UserName);
IP = Get(SystemIPAddress);
ScriptParameter = Get(ScriptParameter);
// make an unique key for later
$ScriptCallID = Get(UUID);
// and build call stack
$$CallStack = $$CallStack & ScriptName & ¶;
// now log it.
r = MBS( "FM.InsertRecord"; FileName; "ScriptCallLog";
"ScriptCallID"; $ScriptCallID;
"ScriptName"; ScriptName;
"FileName"; FileName;
"TableName"; TableName;
"LayoutName"; LayoutName;
"AccountName"; AccountName;
"UserName"; UserName;
"IP"; IP;
"ScriptParameter"; GetAsText(ScriptParameter);
"CallStack"; $$CallStack;
"StartTime"; Get ( CurrentTimestamp ))
]; r ); "")
LogFunctionResult custom function:
If( not IsEmpty($ScriptCallID); Let ( [
// remove us from call stack
$$CallStack = LeftValues ( $$CallStack ; ValueCount ( $$CallStack ) - 1 );
// now updat entry in log to show result.
FileName = Get(FileName);
r = MBS( "FM.ExecuteFileSQL"; FileName; "UPDATE \"ScriptCallLog\" SET \"ScriptResult\" = ?, \"EndTime\" = ? WHERE \"ScriptCallID\" = ?"; 9; 13; GetAsText( Result ); Get(CurrentTimestamp); $ScriptCallID )
]; Result ); Result)
See also
- FM.ExecuteSQL.LastErrorMessage
- FM.ExecuteSQL.LastSQL
- FM.GetSQLBatchMode
- FM.InsertOrUpdateRecord2
- FM.InsertOrUpdateRecord3
- FM.InsertRecordQuery
- FM.InsertRecordTSV
- FM.InsertSetUpdateProgressDialog
- FM.UpdateRecord
- SQL.InsertOrUpdateRecords
Example Databases
Blog Entries
- Custom Functions to Log Script Calls and maintain call stack
- Looping over records in FileMaker with error checking
- MBS FileMaker Plugin, version 10.1pr1
- Dynamically build MBS call and evaluate it
- MBS FileMaker Plugin, version 8.4pr2
- MBS FileMaker Plugin, version 8.4pr1
- MBS FileMaker Plugin, version 7.2pr4
- MBS FileMaker Plugin, version 6.2pr2
- MBS FileMaker Plugin 5.1 for OS X/Windows
- MBS FileMaker Plugin, version 5.1pr7
FileMaker Magazin
- Ausgabe 1/2021, Seite 30
- Ausgabe 1/2021, Seite 29
- Ausgabe 6/2019, Seite 33
- Ausgabe 4/2019, Seite 27
- Ausgabe 6/2018, Seite 27
- Ausgabe 6/2018, Seite 26
- Ausgabe 6/2018, Seite 23
- Ausgabe 6/2018, Seite 22
- Ausgabe 5/2018, Seite 25, PDF
- Ausgabe 3/2018, Seite 36, PDF
Release notes
- Version 10.1
- Changed FM.InsertRecord, FM.CompareTables, FM.DeleteRecord, FM.DeleteRecords, FM.InsertOrUpdateRecord and FM.UpdateRecord to take table name from first field name if table name is empty.
- Version 9.3
- Rewrote FM.InsertRecordCSV to support multi line values.
- Version 9.2
- Fixed problem with delimiter detection in FM.InsertRecordCSV.
- Version 8.5
- Added delimiter parameter for List.CSVSplit, QuickList.CSVSplit and FM.InsertRecordCSV.
- Version 8.4
- Changed FM.InsertRecord and other SQL based functions to process field names and remove field name postfix with :: in table names and remove table prefix with :: in Field names.
- Version 8.2
- Improved FM.InsertRecordCSV and FM.InsertRecordTSV to handle date/time/timestamp fields.
- Version 8.0
- Changed FM.InsertRecordCSV and FM.InsertRecordTSV to detect whether columns are numbers instead of text and handle it correctly.
- Version 7.4
- Added CSV functions: FM.InsertRecordCSV, List.CSVSplit and QuickList.CSVSplit.
- Version 7.2
- Added possibility to specify field and tables by ID for FM.InsertRecord and similar functions.
Created 24th April 2015, last changed 24th July 2020
FM.InsertOrUpdateRecordQuery - FM.InsertRecordCSV
Feedback: Report problem or ask question.

Links
MBS Xojo PDF Plugins