8 Replies Latest reply on Feb 6, 2009 3:47 AM by Ivan Littler

    Calculations for running totals and balances

    athens97045

      Title

      Calculations for running totals and balances

      Post

      Kindly request assistance in designing calculations for fields that have running totals and balances.  I created a database for charitable contributions with the following fields:  Charity; Budget (total Contribution to be made); Contribution (in $); Date (of contribution); and, Balance remaining (to be contributed).  I would like to have two calculations:  first calculation is a running summary amount of the contributions; and, the second calculation that reduces the balance remaining after each contribution. 

       

      Thanks, oracle 

        • 1. Re: Calculations for running totals and balances
          Orlando
            

          Hi Oracle

           

          Welcome to the forum,

           

          This should not be to difficult to implement, You will need to start by having two seperate tables for the data, CHARITY and CONTRIBUTIONS, if you do not have it setup like tis already.

           

          CHARITY will need the following fields:

          __UniqueID (Number) Auto-enter serial

          Name (Text)

          Budget (Number) 

          ContributionBalance (Calculation number) 

          BalanceRemaining (Calculation number) 

           

          DONATIONS will need:

          __UniqueID (Number) Auto-enter serial

          _CharityID (Number)

          Amount (Number)

          Date (Date) 

           

          Then in the Relationship Graph join these tables via the Charity ID

           

          CHARITY                 CONTRIBUTIONS

          __UniqueID ----=---- _CharityID 

           

          Now this is setup you can go back and add your calculations

           

          ContributionBalance (Calculation number) =

           

            Sum ( CONTRIBUTIONS::Amount )

              // This will give you the SUM of all related records Amount value.

           

          BalanceRemaining (Calculation number) =

            CHARITY::Budget - CHARITY::ContributionBalance

              // Swap round depending if you want a negative value or not.

           

          Then on your CHARITY layout create a portal to display you CONTRIBUTIONS and whenever you add a donation this can be done via the port, if set to allow creation, or done via a scripted process, just as long as it is always linked to teh CHARITY record by the ID.

           

          You can then extend this to incorporate a CONTACTS table and link the CONTRIBUTION to a person ect. 

           

          I hope this helps. 

           

          • 2. Re: Calculations for running totals and balances
            athens97045
              

            Thanks Orlando, I created two tables as you suggested with the fields you had.  However, when I added new records to CONTRIBUTIONS table, they would not add or update records in the Charity Table, nor would they perform the total and balance calculations.  What did I do wrong?  I set the relationship between the CHARITY and CONTRIBUTIONS tables as you directed:  

            CHARITY CONTRIBUTIONS

            _UniqueID   = _CharityID

             

            You did not have a _CharityID field in the CHARITY table, was this a mistake?  Also why wouldn't _UniqueID = UniqueID in both tables?

            Also, the CHARITY table is designed only to list each charity only 1x, is that correct?  The CONTRIBUTIONS table shows a record for each contribution, correct?  Therefore, when I add records it is only to the CONTRIBUTIONS table. 

             

            I believe when you listed DONATIONS in your solution, you really meant CONTRIBUTIONS, is this correct? 

             

            Thanks oracle 

            • 3. Re: Calculations for running totals and balances
              Orlando
                

              Hi oracle,

               

              Sorry this is not working for you.

               

              To answar a few of your questions before getting down to the main issue here, Yes my reference to Donations should have been Contributions.

               

              Yes the CHARITY table should link to CONTRIBUTIONS from _UniqueID to _CharityID. I always name the primary key field _UniqueID and then the foreign key is named after the table, so _UniqueID to _CharityID.

               

              And finally you are correct about the number of records you will end up with in the tables. I should have detailed this in my original post, The CHARITY table will only hold one record for each individual charity you are collecting donations for, and the DONATIONS table will hold one record for each donation you receive. So for a particular charity you would create the Charity record and then as many Contribution records you need to create, as long as they are related to the charity record via the _CharityID.

               

              Now first thing I need to check is that you when a Donation record is being created is the _UniqueID from the Charity record it is relating to being entered into the _CharityID field in the contribution record? It sounds like the records are not being related to each other with the ID's.

               

              The easiest method for creating the Contribution records would be to set the relationship to allow creation of record via the portal displayed on the Charity layout. To do this in the Relationship Edit dialog tick the box to "Allow Creation of Records via Relationship" on the Contributions side of the screen. See picture below.

               

              Relationship Setup

               

              Now when you set up the portal on the Charity screen and view it in Browse mode there will always be an empty row at the bottom, when you enter an amount in the empty row, a new record will be created and the _CharityID will automatically populated.

               

              Also just check the calculations look like this.

               

               

              I hope this makes things a bit more clear for you, if not just let me know. 

              • 4. Re: Calculations for running totals and balances
                athens97045
                  

                Orlando.  Thanks, I greatly appreciate the solution you provided.  The calculations and explanation you provided were extremely helpful.  I had two issues in attempting to come up with the solution:  1) understanding how to develop the calculations; and, 2) understanding the relationship between the tables.  When I initially created the relationship between the two tables in the EDIT RELATIONSHIP dialog, I put check marks on both tables and would like to know if this is one reason I had problems with it.  Could you further elaborate on why you can't check all boxes for both tables in order to create or delete records?  Secondly, can you tell me whether there is a great source for coming up with the calculations you used.  

                 

                I have another major problem in attempting to come up with a relational database for an Investment Portfolio, but I'll submit that request later.  

                 

                Again, thanks for your tremendous assistance.  You can probably tell I'm a novice.

                Thanks,

                Oracle 

                • 5. Re: Calculations for running totals and balances
                  Orlando
                    

                  Hi oracle

                   

                  Glad you got this to work. I can't be sure why your solution did not work without seeing it but I don't think it would have been down to the check marks in the Edit Relationship dialog.

                   

                  With regards to what you can check on and off in the Relationship Dialog, You can also set Delete and sort on, I just set Create for the purpose of this process, experiment and see what works for you. And do ask if you get stuck with anything.

                   

                  A good place to start with getting into calculations are a couple of books I always reference

                   

                  FileMaker 9 Developer Reference: Great source of info of Calculation and Script functions with examples.

                   

                  FileMaker Pro 9 Bible: A must have, full of info to get you started and examples to follow.

                   

                  Good luck with your solution and do post if you have any other questions. 

                  • 6. Re: Calculations for running totals and balances
                    Ivan Littler
                      

                    Dear Orlando

                    I came across this thread in an attempt to find a solution for the following problem. I am a novice DB designer creating a book-keeping DB using FM Pro 8 for my wife's small cosmetics and toiletries business. In my 'Petty Cash Recon' page I intend creating a current balance by subtracting a cost from an existing balance. It is looks like a similar application to the one illustrated in this thread. Could you please advise.

                    Regards

                    GLF 

                    • 7. Re: Calculations for running totals and balances
                      Orlando
                        

                      Hi GLF and welcome to the forum

                       

                      I am more than happy to help, but could you provide some more info on how you currently have the system setup. How are the Tables setup, and the relationships you are trying to get the values through, and any calculations you have already setup for this and if they are working as required or not, and we can go from there.

                      • 8. Re: Calculations for running totals and balances
                        Ivan Littler
                          

                        Hi Orlando

                        Thanks for your prompt reply. I'll need a day or two to prepare it for you. I'll be in touch.

                        Sincere thanks

                        GLF