5 Replies Latest reply on Feb 26, 2014 9:54 AM by JimCondon

    Income Statement

    JimCondon

      Title

      Income Statement

      Post

           I am new to databases in general.  Have been working with FileMaker for a couple of months and recently hit a roadblock.  

           I have two tables, one with expense data and one with revenue date.  I am trying to build an income statement, by year, that summarizes the data, and lists multiple years in a traditional format - labels in column one, 2010 in column 2, 2011 in column 3, etc.  I created a third table called annual, which contains one record for each year, and 4 fields per record:  Year, Revenue, Expenses, Net Income.  The numbers work fine, my issues is printing out a report with each year in a column.  

           I could easily do each year in a row using a portal, but that is not the way any user of financial information would look at such a report.

           Is there anyway to show multiple records from the same database on one form (i.e. a vertical portal)?

           My only solution is to expand the number of fields to 2010 Revenue, 2011 Revenue, 2012 Revenue, etc.  But, that seems too awkward.  I am sure I must be missing something.

           Thank you, in advance, for your comments.  I appreciate your time.

      Page-01.jpeg

        • 1. Re: Income Statement
          philmodjunk

               I could easily do each year in a row using a portal, but that is not the way any user of financial information would look at such a report.

               Actually one row portals are a commonly used method to arrange date from different records into columns. This need not be a problem for "any user of financial information" as the presence of portals would be transparent and serve only to put the data into columns to produce the format shown on your sample report.

               This is called a "cross tab" report and portals are only one option. Calculation fields using ExecuteSQL is another. Both of these methods and others can be set up so that you can designate the year for column 1 and each column to the right is one year different from the one to its left.

               But the structure of your two tables for expenses and revenue will be crucial to pulling this off (and I'd use one table to record all revenue and expense transactions in the same table rather than separate tables--makes for simpler reporting.)

          • 2. Re: Income Statement
            JimCondon

                 Thanks for the input.  

                 Now that I know what to look for - Cross Tab Report - there seems to be a wealth of information out there.  

            • 3. Re: Income Statement
              TKnTexas

                   I would concur with Phil that the data is more easily reported from one table.  

                    

              • 4. Re: Income Statement
                philmodjunk

                     here is a demo file of a "bookkeeping ledger" type of database: https://dl.dropboxusercontent.com/u/78737945/AccountingLedger.fmp12

                • 5. Re: Income Statement
                  JimCondon

                       Phil,

                       Thanks for the follow-up.  As soon as I started looking for info on cross-tab reports, I was able to figure it out pretty quickly.  It may not be the most elegant solution, but it works well.  

                       I will check out the bookkeeping ledger you sent me, to see what else I can learn.  I appreciate your follow-up.

                       My road block has been cleared.  And, I am off and running toward my next problem.

                       Thanks again.