While it's always possible for me to misunderstand the question:
I'll assume that you have a relationship "Accounts" based on a unique Account_ID.
If so then you could consider a single unstored calc field resulting in a number. Sum(Account::amount)
You may be able to do this by adding another TO to the relationship graph for the registered Posts table. Match on the account number and have the balance field Sum the amount field.
Another method may be to add an accounts table with a field for balance. Again match on accout number and sum up the amount field from the posts table.
Hope this helps.
Thanks for the input
This is just like a normal bank account, where I need a balance after each post
Date Text Amount Balance
Dec 1 xxx 500 500
Dec11 yy -200 300
dec 18 zz 150 450
Now I need to insert this record: dec 8 xyz 100
So when sorted the table afterwards will look like this
Date Text Amount Balance
Dec 1 xxx 500 500
Dec 8 xyz 100 600
Dec11 yy -200 400
dec 18 zz 150 550
These numbers are also shown in portals different places, where the user might want to se only posts from dec. 15 - dec 31, so therefor I cannot see how to solve this with a calculation.
I also cannot se a way to make a relationship. I know the dates and account no. - but I do not know the date of the previous post ( in the example above it is dec 1), from where I can pick up the balance.
Hope this clarifies my problem, and someone finds a better solution, than I did in my original question
Could you do this with a Script Trigger on data entry? For example, take the most recent balance, add (or subtract) the amount in the current transaction, and then add it to the Balance field as the user commits the record?
No I am afraid that is not possible.
The balance is only interesting, when it has been entered into the "registered table" vi my script.
The user is typing into what I would call a draft table.
On some new posts where the date is the most recent I can use the current balance and then just add or subtract, but if the new post has to fit in between some other posts (due to the date), I have to update all the posts that comes after.
I'm a little confused why this would be very slow. I do something similar on a couple of systems, and it's quick. How many transactions are we talking about? And are you doing them one at a time, or do you put all the batched transactions into the final table, then update the balances?
In other words, which way are you doing this:
1) Grab transaction 1 from draft table.
2) Create new transaction at appropriate point in final table.
3) Loop through all newer transactions in final table, updating balance.
4) Repeat steps 1 through 3 for all remaining transactions.
1) Import all draft transactions into final table.
2) Loop over all transactions from oldest (new record) to newest, updating balance as you go.
I think method 2 might be considerably faster.
My system is a bit more complex, so therefor I cannot do an import.
Actually one line in the the draft table can become 2 or more lines in the end table. I have a debit account and credit account and often VAT accounts in one line.
I just tried to boil it down and just focus on what I see as my main problem.
And yes there can be many transactions - 40 lines in the draft table that becomes 100 in the end table, which can have several Thousand records for each account.
I had hoped that I was missing some math or function in FileMaker that could do this a lot faster.
Unless there's something about your solution I don't get, I'm not sure imports won't work. You can do multiple imports to get the same source record into the destination table multiple times. Just isolate the source set by type (debit, credit, VAT, etc.) and import as many times as needed.
What I'm thinking you might be able to do looks something like this:
1) Isolate the source transaction set by type
2) Import to the appropriate destination account (debit, credit, VAT, whatever)
3) Find based on the earliest date in the source set
4) Go to the earliest transaction and update the balances on a forward-fit basis.
This will mean passing through the records only once (or once per account type), which will be much faster than running all the calculations 100+ times.
What am I missing?