5 Replies Latest reply on Jun 19, 2009 7:09 PM by TKnTexas

    monthly financial reports

    lenek

      Title

      monthly financial reports

      Post

      Having finally finished another database for hubby (THANK YOU for the help!)

       

      I strangely decided to build one for my company.

       

      I'm nearly done but am a little stuck.

       

      To summarise

       

      I have tables inventory, assign sales, sales orders, customers, assign purchases, purchase orders and suppliers

       

      The sales order and purchase order tables are very similar in that each has an order number, date, total value and type (where type is either the type of purchase (inventory, asset, supplies etc) or the type of sale - (which is in effect one of several shops, internet etc)

       

      The sales order and purchase order tables also have a summary field which is a total of the 'total value' fields

       

      What I am struggling to do is my last report - which would show by month all purchases (type and value) and all sales (type and value) and then an overall total at the bottom (which would hopefully be +!)  So showing purchases (expenditure) on the left and income (sales) on the right

       

      I know I need another table linking, but I'm just a little lost over where to go

       

      Thanks for any help

        • 1. Re: monthly financial reports
          TSGal

          lenek:

           

          Thank you for your post.

           

          I can think of a couple of different ways to do this.

           

          First, it sounds like a many-to-many relationship.  That is, there can be several purchase orders per month and several sales per month, so the trick is to get them displaying together.  Therefore, your conclusion of needing another table to link is correct.  The linking to each of these tables would be by year and month.  If you just did month, then how would you distinguish between January 2008, January 2009 and January 2010?  This is a rhetorical question, and it may not apply, but thought I should explain it.

           

          This third table needs a Year and Month fields.  The Purchase and Sales tables need two calculation fields that extracts the Year and Month from the Date field.  Then, link both of these fields in the third table to the Year and Month calculation fields in the Purchase Orders and Sales tables.  Then, you can put a portal into Purchases on left side of your layout, and a portal into Sales on the right side.

           

          In the third table, you can then create two calculation fields that use the Sum() function to add up the totals in the portals, and a Summary field to add up totals for each month.

           

          This should point you in the right direction.

           

          If you need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: monthly financial reports
            lenek
              

            Hi and thanks.  Why does it make so much more sense when someone just writes it out!

             

            I'll have a play and update tomorrow.

             

            Thanks again

            • 3. Re: monthly financial reports
              TKnTexas
                

              Just a tip to share, for all of my financial data, I create one of two extra fields (sometimes both). 

               

              Month=yyyy.mm

              Week =yyyy.wk

               

              I do this especially on sales numbers for running sub-summaries by these two fields.  Even if I don't need to run reports like this initially, I know I will when i have more data available.  It also addresses year over year and month over month data.

              • 4. Re: monthly financial reports
                LaRetta_1
                  

                Month=yyyy.mm

                Week =yyyy.wk

                 

                I actually picked up another method which is even more flexIble (thanks to Comment).  Firstly, I wouldn't use week of year because it varies from year to year but let's move to the calculation for grouping by month/year:

                 

                yourDate - Day ( yourDate ) + 1 (calculation result of date )

                 

                ... this will turn all your dates into the first day of the month.  Why is this powerful?  Because, by keeping the calculation as a true date field, you can utilise the power of FileMaker dates and then 1) you can use pop-ups for selecting the month/year for display in a portal and grap 'month-chunks' of data as you wish, 2) You can group your reports by this date and they will automatically sort true by year and then month, 3) you can use that same field and place it on a layout and (from field date formatting), select to display as September, 2007 or 9-2007 or anything you like. 

                 

                Once you've created this generic 'first day of each month' you will find you use it all the time and you will never go again without it.  Guaranteed.

                • 5. Re: monthly financial reports
                  TKnTexas
                    

                  I remember reading that new power in date fields.  Your enhancement is great. 

                   

                  Regarding weeks, I usually designate the start of the week.  Days may move from one week to another, but when comparing sales it works correctly.