Topics   All   Mac OS X (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Guides   Examples
New in version: 8.2   8.3   8.4   8.5   9.0   9.1   9.2   9.3   9.4   9.5    Statistic  

FM.InsertRecord

Inserts a new record in a table in one line.

Component Version macOS Windows Server FileMaker Cloud FileMaker iOS SDK
FM FMSQL 5.1 Yes Yes Yes Yes Yes
MBS( "FM.InsertRecord"; FileName; TableName; FieldName; FieldValue )   More

Parameters

Parameter Description Example value
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. "Assets"
FieldName A field name to set. "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).
Than 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.

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

See also

Example Databases

Blog Entries

Release notes

  • 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 7.2
    • Added possibility to specify field and tables by ID for FM.InsertRecord and similar functions.

Created 24th April 2015, last changed 19th September 2019


FM.InsertOrUpdateRecordQuery   -   FM.InsertRecordCSV

Feedback: Report problem or ask question.




Links
MBS FileMaker blog