6 Replies Latest reply on Feb 27, 2013 1:20 PM by robyne

    Running balance report

    robyne

      Title

      Running balance report

      Post

           I am trying to create a report that will show all my accounts and their current running balance.  Basically, I have a series of "checkbook" accounts for each employee.  Each account has a running balance field, which works fine on a report for that employee.  But when I put it the field on the report with all accounts, either it computes everyone together or the amount will not show up unless I actually click into the field (this is via a portal and the information goes away the moment I am out of the field). 

           I am using three tables for this:  Table 1 is for Services, Table 2 is for the Employee and Table 3 is for the "Register" .  So an Employee may take care of 5 Services that incur a set of expenses.  The relationship there is based on the Employee# (Services - Employee).  Then the employee will take those expenses out of the "his" Register.  This relationship is based on Register# (Services - Register).  The register is where the running balance occurs.  

           The final relationship is based on the Employee-Register (using Employee #).   Using this 3rd relationship does work in a portal but you cannot see the information unless you click into the field.   What am I missing?

            

           Running FM Advanced 12.3 on Mac OS 10.7.5

        • 1. Re: Running balance report
          philmodjunk

               I can't tell from your post why you have a portal involved here, nor exactly how you are using it.

               If you set up a list view layout listing all your employee check book records, you can sort by employee to group them. If you define your summary field to restart totals when grouped by employee, you'll then see a running total that starts at zero for each employee's group of entries.

          • 2. Re: Running balance report
            robyne

                 Sorry, I failed to explain that I am grouping the employees by a given date.   So if I go to today, and find all the employees, I should get a running balance for each one.  Obviously, since I have only one day, the running balance only picks up today.  Thus, my thought was to have a small portal that would pull this summary piece into the report.  The correct figure is in that field but it won't physically show up without clicking onto it (I assume this refreshes the calculation). 

            • 3. Re: Running balance report
              philmodjunk

                   Can you give a more detailed example of what you want to see and how your tables are set up?

                   I'm not clear on how you can get both a running balance for each employee yet group them by date...

                   IF the field you are clicking is a calculation field, I'd double check the result type to make sure that it is number.

              • 4. Re: Running balance report
                robyne

                     Sure.

                     #1:  Service Table - Register Table; matching fields are Register id. 

                     In report #1 (by employee), the records are referencing the Register Table.  Running balance is perfect.

                     What a I want is a quick way to see this running balance for each employee in one list on the fly since I need to know if their "registers" need to be replenished. 

                     So I created #2:  Service table - Employee table, matching field is Employee id.   The idea being, I get the magic number for each person in one place. 

                     I did try setting up a summary report to find all employees, sort by each, but the balance just adds everything up with #1 relationship and does nothing in #2.    so then I tried by date (recognizing that this odd, but then if you do accounting by hand on a ledger, this is the way it looks)  finding employees for today and getting the totals through a portal.  No go either. 

                      

                      

                • 5. Re: Running balance report
                  philmodjunk

                       I see no reason why you can't use a layout based on Register. You can sort your records by Employee. The trick is to set up the summary total to do a running total, but also set it to restrart totals when grouped by employee.

                       But it really sounds like all you need is the current balance in each register instead of the running balance. That could be done from an Employee table so that you see one row of data for each employee with the current balances.

                  • 6. Re: Running balance report
                    robyne

                         Oh! Briliant!  I did not realize I could restart the totals that way.  Thank you!