Base: insert from one table into other table

I want to insert data from one table into an other while doing some math with it. What is the best way to do this?

I got 4 tables: “invoice”, “invoiceDetails”, “sales” and “products”

“invoiceDetails” is linked to the “products” sold (1 “id” on 1 “p” relation)
“invoice” is linked to “invoiceDetails” (1 “i” on many “id” relation)

I already have a nice report delivering a pretty invoice through sql and report functionality.
Now however i want to insert the “invoice” totals derived from “invoiceDetails” and “products” which are only calculated in the report into the “sales” table. What is the best way to do this? Using SQL of course (INSERT…INTO…) but how and when should I run this SQL?

project file is available
https://docs.google.com/open?id=0BzXci31E7oR8OXNMR3lsNDRxcnc

Hi @Jaypee,

Still looking for an answer to your question?

Yes, I do. I haven’t worked on it though.

From my understanding, you want to insert invoice totals into the invoice tables. The short answer to ‘when’ to update the total is: whenever you make a change to either invoice or invoiceDetails tables.

That said, why not stick with the current situation where a report sums the total on the fly? It will mean less messing about with business logic, and reduce the likelihood of an embarrassing invoice total error.

If you really want to insert those values you either need to use a trigger to UPDATE the sales table or do it manually. Embedded hsqldb 1.8 which comes with LO doesn’t support triggers defined via DDL as far as I know, you would have to use a Java function to do that and find some way to run it. Another alternative might be via a Basic macro with a prepared statement constructed from variables that correspond to the values of your respective invoice and invoiceDetails tables and that is executed upon a given event.