3 Replies Latest reply on Nov 5, 2009 7:29 AM by martinpaulrice

    Do I need a relationship, a script or both?



      Do I need a relationship, a script or both?


      I'm brand spanking new at FM and working on my first app which is to track my budget and expenses. I'm using FM Pro 10 on an Intel imac running OSX 10.6.1


      My db has two tables: Budget and Transactions.

      In the Budget table I have the following fields:

      Account Name; Account Type; Budgeted Amount; Current Balance; Difference; Budget Month

      In the Transactions table I have the following fields:

      Date; Description; Comment; Check Number; Amount; From Account; From Account Type; To Account; To Account Type

      What I want to do is increment or decrement the Budget::Current Balance field for given Accounts when I enter a transaction.

      In other words, I'll enter a transaction, say for 15 dollars, from the Cash account to the Eating Out account. In the Budget table, I want the current balance for Cash decremented by 15 dollars and the current balance for Eating Out incremented by 15 dollars.

      I guess I need to do this by a script, but I'm not sure. And if so, would I need to build some relationships between the two tables?

      Currently, the tables are related as follows:
      Budget::Account Name -> Transactions::From Account
      Budget 2::Account Name -> Transactions::To Account


      (I was able to do this relationship courtesy of help from here.)

      I do this so that I can automatically enter From Account Type and To Account Type in the transactions table. I use these for various reports.

      I hope I've made this clear. I'm not too sure about the terminology yet.

      Any help would be greatly appreciated.


        • 1. Re: Do I need a relationship, a script or both?

          You can use your existing relationships but put calculations in place of your current data field in Budget.


          Current Balance could be defined as:


          Sum ( Budget 2::Amount ) - Sum ( Budget::Amount)


          The Sum and other aggregate functions such as Average, Max and Min, when used in this format reference the designated field for all the related records to compute a total, average or other such value.

          • 2. Re: Do I need a relationship, a script or both?

            Although you could use calculations in the Budget table that refer to the Transaction tables, the results will not be stored. At first, this will not cause any issue. In the long run, the calculations will visibly take more and more time to return a result.


            As an alternative to calculation, you can make use of scripts.


            Depending on the transaction rules you have implemented, there are three possible scenarios that will affect the totals: the addition of a transaction; the modification of a transaction; the deletion of a transaction. In the case of modifications and deletions, this means you need to remember what the original transaction amount was before allowing the user to change the value.


            Whenever we say script, we need to keep in mind the user interface. How will the script that keeps your records straight be activated without fail by the user?


            Forcing a user to enter a new/modify/delete transaction window is one approach that works. It needs to ensure a user can not simply cancel the action by, say, simply closing the window. There are a number of scenarios to look after in the scripts. There is a downside to this approach: it is more cumbersome to the user who needs to perform regular data entry, as it normally forces the user to click on buttons.


            Using script triggers tied to a transaction amount field is another approach that works. Remember, however, that script triggers only work when a user performs an action on the object that contains the script trigger. Importing data, for example, will not launch the script trigger. In that case, you would need a script that updates the totals based on the imported data.


            As a final note, there is no script trigger for record deletion. If a user can delete a Transaction record either by deleting a record in a layout belonging to the Transaction table or by deleting a portal row of a portal displaying records of the Transaction table, then your totals will no longer be correct. You will need to tie the record deletion to scripts via buttons. This is normally the expected approach.


            I hope this helps! 

            • 3. Re: Do I need a relationship, a script or both?

              Phil and David, thanks so much for the responses. They really help and I appreciate them.