6 Replies Latest reply on Nov 24, 2009 7:20 AM by martinpaulrice

    About processing a record change



      About processing a record change


      In one of my data entry screens, I have a button I click after filling in all the fields for a new record. The button then runs a script that uses information in this new record to modify some records in another table.


      What I need to do now is create another button I'll click if I bring up one of the records in the first table and change it so that I then can again modify some records in the other table.


      My question has to do with what I need to do when and how (though I don't mean how to write the script). When an existing record is brought up in the first database, I need to capture some of the existing field values. Then, if I change any of those values in the form, I'll click the second button and run the new script to modify some records in the second table.


      I'm using FM Pro 10 (on an iMac running OSX 10.6.2) so that I have access to triggers if I need them.


      So my questions are:

      1) what do I need to do to get a script running as soon as an existing record is brought up so that I can capture the information I might need?

      2) after the changes are made and I click the button to run the script that will make the changes in the other database, how do I access the information I captured in question 1?


      I'm a new user and not very experienced yet. One other piece of information that might be useful is that I will be the only person using this application.


      Thanks for any help. 

        • 1. Re: About processing a record change
             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.
          • 2. Re: About processing a record change
               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.
            • 3. Re: About processing a record change

              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:




              Check No


              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:

              Account Name

              Account Type

              Budgeted Amount

              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.



              • 4. Re: About processing a record change

                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.



                • 5. Re: About processing a record change

                  Howdy martin,


                  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...

                  • 6. Re: About processing a record change

                    Hi, Ninja:


                    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.