FM.InsertRecord
---------------

Inserts a new record in a table in one line.

| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
|---|---|---|---|---|---|---|
| [FM](component_FM.md) [FMSQL](component_FMSQL.md) | [5.1](newinversion51.md) | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |

MBS( "FM.InsertRecord"; FileName; TableName; FieldName...; FieldValue... ) 

**MBS**( **"FM.InsertRecord";** /\* Inserts a new record in a table in one line. \*/  
**$FileName**; /\* The file name of where the table is inside. Can be empty to look for the table in all files.  
  
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.e.g. 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.e.g. "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.e.g. "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.e.g. "Test" \*/ 

### 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.      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. | 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" |

### 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](FMUpdateRecord.md) to update a record and [FM.InsertOrUpdateRecord](FMInsertOrUpdateRecord.md) 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](FMExecuteSQLLastSQL.md) function to see later what SQL was created and run.  
The [SQL](component_SQL.md) functions in FileMaker do not trigger OnWindowsTransaction script trigger.  
  
Does not take part in transactions in FileMaker. Changes are made directly to the database, so you can't revert them later with Revert Transaction script step.  
  
Please consider using newer [JSON.InsertRecord](JSONInsertRecord.md) and [JSON.InsertRecords](JSONInsertRecords.md) functions.  
  
This function takes variable number of parameters. Pass as much parameters as needed separated by the semicolon in FileMaker.  
Please repeat FieldName and FieldValue parameters as often as you need.  
### 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 &amp; "; $P \[" &amp; $ParamIndex &amp; "\]" \]   
\#   
Set Variable \[ $ParamIndex ; Value: $ParamIndex + 1 \]   
Set Variable \[ $P \[$ParamIndex \] ; Value: Get(LayoutTableName) \]   
Set Variable \[ $Params ; Value: $Params &amp; "; $P \[" &amp; $ParamIndex &amp; "\]" \]   
\#   
\# We loop over field 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 &amp; ": " &amp; $typ \]   
 Else  
 # We store in $P \[\] our parameters for evaluate and in $Params the parameters for Evaluate  
 Set Variable \[ $Types ; Value: $Types &amp; ¶ &amp; $FieldName &amp; ": " &amp; $Typ \]   
 Set Variable \[ $ParamIndex ; Value: $ParamIndex + 1 \]   
 Set Variable \[ $P \[$ParamIndex \] ; Value: $Fieldname \]   
 Set Variable \[ $Params ; Value: $Params &amp; "; $P \[" &amp; $ParamIndex &amp; "\]" \]   
 Set Variable \[ $ParamIndex ; Value: $ParamIndex + 1 \]   
 Set Variable \[ $P \[$ParamIndex \] ; Value: $Value \]   
 Set Variable \[ $Params ; Value: $Params &amp; "; $P \[" &amp; $ParamIndex &amp; "\]" \]   
 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 " &amp; $params &amp; ")" \]   
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 &gt; 0; MBS ( "[FM.ExecuteFileSQL](FMExecuteFileSQL.md)"; 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 &amp; ScriptName &amp; ¶;  
  
// 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](FMExecuteFileSQL.md)"; FileName; "UPDATE \\"ScriptCallLog\\" SET \\"ScriptResult\\" = ?, \\"EndTime\\" = ? WHERE \\"ScriptCallID\\" = ?"; 9; 13; GetAsText( Result ); Get(CurrentTimestamp); $ScriptCallID )  
  
\]; Result ); Result)  
Use an insert ID to get the primary key of a new records:

 Set Variable \[ $InsertID ; Value: Get(UUIDNumber) \]  
Set Variable \[ $r ; Value: MBS ("FM.InsertRecord"; Get(FileName); "Assets";   
 "Name"; "Test";   
 "Vendor"; "Company Ltd.";   
 "InsertID"; $InsertID ) \]  
