7 Replies Latest reply on Jan 7, 2010 10:35 AM by philmodjunk

    Calculating a complex running total from many records

    acrobat_1

      Title

      Calculating a complex running total from many records

      Post

      Dear all Filemaker experts,

       

      I have been creating a solution to allow tracking of finances similar to quicken for myself.  For this solution I have created a database containing a series of records that represent each of the financial entries into each account.  Hence, the list view contains all entries in all accounts.  I need to create a function that will allow me to sort entries from only one account (designated by a field), put them in order from oldest to newest by date (another field) and timestamp of entry when there are more than one entry for an account in the same day, and then calculate the running total for each of the entries subtracting each of the previous entries and the current entry from a known starting point.  This calculation field would hopefully be the same formula for each entry and could use data from all previous fields to calculate the running total on the fly.  This would change if another entry was entered earlier than some of the later entries. 

       

      I hope I described that well enough.  Thanks for any leads on this.

       

      -Rob 

        • 1. Re: Calculating a complex running total from many records
          philmodjunk
            

          "...and then calculate the running total for each of the entries subtracting each of the previous entries and the current entry from a known starting point. "

          That's where you lost me. I'm not sure I follow that. Perhaps you could post an example.

           

          In the mean time, have you tried using a summary field set up with the "Total of", "running total" and "restart summary for each sorted group" options?

          • 2. Re: Calculating a complex running total from many records
            acrobat_1
              

            Thanks for the reply,

             

            I will clarify :

             

            I have a calculated field in each entry of the record that I want to contain the current total after subtracting all the sums from PREVIOUS entries (based on date and timestamp values).  Hence, each entry in any particular record would have a calculation done based on the previous similar entires in earlier records cumulatively.  This would be done dynamically such that if an earlier record were introduced all the subsequent records would be recalculated.  The result would be like a running tally in a checkbook where the total was incremeted or decremeted with each earlier deposit or payment.  Your suggestions of a running total sound right on if I could work out the syntax of getting this to occur dynamically in the calculated entry of the record.

             

            I hope that helps.

             

            -Rob 

            • 3. Re: Calculating a complex running total from many records
              philmodjunk
                

              Here's how I set up my own check book register

              Fields

              Date (Date)

              Deposit (number)

              Credit (number)

              cBal (Deposit - Credit)

              sBalance ( Summary, Total of cBal, Running total)

               

              That does exactly what you describe. There's no subtracting "previous sums" and that's why I've asked for clarification.

               

              If I had multiple accounts to list with a running total for each, I'd add 1 more field, AccountID and add the Restart Summary for each sorted Group option to sBalance. I'd then specify AccountID as the "when sorted by" field.

               

              Pulling up these records in a list view layout and sorting them first by AccountID, then by Date will give me a chronologic listing of all my transactions, Grouped by AccoungID and sBalance will provide a running total for each account.

               

              Does that work for you?

              • 4. Re: Calculating a complex running total from many records
                acrobat_1
                  

                Thanks again,

                 

                You certainly make it sound easy.  I understand your fields for Date, Deposit (the amount entered with that entry),  Credit (the total amount remaining at that deposit), cBal (the new Credit after the Deposit is adjusted) but I do not understand the workings of sBalance.  What does "summary" mean in the parameters parentheses? How does the "Running Total" know to adjust for only previous sums and not sums that exist after the entry?  Would this Running Total automatically readjust in all later entries if you entered another transaction EARLIER than others in the database?  What does "Total of cBal" refer to... I thought that cBal was the running total after adjusting for the current entry?  Forgive my ignorance, I am new to numeric scripting between records in Filemaker.

                 

                -Rob 

                • 5. Re: Calculating a complex running total from many records
                  philmodjunk
                    

                  No scripting involved here, just field definitions. I listed the details of the field's definitions in the parenthesis. I guess my "short hand" was a bit cryptic.

                   

                  sBalance is a field of type summary. When you select this field type you'll get a dialog popping up so you can set the details for how this summary field calculates values.

                   

                  You want a "total of" type of summary field and you want to select cBal as the field that it computes a "total of". "Running total" and "Restart summary for each sorted group" are additional options to select in this same dialog box.

                   

                  A summary field computes a value derived from either a group of records in your found set of records or all the records in the found set depending on what part of your layout you put it in and how you've sorted your records.

                   

                  I'd put the fields I've described in my earlier post in a row like this in the body of a layout set up for list view:

                   

                  [AccountID][Date][Deposit][Credit][sBalance]

                   

                  The result will look very much like a spreadsheet or bookkeeping ledger and sBalance will provide the running balance you require provided you sort the records by AccountID.

                   

                  BTW, the "c" and "s" in cBal and sBalance are just a way I label fields so that I can tell that cBal is a calculation field and sBalance is a summary field without having to go back to Manage Database to see the definition.

                  • 6. Re: Calculating a complex running total from many records
                    acrobat_1
                      

                    Thanks again for the thoughtful reply,

                     

                    I understand your logic and see the result.  However, I wanted to have the list of all transactions in all accounts by date listed.  If I do that the accounts will be mixed and the totals won't make sense.  Hence, is there a way to transiently have the accounts listed in the manner you suggest and then freeze the values so that when displayed by date the totals make sense for each entry?  Could i do this in a separate display that worked behind the scenes to calculate these values and then froze them in other displays and only changed the values if there were new entries or new data entered into any of the entries?

                     

                    I hope I made that clear.  I was hoping to make my displays more dynamic and independent of the actual visual display of records. 

                     

                    -Rob 

                    • 7. Re: Calculating a complex running total from many records
                      philmodjunk
                        

                      " If I do that the accounts will be mixed and the totals won't make sense."

                      Not if you sort the records like I describe. You specify a sort order listing your Account Field first, then your transaction date field second. This will group your transactions by account and then list them (within each group) in chronological order. If you set up the running total summary like I describe, you'll get a running total that starts over with each new account. (You may want to script this sort and set up your interface so that selecting this layout always sorts the records in this manner.)

                       

                      Your listing should look like this when you are done:

                       

                      Acct      Date         Dep.      With.     Balance

                      Acct123 1/1/2009 $45.00                  $45.00

                      Acct123 1/2/2009               $5.00      $40.00

                      Acct234 1/1/2009   $5.00                    $5.00

                      Acct234 1/5/2009   $8.00                   $13.00

                      Acct999 1/1/2009 $100.00                $100.00

                      Acct999 1/2/2009               $50.00      $50.00

                       

                      Note that there are ways to improve on this layout once you get your running total to calculate correctly.