"...and then calculate the running total for each of the entries subtracting each of the previous entries and the current entry from a known starting point. "
That's where you lost me. I'm not sure I follow that. Perhaps you could post an example.
In the mean time, have you tried using a summary field set up with the "Total of", "running total" and "restart summary for each sorted group" options?
Thanks for the reply,
I will clarify :
I have a calculated field in each entry of the record that I want to contain the current total after subtracting all the sums from PREVIOUS entries (based on date and timestamp values). Hence, each entry in any particular record would have a calculation done based on the previous similar entires in earlier records cumulatively. This would be done dynamically such that if an earlier record were introduced all the subsequent records would be recalculated. The result would be like a running tally in a checkbook where the total was incremeted or decremeted with each earlier deposit or payment. Your suggestions of a running total sound right on if I could work out the syntax of getting this to occur dynamically in the calculated entry of the record.
I hope that helps.
Here's how I set up my own check book register
cBal (Deposit - Credit)
sBalance ( Summary, Total of cBal, Running total)
That does exactly what you describe. There's no subtracting "previous sums" and that's why I've asked for clarification.
If I had multiple accounts to list with a running total for each, I'd add 1 more field, AccountID and add the Restart Summary for each sorted Group option to sBalance. I'd then specify AccountID as the "when sorted by" field.
Pulling up these records in a list view layout and sorting them first by AccountID, then by Date will give me a chronologic listing of all my transactions, Grouped by AccoungID and sBalance will provide a running total for each account.
Does that work for you?
You certainly make it sound easy. I understand your fields for Date, Deposit (the amount entered with that entry), Credit (the total amount remaining at that deposit), cBal (the new Credit after the Deposit is adjusted) but I do not understand the workings of sBalance. What does "summary" mean in the parameters parentheses? How does the "Running Total" know to adjust for only previous sums and not sums that exist after the entry? Would this Running Total automatically readjust in all later entries if you entered another transaction EARLIER than others in the database? What does "Total of cBal" refer to... I thought that cBal was the running total after adjusting for the current entry? Forgive my ignorance, I am new to numeric scripting between records in Filemaker.
No scripting involved here, just field definitions. I listed the details of the field's definitions in the parenthesis. I guess my "short hand" was a bit cryptic.
sBalance is a field of type summary. When you select this field type you'll get a dialog popping up so you can set the details for how this summary field calculates values.
You want a "total of" type of summary field and you want to select cBal as the field that it computes a "total of". "Running total" and "Restart summary for each sorted group" are additional options to select in this same dialog box.
A summary field computes a value derived from either a group of records in your found set of records or all the records in the found set depending on what part of your layout you put it in and how you've sorted your records.
I'd put the fields I've described in my earlier post in a row like this in the body of a layout set up for list view:
The result will look very much like a spreadsheet or bookkeeping ledger and sBalance will provide the running balance you require provided you sort the records by AccountID.
BTW, the "c" and "s" in cBal and sBalance are just a way I label fields so that I can tell that cBal is a calculation field and sBalance is a summary field without having to go back to Manage Database to see the definition.
Thanks again for the thoughtful reply,
I understand your logic and see the result. However, I wanted to have the list of all transactions in all accounts by date listed. If I do that the accounts will be mixed and the totals won't make sense. Hence, is there a way to transiently have the accounts listed in the manner you suggest and then freeze the values so that when displayed by date the totals make sense for each entry? Could i do this in a separate display that worked behind the scenes to calculate these values and then froze them in other displays and only changed the values if there were new entries or new data entered into any of the entries?
I hope I made that clear. I was hoping to make my displays more dynamic and independent of the actual visual display of records.
" If I do that the accounts will be mixed and the totals won't make sense."
Not if you sort the records like I describe. You specify a sort order listing your Account Field first, then your transaction date field second. This will group your transactions by account and then list them (within each group) in chronological order. If you set up the running total summary like I describe, you'll get a running total that starts over with each new account. (You may want to script this sort and set up your interface so that selecting this layout always sorts the records in this manner.)
Your listing should look like this when you are done:
Acct Date Dep. With. Balance
Acct123 1/1/2009 $45.00 $45.00
Acct123 1/2/2009 $5.00 $40.00
Acct234 1/1/2009 $5.00 $5.00
Acct234 1/5/2009 $8.00 $13.00
Acct999 1/1/2009 $100.00 $100.00
Acct999 1/2/2009 $50.00 $50.00
Note that there are ways to improve on this layout once you get your running total to calculate correctly.