7 Replies Latest reply on Mar 27, 2014 9:48 AM by philmodjunk

    financial trial balance report

    eibcga

      Title

      financial trial balance report

      Post

           I am trying to get a trial balance report to work as intended.  The opening and closing balances are not showing the balances properly based on the specified date ranges.  The total debits and total credits are not showing the proper balances within the specified date ranges.  I list below the settings made with the corresponding Column heading / Field name / One-row filtered portal setup.  Also are the screenshots which show the relationships and layout.

      Open

           Ledger_TB::summaryruntotal_amount

           Journal::date  ≥ Ledger_TB::g_dateopen

      Debit

           Ledger_TB::summarytotal_dr

           Ledger_TB::g_dateopen ≤ Journal::date and Ledger_TB::g_dateclose ≥ Journal::date

      Credit

           Ledger_TB::summarytotal_cr

           Ledger_TB::g_dateopen ≤ Journal::date and Ledger_TB::g_dateclose ≥ Journal::date

      Close

           Ledger_TB::summaryruntotal_amount

           Journal::date  ≤ Ledger_TB::g_dateclose

           It's almost there but I'm missing something.  Any guidance please?

            

            

      test.jpg

        • 1. Re: financial trial balance report
          philmodjunk

               Is summaryruntotal_amount a summary field with the "running total" option specified? Try a copy of this field without the running total option specified and see if that makes a difference.

          • 2. Re: financial trial balance report
            eibcga

                 yes, it is set to running total, which is why I call the field "summaryruntotal".  The summary total and running total fields do work on other layouts, but not on this new layout mentioned above with the filtered one-row portals.  I still have the records sort by account ID, then by date, so that the break fields are properly set, but it doesn't seem to make a difference.  I do get different results when I changed the summary running total field to the summary total field, but the result is still not correct either way.  Thank you for your consideration.    

            • 3. Re: financial trial balance report
              philmodjunk

                   A running total summary field, when accessed from a related table is unlikely to return the value you need due to the difference in how a running total field evaluates. You will get the value of the running total from the first related record. And this also is the case even if Ledger and Ledger_TB are occurrences of the same table.

                   Would I be correct that you want to pull up a specific group of records from Ledger and show the opening balance for that set of records? And the closing balance is the balance total of the found records plus the opening balance?

                   I've helped another person set that up so I just need confirmation that I am understanding what you are trying to set up here.

              • 4. Re: financial trial balance report
                eibcga

                     This is what is happening - I'm getting the value of the running total from the first related record.  I would like the sum of all amounts in the found set within a specified date range, by account.  I thought using the running total summary field, or the summary field, would give this result, but it is not.  You are correct, I would like, by account, the opening balance, plus the total debits, minus the total credits, equals the closing balance -- all shown separately in a report, based on a specified date range.  Since I don't have this report, I do things in a crude fashion by doing finds on groups of records which gives summary totals and running totals - not the ideal way to do things in a powerful database like FM.  Any guidance would be appreciated.  I'm still learning a lot about the concept of "context" of layouts and how FM wants things set up.

                • 5. Re: financial trial balance report
                  philmodjunk

                       One last picky but key detail: Am I correct that you need this for a single account at a time? Or do you specify a date range and need to see this data for multiple accounts in the same report? (That would make each closing balance a subtotal for each account.)

                       While waiting for that answer, here's the basic elements needed for a trial balance report for one account.

                       Define the following fields in Ledger:

                       If you don't already have it, define a calculation field, I'll call it cBal as Debit - Credit  (assuming that debits increase the balance and credits decrease it.) Then define a summary field sBalanceRunning as the running total of cBal.  (Note, if you use a single field for debits and credits with negative values for the credits, just define this summary field to compute a running total of that amount field.)

                       If you set up a list view layout based on Ledger with your needed transaction fields and also the sBalanceRunning field, you can perform a find to find all records for a specified account and date range. The result would be correct if your starting balance was 0.

                       To work from a starting balance, define a number field with global storage: gStartingBal. Global fields, as long as they are not needed as match fields in relationships, can be defined in any table that you want and they still work and are still accessible to any layout, script or calculation. I often use a special "globals" table to make it easier to manage these special fields.

                       Now define a calculation field, cAdjustedBalRunning as:

                       gStartingBal + sBalanceRunning

                       You can then modify the layout I described to put this calculation field in place of the sRunningBalance field. If you were to manually enter the correct starting balance into gStartingBal, you'd now see the report that you have requested.

                       The final step is to script the find process so that you first: Find the transactions for the specified account that precede the specified date range to compute the starting balance and assign it to the global field, then finds the records in the specified date range and sorts them by date to show the trial balance.

                  • 6. Re: financial trial balance report
                    eibcga

                         The above "Trial Balance" layout screenshot shows that each row will be a different account, and each row will show four columns: the open balance, debits, credits, closing balance (note there is no body part).  The grand summary total at the bottom part of the layout will add up each column so all the open balances (the result will be zero since all debits/positive figures will equal all credits/negative figures), and the same for closing balances.  The grand summary total for each of the debit column and the credit column will sum all the totals -- the total of each of these columns will have the same amount, since total debits will equal total credits (assuming all accounts are included in the found set).  For your information, the existing debit and credit fields are calculation fields with an IF formula (if the number in the amount field is positive, show in debit column, if the number in the amount field is negative, show in the credit field).  

                         The field Ledger_TB::summaryruntotal_amount I mention above is the running total summary field you suggest I should create, which I already have in the Ledger table, since it's adding up the Ledger::amount field where all debits and credits are entered (debits are positives, negatives are credits). The Total Of summary field, without the running total setting, is Ledger_TB::summarytotal_amount

                         I am currently doing manual Finds by specified accounts and date ranges to get the desired reports from existing layouts in List view, as you mention (what is missing is a starting balance as of a specified start date).  I will try your suggestion of a gStartingBal field, then a cAdjustedBalRunning calculation field as Ledger::gStartingBal + Ledger::summaryruntotal, and modify the layout and use a script. 

                         Thank you!

                    • 7. Re: financial trial balance report
                      philmodjunk

                           For a single account, what I described works for what you want, but be sure to refer to fields from Ledger--not Ledger_TB or you will not see the correct values, but it will need to be adapted to work with multiple accounts.

                           Instead of a single global field for the starting balance--which must be different for each account, define a number field in Accounts. Your relationship from Ledger to Accounts will then enable you to set up a different starting balance for each account.

                           And since you have multiple accounts involved--a detail that I did not spot in your earlier screen shot, I'd use a date based relationship to access the needed starting balances.

                           Ledger::gStartDate > Ledger_TB::TransactionDate AND
                           Ledger::id_account = Ledger_TB::id_account

                           will allow you to use Sum ( Ledger_TB::cBal ) to get the starting balance for a given account if I am correct that Ledger and Ledger_TB are two occurrences of the same data source table.