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 :
Custom uses 2 Global Fields for Start Date & End Date.
Here's the Script if you want to pick thru it:
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.
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.
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
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?
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.
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.
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