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.