Set Variable \[ $primaryKey ; Value: MBS ( "[FM.ExecuteFileSQLValue](FMExecuteFileSQLValue.md)"; Get(FileName); "SELECT \\"PrimaryKey\\" FROM \\"Assets\\" WHERE \\"InsertID\\" = ?"; $InsertID ) \]  
Show Custom Dialog \[ "New primary key: " &amp; $primaryKey \]  
### See also

- [FM.InsertOrUpdateRecord2](FMInsertOrUpdateRecord2.md)
- [FM.InsertOrUpdateRecord3](FMInsertOrUpdateRecord3.md)
- [FM.InsertRecordCSV](FMInsertRecordCSV.md)
- [FM.InsertRecordQuery](FMInsertRecordQuery.md)
- [FM.SetSQLBatchMode](FMSetSQLBatchMode.md)
- [FM.UpdateRecord](FMUpdateRecord.md)
- [JSON.InsertRecord](JSONInsertRecord.md)
- [JSON.InsertRecords](JSONInsertRecords.md)
- [SQL.InsertOrUpdateRecords](SQLInsertOrUpdateRecords.md)
- [SQL.InsertRecords](SQLInsertRecords.md)

### Release notes

- **Version 16.3**
    - Updated FM.InsertRecord to delay getting the field types until we have a field type mismatch.
