Why are you updating the information in the other table to begin with. A proper relational system should not have to update records in other tables when modifyed in one.
You might want to post an example of what you are trying to do. Likely, one of us can suggest a better design for your database that eliminates the need for this in the first place.
Phil, I guess this would apply to the posting you made in the other thread, too, where you listed your ledger layout. What I'm doing is also essentially a ledger in that it tracks all my expenses. In addition, it maintains the balances in all my spending accounts by recording income and outgoing transactions. Finally, it also maintains a budget, that is, I establish the budget every month (the allotments don't change often) and I keep a running track of how my spending stacks up with the budget allotments.
I have three tables:
Table Transactions with the fields:
From Account (looked up from the budget table)
From Account Type (looked up from the budget table)
To Account (looked up from the budget 2 table)
To Account Type (looked up from the budget 2 table)
Sub Total (used in a report)
Grand Total (used in a report)
Table Budget with the fields:
Current Balance (calculated by that script I posted)
Difference (also calculated)
Budget Date (in the form 11/2009 so that the budget figures for any given month can be called up when needed)
Table Budget 2, which is a duplicated Table Budget. From Budget I have a From Account Name relation with Transactions, and with Budget 2 a To Account Name relation with Transactions.
And that's how it's all set up. I enter transactions and get my expense spending reports and I set up a budget and see how the actual spending tracks with what's budgeted.
Obviously, as a raw beginner with FM, I'd greatly appreciate any suggestions to make this more effective.
Phil and anyone else following this thread, I guess I didn't explain fully what else I'm doing here and what the specific purpose of this question was. Given what I wrote in my above post, that is, that the current balance of given accounts reside in the Budget table and are updated there when I make a new transaction entry, if I have to change a transaction entry that affects the numbers, then that has to be reflected in the Budget table.
Because this is a totally personal app, I don't want to do a reversing entry and then a corrected one every time I made a small error in entry -- I don't need that kind of audit trail for my dinky, little personal app. And of course in this scheme, even if I do figure out the info I've asked for about changing a record, I'll still have to deal with deleting a record and that act's implications for the balances.
Hope this helps to explain a little more what I'm about.
I followed this thread and your other and note that Phil chatted about calculating totals rather than scripting the calculations. Using calculation and summary fields will serve you much better in the long run since any change in a transaction will automatically update across your tables.
Can you imagine what would happen if you accidentally clicked on the button to run the script a second time? Youd have a basically invisible double entry and your totals would be wrong...and you wouldn't know why.
I know you've invested a lot of time scripting the transactions already...but you'll find it worth your while to revisit your calculations to make them unstored calculation fields rather than script-modified totals. Maybe it won't seem like it's worth the effort now...but it will when you have a far more robust accounting system that isn't at the whim of an errant button click or script launch.
JMHO. Hope it helps...
Thanks for the advice. Of course you're absolutely right. And I've been so concerned with the scripting -- this is my first FM app, I never even thought about the "accidental click" and what a disaster that would be. I don't mind in the least spending whatever time is necessary making this into a well-functioning system because I, as do you, think that it certainly is worth the effort and time.
I still have several questions about Phil's structure and am trying to figure out how to best make it work for me. But I like doing this and am even having fun
Thanks again for the suggestion and the moral support.