8 Replies Latest reply on Jun 18, 2014 7:08 AM by philmodjunk

    Month summaries for previous years



      Month summaries for previous years


           Hi, I recently inhereted a customer after his old tech passed away. The tech had set up a very nice looking database in FP, with some nice reports.

           The customer has asked that I design a new report for him showing a summary of his sales for the month compared to previous years. I have been searching and reading about how to do this for a month, and haven't gotten very far. The previous tech has fields for just about everything, but I think my problem is I don't know how to get them properly laid out. When I try, I get all the line items for a single month, regardless of year. I have tried calculations in a script, subsummary reports, and jsut constraining found sets.

           If anyone can start me in the right direction, I would be eternally grateful.

        • 1. Re: Month summaries for previous years

               I have a report similar to this.  At the top of the screen is a dropdown (seasonal work-that extends from Sept 1-Aug 31), that gives you a choice of :

               This YTD

               Last Season


               Custom uses 2 Global Fields for Start Date & End Date.

               Here's the Script if you want to pick thru it:


          • 2. Re: Month summaries for previous years

                 If that does not work, feel free to describe the design of your database in much more detail so others have a better chance at making specific suggestiongs that will work for your specific database design.

            • 3. Re: Month summaries for previous years

                   Yea, sorry, that script is a bit much for me. I should have stated I'm pretty new to Filemaker.

                   So the database is pretty simple. It is 2 tables; customers and line items. Line items has a transaction date, parts, labor, tax, and total. I have some fields that are summaries of the latter 4, and a couple of sort fields, but I can create new ones pretty easily.

                   I would like to have a report that comes out with (current month) for every year, and just the totals for that month (parts, labor, etc.) So that the customer can compare how he did this year in this month versus last year and the years before.

                   I think my issue is twofold. I'm not sure what is the best way to pull all the records from the db and put them in a nice report, and I think I need to mess with the layout to get it to display properly.

              • 4. Re: Month summaries for previous years

                     So the database is pretty simple. It is 2 tables; customers and line items.

                     That would seem to be short by at least one table, Invoices:


                     But sticking with what you have, you can define cMonth as a calculation field with this expression if you do not already have such a "sort" field:

                     Transaction Date - Day ( Transaction Date ) + 1

                     Select "Date" as the result type. This calculation computes a date for the first day of the month for all transaction dates that fall in the same month and year. If you sort your LineItems records by cMonth, you will be able to group your records by Month and the groups will be in chronological order.

                     You can then set up a summary report on a layout based on LineItems. Remove the Body layout part and replace it with a Sub Summary layout part "when sorted by cMonth". Put your summary fields in this sub summary layout part and be sure to always keep your records sorted by cMonth when viewing data on this layout. You'll get one row of data for each month of the year. You can perform a find to pull up records in a specific range of dates to get a report for thi year to date, Last year, the last 12 months, etc..

                     For more on summary reports of this type: Creating Filemaker Pro summary reports--Tutorial

                • 5. Re: Month summaries for previous years

                       Wow, that feels amazing. I finally have a report that has all months. Found out in the process that I have to set the color of the font to see the output. One follow-up; if I just want to see the current month for every year, could I just modify cMonth in some way, like use get(currentdate) somehow?

                  • 6. Re: Month summaries for previous years

                         You wouldn't want to do that as that would bring data into the group of records you are summarizing from other months--producing an incorrect sub total.

                         But you could perform a find, specifying the month with a wild card * for the day and the year to find all records for a given month.

                         Using 5/*/* for example, would find all records for the month of May for all years.

                         You can then sort your found set as you normally would to see a monthly sub total for each.

                    • 7. Re: Month summaries for previous years

                           I think that's exactly what I want. The only caveat is that the user (or I) would then have to change that every month. Not the worst scenario, but I was hoping to avoid that.

                      • 8. Re: Month summaries for previous years

                             If you script the process, you should be able to stream line this, though you might find that requiring the user to select the month is not a bad thing as the month they want to specify may not be obvious when you are close to or just after the end of the month.

                             Here are some examples of scripted finds: Scripted Find Examples