- **Version 16.1**
    - Rewrote [FM.InsertRecordQuery](https://www.mbsplugins.eu/FMInsertRecordQuery.shtml) and [FM.InsertRecordQueryIgnoreDuplicates](https://www.mbsplugins.eu/FMInsertRecordQueryIgnoreDuplicates.shtml) to use newer code base.
- **Version 14.5**
    - Improved type detection for [FM.InsertRecordCSV](https://www.mbsplugins.eu/FMInsertRecordCSV.shtml) function.
- **Version 12.4**
    - Fixed a problem with [FM.InsertRecord](https://www.mbsplugins.eu/FMInsertRecord.shtml) and [FM.UpdateRecord](https://www.mbsplugins.eu/FMUpdateRecord.shtml) functions. They now ignore empty field names.
- **Version 11.2**
    - Changed [FM.InsertRecordCSV](https://www.mbsplugins.eu/FMInsertRecordCSV.shtml) and [FM.InsertRecordTSV](https://www.mbsplugins.eu/FMInsertRecordTSV.shtml) to recognize backslash escapes to have returns and tabs escaped in text.
- **Version 10.1**
    - Changed [FM.InsertRecord](https://www.mbsplugins.eu/FMInsertRecord.shtml), [FM.CompareTables](https://www.mbsplugins.eu/FMCompareTables.shtml), [FM.DeleteRecord](https://www.mbsplugins.eu/FMDeleteRecord.shtml), [FM.DeleteRecords](https://www.mbsplugins.eu/FMDeleteRecords.shtml), [FM.InsertOrUpdateRecord](https://www.mbsplugins.eu/FMInsertOrUpdateRecord.shtml) and [FM.UpdateRecord](https://www.mbsplugins.eu/FMUpdateRecord.shtml) to take table name from first field name if table name is empty.
- **Version 9.3**
    - Rewrote [FM.InsertRecordCSV](http://www.mbsplugins.eu/FMInsertRecordCSV.shtml) to support multi line values.
- **Version 9.2**
    - Fixed problem with delimiter detection in [FM.InsertRecordCSV](http://www.mbsplugins.eu/FMInsertRecordCSV.shtml).
- **Version 8.5**
    - Added delimiter parameter for [List.CSVSplit](http://www.mbsplugins.eu/ListCSVSplit.shtml), [QuickList.CSVSplit](http://www.mbsplugins.eu/QuickListCSVSplit.shtml) and [FM.InsertRecordCSV](http://www.mbsplugins.eu/FMInsertRecordCSV.shtml).

### Example Databases

- [MongoDB/MongoDB Audit](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/MongoDB/MongoDB%20Audit.shtml#4ScriptAnchor_)
- [SQL in FileMaker/Custom Functions to Log Scriptcalls](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20in%20FileMaker/Custom%20Functions%20to%20Log%20Scriptcalls.shtml#1CustomFunctionAnchor_)
- [SQL in FileMaker/Insert or Update Tests](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20in%20FileMaker/Insert%20or%20Update%20Tests.shtml#20ScriptAnchor_)
- [SQL in FileMaker/SQL Select Container](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20in%20FileMaker/SQL%20Select%20Container.shtml#3ScriptAnchor_)

### Blog Entries

- [MBS FileMaker Plugin, version 16.3pr3](https://www.mbsplugins.de/archive/2026-06-22/MBS_FileMaker_Plugin_version_1/monkeybreadsoftware_blog_filemaker)
- [SQL error checking improved](https://www.mbsplugins.de/archive/2026-04-30/SQL_error_checking_improved/monkeybreadsoftware_blog_filemaker)
- [Logging Activities in FileMaker with MBS FileMaker Plugin](https://www.mbsplugins.de/archive/2026-03-13/Logging_Activities_in_FileMake/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Advent calendar - Door 11 - Adding records](https://www.mbsplugins.de/archive/2025-12-11/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)
- [Insert or update with JSON](https://www.mbsplugins.de/archive/2024-08-28/Insert_or_update_with_JSON/monkeybreadsoftware_blog_filemaker)
- [Watch MongoDB Database](https://www.mbsplugins.de/archive/2023-11-04/Watch_MongoDB_Database/monkeybreadsoftware_blog_filemaker)
- [New records without layout change in FileMaker](https://www.mbsplugins.de/archive/2023-08-24/New_records_without_layout_cha/monkeybreadsoftware_blog_filemaker)
- [MBS @ FMTraining.TV](https://www.mbsplugins.de/archive/2022-08-29/MBS_@_FMTrainingTV/monkeybreadsoftware_blog_filemaker)
- [Sending email with a huge custom function](https://www.mbsplugins.de/archive/2021-06-26/Sending_email_with_a_huge_cust/monkeybreadsoftware_blog_filemaker)
- [Combined Components with MBS FileMaker Plugin](https://www.mbsplugins.de/archive/2021-03-29/Combined_Components_with_MBS_F/monkeybreadsoftware_blog_filemaker)

### FileMaker Magazin

- [Ausgabe 1/2021, Seite 29 bis 30](https://filemaker-magazin.de/neuigkeit/4124-Appetithappen-FMM_202101)
- [Ausgabe 6/2019, Seite 33](https://filemaker-magazin.de/neuigkeit/4064-Appetithappen-FMM_201906)
- [Ausgabe 4/2019, Seite 27](https://filemaker-magazin.de/neuigkeit/4053-Appetithappen-FMM_201904)
- [Ausgabe 6/2018, Seite 26 bis 27](https://filemaker-magazin.de/neuigkeit/4025-Appetithappen-FMM_201806)
- [Ausgabe 6/2018, Seite 22 bis 23](https://filemaker-magazin.de/neuigkeit/4025-Appetithappen-FMM_201806)
- [Ausgabe 5/2018, Seite 25](https://filemaker-magazin.de/neuigkeit/4011-Appetithappen-FMM_201805)
- [Ausgabe 3/2018, Seite 34 bis 36](https://filemaker-magazin.de/neuigkeit/3953-Appetithappen-FMM_201803)
- [Ausgabe 3/2015, Seite 40 bis 41](https://filemaker-magazin.de/neuigkeit/3810-Appetithappen-FMM_201503)

This function checks for a license.

Created 24th April 2015 , last changed 10th July 2025

  
[FM.InsertOrUpdateRecordQuery](FMInsertOrUpdateRecordQuery.md) - [FM.InsertRecordCSV](FMInsertRecordCSV.md)

[HTML Version](FMInsertRecord.shtml)