Monkeybread Software - MBS FileMaker Plugin: FM.InsertOrUpdateRecordQuery

Components	All	New	MacOS		Windows		Linux		iOS
Examples	Mac & Win		Server	Client	Guides	Statistic	FMM	Blog	Deprecated	Old

FM.InsertOrUpdateRecordQuery

Insert or updates a lot of records queried from a database.

Component	Version	macOS	Windows	Linux	Server	iOS SDK
FM FMSQL	8.4	✅ Yes	✅ Yes	✅ Yes	✅ Yes	✅ Yes

MBS( "FM.InsertOrUpdateRecordQuery"; InsertFileName; InsertTableName; FieldNames; KeyFieldMapping; QueryFileName; SQL Statement { ; Params... } ) More

Parameters

Parameter Description Example Flags

InsertFileName The file name of where the target table.
Can be empty to look for the table in all files. Get(FileName)

InsertTableName The name of the table to insert record into (target).
Can be ID of table, so we lookup name by ID.
Can be result of GetFieldName() function as we remove field name automatically. "Assets"

FieldNames A list of field names for the insert.
Empty entries in the list are ignored.

Can be ID of field, so we lookup name by ID.
Can be result of GetFieldName() function as we remove table name automatically. "Model"

KeyFieldMapping The list of field names with index to map keys.
Please specify a list of key name=key index.
The key name is the name of the field in the insert table and the index gives zero-based position in result set from SELECT query in 6th parameter.

Field order in source and destination does not need to be the same. ID=0

QueryFileName The file name for the database to run the query against (source).
Can be empty to look for the table in all files. Get(FileName)

SQL Statement SQL Statement as a Text string to query records from the source table.

Params... Pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement. 123 Optional

Parameter	Description	Example	Flags
InsertFileName	The file name of where the target table. Can be empty to look for the table in all files.	Get(FileName)
InsertTableName	The name of the table to insert record into (target). Can be ID of table, so we lookup name by ID. Can be result of GetFieldName() function as we remove field name automatically.	"Assets"
FieldNames	A list of field names for the insert. Empty entries in the list are ignored. Can be ID of field, so we lookup name by ID. Can be result of GetFieldName() function as we remove table name automatically.	"Model"
KeyFieldMapping	The list of field names with index to map keys. Please specify a list of key name=key index. The key name is the name of the field in the insert table and the index gives zero-based position in result set from SELECT query in 6th parameter. Field order in source and destination does not need to be the same.	ID=0
QueryFileName	The file name for the database to run the query against (source). Can be empty to look for the table in all files.	Get(FileName)
SQL Statement	SQL Statement as a Text string to query records from the source table.
Params...	Pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement.	123	Optional

Result

Returns OK or error.

Description

Insert or updates a lot of records queried from a database.
This function allows to easily copy a lot of records from one table to another table.
Copy can be from one database to other or within same database. Also from one table to other or within a table. Due to passing in new field names, you can even rearrange values from one column to other while copying. In the SQL you can use functions for sums or join data from several tables together to fill a new table. Filename parameters can be empty.
Use FM.ExecuteSQL.LastErrorMessage and FM.ExecuteSQL.LastError to see error message.
Reports an error if field name list doesn't match column count of the query result.

Please remove from SQL statement and field names all fields which are globals or unstored calculations.

For inserting records from other database (e.g. MySQL), please use SQL.InsertRecords function.

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 GetFieldName() function to query field names and the plugin removes the table prefix with :: in the field name before passing it to SQL engine.

Please don't overload the function, so better work in blocks of e.g. 1000 rows at a time.

This function takes variable number of parameters. Pass as much parameters as needed separated by the semicolon in FileMaker.
Please repeat Params parameter as often as you need.

Examples

Copies records from Contacts to ContactsCopy:

Set Variable [ $r ; Value: MBS( "FM.InsertOrUpdateRecordQuery"; ""; "ContactsCopy"; "ContactsCopyID¶First¶Last¶Mobile"; "ContactsCopyID=0"; ""; "SELECT \"ContactsID\", \"First\", \"Last\", \"Mobile\" FROM \"Contacts\"" ) ]

Copy values from Assets sample database:

// Source is English version of Assets sample database in FileMaker Pro
// Destination is German version named Anlagen with

MBS( "FM.InsertOrUpdateRecordQuery";
// destination file name
"Anlagen";
// destination table name
"Anlagen";
// destination field names
"Name¶Beschreibung¶Seriennummer¶Bild¶Preis¶Anbieter¶Kaufdatum¶Typ¶Primärschlüssel¶ErstelltVon¶GeändertVon";
// what is primary key in destination table
"Primärschlüssel=0";
// source file
"Assets";
// SELECT statement to query values
"SELECT \"Name\", \"Description\", \"Serial Number\", \"Image\", \"Price\", \"Vendor\", \"Purchase Date\", \"Type\", \"PrimaryKey\", \"CreatedBy\", \"ModifiedBy\" FROM \"Assets\" " )

FM.InsertOrUpdateRecordQuery

Parameters

Result

Description

Examples

See also