Ask Your Question

base: insert from one table into other table [closed]

asked 2012-11-14 16:57:46 +0200

Jaypee gravatar image

updated 2012-11-19 15:54:58 +0200

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-21 12:29:53.315693


Hi @Jaypee,

Still looking for an answer to your question?

qubit gravatar imagequbit ( 2013-03-11 11:25:43 +0200 )edit

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

Jaypee gravatar imageJaypee ( 2013-03-11 17:46:45 +0200 )edit

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.

黄智壯 gravatar image黄智壯 ( 2014-04-10 07:24:21 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2014-04-26 18:10:45 +0200

Alex Thurgood gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2012-11-14 16:57:46 +0200

Seen: 1,183 times

Last updated: Apr 26 '14