4 Replies Latest reply on Jan 6, 2012 6:31 PM by mikefromnewmexico

    Running Totals

    ProfV

      Title

      Running Totals

      Post

      Greetings:

       

      I am in the process of creating a Database that tracks budgets. And I'm having a problem with keeping a running total on a certain account. For example :

      Starting Balance for Account 249- Supplies - $2,000

      Entry 1: 100 Remaining Balance= 1,900

      Entry 2: 500 Remaining Balance 1,400

      Entry 3: 350 Remaning Balance 1,050

      If any one can help me with this problem it would be a ton of help! And if you need me to explain more please let me know!

       Also I am using File Maker Pro 9 ov3 on a Windows computer.

       

      I have perviously tired to do a simple calculation of

      Remaining Balance= Starting Balance- Vendor Quote

      But it's just giving me that calculation just for that line instead of a summary for the entire account! Thanks in advance

       

        • 1. Re: Running Totals
          philmodjunk

          Define two number fields such as: Debit, Credit.
          Define a calculation field cBal as Debit - Credit.
          Define a summary field, sBalance as the Total of cBal with the running total option selected.

          Put starting balance in the Debit field of record 1.
          Put Entries 1, 2 and 3 in the credit field of subsequent records.

          In a list view, table view or portal you can put the Debit, Credit and sBalance fields to set up a basic "ledger" of your transactions. The cBal field need not be placed on any layout.

          • 2. Re: Running Totals
            ProfV

            Thank you so much! Your directions helped a ton! However, now that I am actually doing the running total, I am running into another problem. How can I get the running total to work for separate accounts as well, for example before we worked on the Supplies Account, now I would like to do it for multiple accounts (Travel, Salary, etc.). Would I have to create another layout for each account or would it be able to work in the same layout?

            Also, we are trying to track a the budget of different Grants, so along with having a running total for each individual account is there a way to link it so that it give us a running total of the Grant as a whole?

            Once again thank you so much for all your help and if you need me to clarify anything just let me know!

            • 3. Re: Running Totals
              philmodjunk

              Assuming you have a field in your table that identifies the account it belongs to, you have an option in the summary field definition to "restart summary with each sorted group". If you select this option, you can select your account field as the "group by" field. Now you can sort your entries first by account then by date or any other useful order (assumes you have a date field for each transaction record) and you'll see the entries grouped for each account with a running total that starts over for each.

              You can also keep your set up as is and just perform a find for a given account. That gives you a view of your transactions with your running total for one account at a time. A portal to this table that displays only one set of account transactions at a time could also be used to segregate your transactions by account.

              • 4. Re: Running Totals
                mikefromnewmexico

                The running balance worked great for my inventory needs and I'm using my line items table as a ledger. I was beating myself up for a bit til I ran across this post. Thanks PMJ