3 Replies Latest reply on Nov 16, 2013 7:16 AM by philmodjunk

    Financial trial balance

    eibcga

      Title

      Financial trial balance

      Post

           I have a layout called Trial Balance, based on the Ledger table, that lists all the accounts as of a certain date (e.g., October 31, 2013).  The Body of the report provides a sub-summary when sorted by account number.  A trailing grand summary totals all the account balances that were listed in the sub-summary part.

           The balances shown are as at October 31, 2013 accumulating since the first financial transaction was entered.  I would like to be able to show on one report a true trial balance where it shows all the balance sheet accounts (i.e., assets, liabilities, etc.) with their respective perpetual balances accumulated since time zero to October 31, 2013, all the income statement accounts (i.e. revenues and expenses), with their respective periodic balances accumulated since the opening date I specify (e.g, since January 1, 2013 to October 31, 2013), and any opening equity accounts (i.e., opening retained earnings) with the opening balance as at the specified opening date.  Thus we have…

           Time Zero                              Opening Balance              Closing Balance

           0                                            January 1, 2013               October 31, 2013

           |<———————————————————————————————>|  Perpetual = Time Zero to October 31/13

                                                        |<—————————————————>|  Periodic = January 1, 2013 to October 31/13

           |<————————————>|                                                               Opening = Time Zero to January 1, 2013

            

           I already have all the relationships set up, but I would like to use a filtered portal on the sub-summary part in the Trial Balance layout, where the portal is one row only.  Currently, the Trial Balance report just lists all the accounts and their perpetual balances (i.e., time zero to October 31, 2013).  Could I filter something like, if the account number starts with 1, 2 or 3, then sum to October 31/13; if account number starts with 4 or 5, then sum from January 1/13 to October 31/13, else sum to December 31, 2012 or (i.e., January 1/13 minus 1).  Is this the right idea?

           The relationships graph shows object tables in grey, and event tables in green.  Query relationships are in red.  The Journal table tracks Transactions.  The Ledger table tracks Transaction Line Items.  A financial transaction can effect many accounts at one time, and an account can have many transactions at one time.  So, the Ledger table serves as the join table to resolve the many-to-many relationship between Journal and Account.

           Any guidance please for a beginner using FMPA 12.0v5 on the Mac?  Thank you very much indeed! 

      Screen_Shot_2013-11-15_at_3.30.19_PM.png

        • 1. Re: Financial trial balance
          philmodjunk

               You have a very clear, for the most part, synopsis of the problem, but:

               

                    already have all the relationships set up,

               I'm not sure that you do for what you need for your filtered portals. The one thing that you can't see when you look at the table occurrence boxes in Manage | Database | Relationships is which boxes have the same data source tables and which do not.

               A self join relationship between Ledger and a second Tutorial: What are Table Occurrences? with the same data source table is what you need for your portals. And you can use multiple table occurrences with different date range relationships or one table occurrence with multiple filtered portals to show each of your balances. In each case, the same summary field that you likely already have (just not a running total summary field), can be placed in the one row portal to show the totals. (oh yes, and if you were to set up multiple table occurrences, you don't need the portals and the totals can be accessed for use in calculations and scripts...)

               The basic match fields to use is your account ID field. THen you can include date fields as additional match field to get a match by date range or you can put the same logic into the portal filter.

               A typical date range portal filter expression:

               GlobalFields::gStartDate < PortalTable::Date AND GlobalFields::gEndDate > PortalTable::Date

          • 2. Re: Financial trial balance
            eibcga

                 Thank you PhilModJunk for your quick reply!  All the tables you see in the relationships graph are data source tables, except for the red ones, which are table occurrences of the Ledger table.  So I just need then, to add another red TO of the Ledger table, say, call it Ledger_TB, and add a new layout on it with filtered portals pointing to global date fields.  I'll try this approach.  Why do I have to add another TO? Can't I just add a new layout (or edit the existing one) to the Ledger table and have filtered portals on it (pointing to global date fields)? — ok, I see why I have to add another TO otherwise the fields won't show as related fields when I create the formula in the filtered portal.  I'm not getting the desired results, however. How do I get back the formula in the filtered portal so I can edit the formula?  I'm also confused what related fields to use from which TO, and how to build the filtered portal to give me what I want per my previous post?  

            • 3. Re: Financial trial balance
              philmodjunk
                   

                        So I just need then, to add another red TO of the Ledger table, say, call it Ledger_TB, and add a new layout on it with filtered portals pointing to global date fields.

                   Not quite. You can use your existing layout based on Ledger or create a new layout based on Ledger. You would not use a new layout based on the new table occurrence as you need to have a "starting context" of the Ledger table occurrence.

                   

                        Why do I have to add another TO?

                   You need a TO that allows you to establish the needed relationship that matches to the correct set of related records. None of the other TO's that you already have define the relationship that you need for your sub totals to be displayed in your one row portals. And I do not see the relationship needed in your new screen shot either. You need at least this relationship:

                   Ledger-----<Ledger|SameAccount

                   Ledger::id_account = Ledger|SameAccount::id_account

                   if you use a filtered portal

                   Or you can define some global fields for your dates to hold the dates needed for your date ranges and then include them as match fields:

                   Ledger::id_account = Ledger|ThisYear::id_account AND
                   Ledger::gJanuary1 < Ledger|ThisYear::TransactionDate AND
                   Ledger::gCloseDate > Ledger|ThisYear::TransactionDate

                   Either method can be made to work and I've already discussed the trade offs between using a filtered portal and building the "filter" right into the relationship.