5 Replies Latest reply on Feb 20, 2013 9:01 AM by philmodjunk

    Yearly reports



      Yearly reports


           I am new to FileMaker - I have been using Bento since the days of Bento 1.0 to manage my website design business. However I have just got a new PC and hence the switch to FileMaker. I have managed to copy all my Bento tables and load them into FileMaker and make the relationships between the tables.

           However, In Bento I have a table for Sales where I keep track of invoices, and below it I have Collections for Sales 2011, Sales 2012, etc. I cannot figure out how to make this happen in FileMaker. I did ceate a Layout which is quite lovely with Monthly subtotals, but it seems to report on the whole table - I can't find a way to limit it to one year only.

           Please advise what is the best way to accomplish this.

        • 1. Re: Yearly reports

               How would you use such a "collection"? I'm not familiar with Bento so can't quite picture why you would do that and how you would use it.

               In Filemaker, it's quite easy to perform a find to find all records dated for a given year on a layout designed to provide a summary report of that data.

               You can set this up so that you specify a year in a field, click a button and the report based on all invoices dated for that year pops up on your screen.

          • 2. Re: Yearly reports

                 The Collection is a subset of the data in the parent table (called a Library in Bento). I enter my invoices in the 2013 Collection (for example) with net and total sales, tax, when paid I enter the paid amount and there is a calculation of Balance Owing so I can see who has not paid their invoices.

                 So with the Collection I can see totals to get a year to date picture of total sales, total paid, balance outstanding, etc. In my Client table, I have a portal (equivalent to a Related Records collection in Bento) to show the invoices for that client (for all years).

                 Because I was creating a new database, I didn't copy all the previous year sales over, so I just started with a 2013 Sales table. But then when I created the Portal in the Client table, I realized I don't want to have to do this every year if I have a separate table for each year, there must be a better way.

                 So, as I mentioned, I did create a layout with subtotals for month (which is great - can't do that in Bento as far as I know) and grand total which works for now since I only have one year in my table, but once 2014 comes along there will be trouble! And I figured out how to do a find on the year - but how do I put the 2 together (get the find onto the report layout)?


            • 3. Re: Yearly reports

                   Yes, keep all you invoices in one table. Don't try to put each year in a different table as that approach will cause all kinds of problems for you.

                   But with the data all in one table, there are two simple approaches that you can use to see the invoice data for all invoices of a given year and, if desired, get year to date totals from this data.

                   You can use a portal to invoices with a field in your layout's table set up so that you can enter or select a year. This can match to a year field in the portal's table and then, when you select or enter a year in the layout's year field, you see the portal update to list invoices from that year. A summary field that computes the total you want can be defined in Invoices. If you put that field on your layout next to your portal, it will show the the total for the invoices shown in it.


                   You can perform finds as I described in my previous post. The resulting report can show monthly and or yearly totals as needed using summary fields, sub summary layout parts and a trailing grand summary layout part. The same summary field can display both sub totals and a grand total simply by putting copies of this field in different layout parts.

                   This second option offers many differnt variations of the same basic method. In a sense, your found set wil fill the same role as your "collection" in Bento but be fully flexible as you are not limited to using such a report simply to view data from a specified year. You will also have the option of specifying more than one year or just part of a year.

                   You may find this tutorial on summary reports helpful: Creating Filemaker Pro summary reports--Tutorial

                   Note: this is an older thread that will not pop up in recent items if you post a comment to it. I recommend that you post any comments or questions about it here in this thread to improve the changes that I will see it.

              • 4. Re: Yearly reports

                     Thank you for the tutorial on Summary Reports - that was helpful. Okay, so I think now I finally see. All I have to do is go to the layout and perform the find based on the year, and sort by month and then I will see the report for that year. That is the part I was missing - how do I get it to show just one year and not the whole table. I didn't really understand the relationship between the table and the layout - it is starting to become a bit clearer now! i am finding that things are not as difficult as I expect them to be. 

                • 5. Re: Yearly reports

                       The interface between a layout and a table is called a "table occurrence"--the name we use for each of the "boxes" found in Manage | Database | Relationshps. To learn more about Table Occurrences and how to use them in FileMaker: Tutorial: What are Table Occurrences?