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.2
12.3
12.4
12.5
13.0
13.1
13.2
13.3
13.4
13.5
Statistic
FMM
Blog
Inserts a lot of records queried from a database.
Component | Version | macOS | Windows | Linux | Server | iOS SDK |
FM FMSQL | 5.1 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameter | Description | Example | Flags |
---|---|---|---|
InsertFileName | The file name of where the insert table is inside. Can be empty to look for the table in all files. | Get(FileName) | |
InsertTableName | 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" | |
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" | |
QueryFileName | The file name for the database to run the query against. Can be empty to look for the table in all files. | Get(FileName) | |
SQL Statement | SQL Statement as a Text string | ||
Params... | Pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement. |
123 | Optional |
Returns OK or error.
Copy records within one table in same file:
MBS( "FM.InsertRecordQuery"; ""; "Contacts"; "First¶Last¶Company"; ""; "SELECT \"First\", \"Last\", \"Company\" FROM Contacts" )
Copy records from a table from one database to other (production to development)
MBS( "FM.InsertRecordQuery"; "ContactsDev.fmp12"; "Contacts"; "First¶Last¶Company"; "Contacts.fmp12"; "SELECT \"First\", \"Last\", \"Company\" FROM Contacts" )
Copy data to other table:
MBS( "FM.InsertRecordQuery"; ""; "Test"; "FirstName¶LastName¶CompanyName"; ""; "SELECT \"First\", \"Last\", \"Company\" FROM Contacts" )
Query sum of sales grouped by customers in a sales report table:
MBS( "FM.InsertRecordQuery"; ""; "SalesReport"; "SumSales¶CustomerID"; ""; "SELECT sum(\"InvoiceTotal\"), \"CustomerID\" FROM Clients, Invoices WHERE Invoices. CustomerID = Clients.CustomerID GROUP by CustomerID" )
Copy two fields from one table to other:
MBS( "FM.InsertRecordQuery"; ""; "ASC Apple Script"; "Name¶Script"; ""; "select * from \"AppleScript\"")
Copy from Anlagen example (in German) to copy of same file:
MBS( "FM.InsertRecordQuery";
/* InsertFileName */
"Anlagen2";
/* InsertTableName */
"Anlagen";
/* FieldNames */
"Abschreibung Jahre¶Anzahl Diagramme¶Ausgewählte Datei¶Datei 1 Container¶Datei 2 Container¶Datei 3 Container¶Datei 4 Container¶Datei 5 Container¶Datum Ausgang¶Datum Ausgang Popover¶Datum Eingang¶Erstellungsdatum¶Fälligkeit¶Fälligkeit Popover¶Gekauft von¶Geprüft durch¶In Betrieb seit¶Interne ID¶Kategorie¶Kaufdatum¶Kosten¶Modell¶Notizen¶Ort¶Position¶Prüfdatum¶Seriennummer¶Signatur Container¶Zugewiesen an¶Zustand";
/* QueryFileName */
"Anlagen";
/* SQL Statement */
"SELECT \"Abschreibung Jahre\", \"Anzahl Diagramme\", \"Ausgewählte Datei\", \"Datei 1 Container\", \"Datei 2 Container\", \"Datei 3 Container\", \"Datei 4 Container\", \"Datei 5 Container\", \"Datum Ausgang\", \"Datum Ausgang Popover\", \"Datum Eingang\", \"Erstellungsdatum\", \"Fälligkeit\", \"Fälligkeit Popover\", \"Gekauft von\", \"Geprüft durch\", \"In Betrieb seit\", \"Interne ID\", \"Kategorie\", \"Kaufdatum\", \"Kosten\", \"Modell\", \"Notizen\", \"Ort\", \"Position\", \"Prüfdatum\", \"Seriennummer\", \"Signatur Container\", \"Zugewiesen an\", \"Zustand\" FROM Anlagen"
)
Copy data to other table with parameter:
MBS( "FM.InsertRecordQuery"; ""; "Test"; "FirstName¶LastName¶CompanyName"; ""; "SELECT \"First\", \"Last\", \"Company\" FROM Contacts WHERE City=?"; "Hamburg" )
Copy from file to fill clone with order and two fields to filter:
MBS( "FM.InsertRecordQuery"; "Aufgaben Clone"; "Aufgaben"; "Aufgabe¶Fälligkeit¶Beschreibung¶Status¶Kategorie"; ""; "SELECT Aufgabe, \"Fälligkeit\", Beschreibung, Status, Kategorie FROM Aufgaben WHERE IDField=? AND OtherThing=? ORDER BY \"Fälligkeit\" "; 123; "Haus" )
Import records and add Import ID field with constant value:
MBS( "FM.InsertRecordQuery"; "Import.fmp12"; "ImportContacts"; "First¶Last¶Company¶ImportID"; "Source.fmp12"; "SELECT \"First\", \"Last\", \"Company\", '123' FROM Contacts" )
This function checks for a license.
Created 29th April 2015, last changed 29th January 2023