Calculation only updating when record saved

[kerkness]

Hopefully I can explain this situation fully. I am building an invoice solution and am having problems getting some of my calculation fields to update without having to first save the main record.

Schema Summary

Main Tables:

- Transactions

- TransactionItems

These tables have the following relationship: Transactions::Id = TransactionItems::TransactionId

Each transaction item has a TaxCode, so I also have two instances of the TransactionItems table:

Tax1_TransactionItems

Tax2_TransactionItems

These tables instances are also related to the main Transactions table but only contain items that match either TaxCode 1 or TaxCode 2

In the Transaction tables I have the following calculation fields.

SubTotal = Sum(TransactionItems::ItemTotal)

Tax1SubTotal = Sum(Tax1_TransactionItems::ItemTotal)

Tax2SubTotal = Sum(Tax2_TransactionItems::ItemTotal)

In my layout I use a Portal to Show as well as add new TransactionItems.

PROBLEM:

When I am adding/editing TransactionItems only the SubTotal field updates itself immediately after changing the TransactionItems::ItemTotal field. Both Tax1SubTotal and Tax2SubTotal will not update themselves until I commit the Transactions record.

Can anyone suggest a better way of constructing my relationships or a trick to force Tax1SubTotal and Tax2SubTotal to auto update? I have tried adding a Script trigger which forces a RecordCommit when portal rows are being edited, but this is sloppy and doesn't work effectively.