Re: Forms: Update master block with running total from detail

Yosi,

You can keep TOTAL up-to-date with 3 triggers, Pre-Record and When-Validate-Record on the LINEITEM block, and a Key-Delete. In the Pre-Record save the Amount, and then in the When-Validate-Record compare the current and saved values, and apply the difference to TOTAL. When-Validate-Record will fire when you navigate from an inserted or changed record, so it's a better choice than Post-Record which fires a lot more often.
In the key-delete trigger ensure the Cursor Block is LINEITEM and then subtract the saved Amount from TOTAL.

This leaves the problem of rollback. A simple approach is to ensure the master and detail blocks are kept in sync by preventing key-clrblk on the LINEITEM block, ie. create a LINEITEM key-clrblk trigger with Trigger Text = null;

Yosi Greenfield wrote in message <382989C4.6E659963_at_compuserve.com>...
>All,
>
>This must be a common situation, and I've been trying to deal with it in
>my form. (I've been a dba for a long time...) If you can bear with the
>length of this post, thanks very much.
>
>Given the standard Oracle ORDER and LINEITEM table, where ORDER contains
>the order heading information and subtotals and totals, and LINEITEM
>contains the items in the order.
>
>If ORDER has a TOTAL column in it, and I display that field on my form
>master block, how do I update that field as I enter/delete/update lines
>into the LINEITEM detail block?
>
>The question raises a few issues:
>
>I could simply write a database before insert/update/delete trigger to
>update the ORDER.TOTAL field behind the scenes. That would keep the
>database in sync but won't update my form. On the other hand, if I
>update the form field using Forms triggers, there's a whole range of
>situations I have to handle to keep it in sync.
>
>My first thoughts have been to use separate triggers to keep the field
>up to date: a key-delete to subtract the linetot from the order total, a
>post_record that checks for record_status='INSERT' for new records, or
>for 'CHANGED for modified records. Or maybe set a flag for a record when
>the linetot field is inserted or updated, and check for that in the
>post-record.
>
>In any case, I realized I'd have to handle rolling back the LINEITEM
>changes, because I've already modified the ORDER.TOTAL field, which is a
>database field, and that will get committed even though I've rolled back
>the detail.
>
>There must really be so many issues. Couldn't there just be a
>'keep-this-field-updated-from' property or something? Since (I think)
>there isn't one, does anyone have a simple methodology for doing this?
>
>Any direction will be most appreciated.
>
>Thanks much,
>
>Yosi Greenfield
>yosi_at_comhill.com
>
>
>
Received on Thu Nov 11 1999 - 04:36:52 CET