Thank you for your post.
You can use a calculation field to total information in another table. When you are defining a calculation, use the Sum() or Count() function, and reference the field in the linked table. That will allow you to get a summarized value from the related records.
The summary field summarizes records in the current table.
Hmmm...this sounds like I would be placing that option on the field that holds the total (e.g., in the Accounts Table). So I would have to make sure the field that contains the Charge (e.g., in the Transactions Table) somehow triggers the field that holds the total (in the other table) to update its value. I imagine I do this with a script.
Thank you for the clarification.
It sounds like you are trying to do two different things. Or, do you want to take a value from a file, adjust it one file, and then change that value back? Maybe a realistic example of what you are trying to accomplish might make it easier to understand.
Thanks. Here's my example, outlined in the original post, but hopefully made more clear here. Three tables:
One-to-many relationships going down that above list. Each Account has many Cases. Each Case has many Transactions.
There's a Total Charges field in the Accounts table as well as the Cases table. There's a Charge field in the Transactions table.
I want any change in the Charge field in the Transactions table to change the value in the respective fields in the Accounts and Cases tables when the record in the Transaction table is saved. I don't want the change in the respective fields in the Accounts and Cases tables to take place when I display them. Rather, I want them to be updated when saving a record in the Transactions table.
So the Total Charges in the Accounts and Cases tables are not Calculation fields. They're Number fields who's values are stored in the database like any other Number or Text field (Phone, SSN, etc.).
This is very easy to do with DataPerfect. Tell the Charge field in the Transactions table what fields to carry a total to, and it's done. It does the rest.
Is that clear?
Reading the Help screens and browsing one of the example databases that came with the trial download, the alternative seems to be defining the Total Charges fields in Accounts and Cases tables as Calculation fields, using the Sum() function to get totals from related records in the Transactions table. But there can be hundreds or thousands of records in the Transactions table for each of many Accounts. My newbie understanding of the Calculation field is that it will recalculate every time I display a record that contains it, and also recalculate everytime I run a report that accesses it. This seems unnecessarily time consuming.
That's why I wanted to see how to use Number fields for Total Charges in the Accounts and Cases tables, and have their values updated whenever a subrecord in the Transaction table is saved.
If you only want a value changed when a transaction is saved, then you need to have some way of letting the other tables know that the transaction is saved. This can be done with a button that executes a script where it shows the transaction is saved and the value is then copied to the other tables and updated.
For example, the script may include:
Set Field [Status; "Saved"]
Set Variable [$amount; Amount field]
Go to Layout [layout that contains case information]
Set Field [Case Amount Field; Case Amount Field + $amount]
Go to Layout [layout that contains Account information]
Set Field [Account Amount Field; Account Amount Field + $amount]
Granted, this is a simplistic script, but it should point you in the right direction.
Thanks, TSGal. This looks like a good start.