Re: Sum of Details on Master

Hi Nico -

A possible solution:

1.
Create a new column on your INVOICE table (e.g. inv_tot) to hold the denormalised total for all the invoice lines against the invoice. 2.
Create a database trigger e.g row level (PRE INSERT/UPDATE/DELETE) that corrolates the inv_tot with the total on your invoices (You could do the same functionality in a Form level procedure) when you commit row(s) in the invoice line block. - No other functionality is required to corrolate the 'total' i.e. ignore 4.5 'sum' non-database field creation. 3.
Create a corresponding database field to display the total (display, query only - so you can issue queries against the total) in your INVOICE block (named: inv_tot in this example) which will display the total, which will automatically be updated on INSERT/UPDATE/DELETE of rows in the invoice line block via the triggers (or procedures if you keep the functionality in the client application).

Above is one way to do the functionality you require but of course there are others. . .

Regards

• Martyn Cavett Senior ORACLE Analyst LGT Asset Management plc London, England. home: cavett_at_globalnet.co.uk

Nico Vandenbroucke <nbroucke_at_be.oracle.com> wrote in article <5hg8ea$f1p_at_inet-nntp-gw-1.us.oracle.com>...
> Any1 who knows how to handle this problem?
>
> The database holds two tables ('INVOICE' (master) and 'INVOICELINE'
 (detail)).
> Every Invoice line has an amount.
>
> We would like to keep the total amount of the invoice lines in the
 Invoice
> table.
>
> We've been thinking about some databasetriggers on Invoice line,
> but how can we handle this in Forms 4.5 ?
>
> We know that designer/2000 allows the generation of a sum field, but I
 believe
> the generator then generates a non-database field.
>
> How is forms than handling the sum field? (We can't accept a query with
> 'QUERY_ALL' option and we would like to query on that sum field).
>
> If we make it a database field in forms then I think there can be a
> collision with the database triggers!
>
> thanks in advance
>
Received on Sun Mar 30 1997 - 00:00:00 CST