Quickly copy lots of records
We got a new idea on how to copy a lot of records from one FileMaker table to another. And those tables can be in different fields. We can preserve the data type, so everything is not converted to text while copying. And we can do modifications like have source and dest use different field names. Finally you can include calculations and join data from several tables.How we do it?
We combine a SQL select query with insert commands. So we can make a query and use result to create new records.
For that we got two new functions for next plugin version: FM.InsertRecord and FM.InsertRecordQuery.
Now you can call it like this:
MBS( "FM.InsertRecordQuery"; ""; "ExportContacts"; "First¶Last¶Company"; ""; "SELECT \"First\", \"Last\", \"Company\" FROM Contacts" )
This will query the fields First, Last and Company from a table called Company in current file. Than it will insert those as new records into the table ExportContacts.
Another example here:
MBS( "FM.InsertRecordQuery"; ""; "SalesReport"; "SumSales¶CustomerID"; ""; "SELECT sum(\"InvoiceTotal\"), \"CustomerID\" FROM Contacts, Invoices GROUP by CustomerID" )
This does create records in table SalesReport. For the query we use SQL features like grouping by CustomerID and summing up the InvoiceTotal fields. This way we can easily collect data for a sales report into a new table. This table can than feed a chart or be exported.
What do you think? Is this useful? Will be included in 5.1pr7 soon. Or just email for a copy if you like to try.
