5 Replies Latest reply on Dec 2, 2013 11:01 AM by philmodjunk

    Total Balance

    FilipePimentel

      Title

      Total Balance

      Post

           Hello FM12 Pros: 

           I am looking to run a monthly report that automatically updates every time it is run: 

           It needs to be only one report showing the figures: Basically what we want is:

           Revenue: 

           until December/12        January/13 Total               Total until January/13 (this would include Total Until Dec/12)  

            

           Expenses: 

           until December/12        January/13 Total               Total until January/13 (this would include Total Until Dec/12)  

            

           Final Balance

           Total Revenue - Total Expenses

            

           And then Again for February, March, etc...figures would be updated automatically. I will not use the body, as I only need to show the totals. 

           Thanks for the help

            

           F

        • 1. Re: Total Balance
          philmodjunk

               I am guessing that "12" refers to 2012 and "13" refers to 2013. Is this the case or are these days of the month?

               How is your data structured? Some developers use one field for all expense and revenue amounts with a second field to identify whether it is an expense or revenue. Some use negative numbers for expenses and positive for revenue. I prefer to do it Ye Olde Bookkeeper fashion and use a debit field for one and a credit field for the other. Others use completely separate tables for expense and revenue transactions--which usually complicates reporting needs.

               All of the above can support the report that you need but the implementation details will differ.

          • 2. Re: Total Balance
            FilipePimentel

                 I use one field for all expenses and revenues with a second one to identify what it is (Revenue or Expenses). 

                 And the "12" is actually the year. 

            • 3. Re: Total Balance
              philmodjunk

                   So the first requirement is to separate the expenses from the revenue.

                   This can be done with calculation fields such as:

                   If ( TransactionType = "income" ; AmountField )

                   to get a field that is empty when the transaction is for Revenue

                   A Summary field can then compute the total of this calculation field to get a total income. Sub summary layout parts can use this summary field to get subtotals for specific groups of records such as a group of records for a given month.

                   A Final Balance can be computed by subtracting a summary field for total expenses from a summary field for total income. (See why I use one field for debits and another for credits?)

                   Running totals are also possible if you select that option for a summary field. (And this sometimes requires TWO summary fields, one with the running total option selected and one without it selected.)

              • 4. Re: Total Balance
                FilipePimentel

                     Phil, 

                      

                     Thanks for your reply. 

                     You actually answered a questions I was going to ask: why is it better to have different field for income and expenses.

                     Once again thanks 

                • 5. Re: Total Balance
                  philmodjunk

                       I have frequently set up list view layouts to function with a "Bookkeeping Ledger" format.

                       I define date, description fields, include the debit and credit fields and then define a calculation field, cBal to be Debit - Credit. I then define a summary field to compute a running balance of cBal to show the current balance on each row of the layout.