AnsweredAssumed Answered

Summary Fields question

Question asked by ralvy on Oct 29, 2008
Latest reply on Oct 31, 2008 by ralvy


Summary Fields question


I come from a DOS relational database manager (DataPerfect) that has a field function called Keep A Total. It allows me to configure a Number field to add/subtract its value to/from a Number field found in a different table. For instance, a field called Charge, found in the Transactions Table, might update the value found in the Case Charges field found in the Cases Table for a client's case, as well as update the value found in the Account Charges field found in the Account Table for a given client (each client has a single Account with multiple Cases, and each Case has multiple Transactions). All these charges fields would be real Number fields, not Calculation fields, so their values would be stored in the database and could be used for indexing if needed. So no calculations of totals take place when displaying a record in the Accounts or Cases Tables because those values are updated and stored when records in the Transactions Table are created or edited.


The closest thing I see in FP9 is using Summary fields in the Accounts and Cases Tables, which calculate totals from values found in the linked tables. But this has an inherent problem, as far I can tell. Won't that Summary field recalculate every time an Accounts record or a Cases record is displayed?


I imagine I need to work with a script on the Charge field in the Transaction Table, where, on each edit or creation, this script looks to see if the saved value in that field changed, and if so, adds/subtracts the difference to/from the corresponding fields in the other two tables.


I haven't looked at scripts yet. I just wanted to see if there was a more automated way to handle this, like I have been used to with DataPerfect.