Still working on converting a FileMakerPro database and reports to LibreOffice Base…
The basic issue is that FileMakerPro has the concept of “sum of” fields for reports. (Maybe also “average of,” etc.?) If your report has detail rows with values, but then the report is broken up by category,
Here’s the setup.
- DayTab has one row per day with a date and a value.
- MonthTab has several values for the month including first and last day of the “month.” The definition of “month” is fluid since it depends when the given values are recorded. That varies around a bit because they don’t happen on weekends or holidays. And that’s usually somewhere around the 20th of the month.
- MonthReportQuery pulls the values from MonthTab and the sum of values in the appropriate entries in DayTab, plus several calculations on those fields to compute perfornance for the month.
So far so good. It took a bit of fancy footwork to get the MonthTab sums right, but it works.
But here’s the rub. I also need a running total for the year. (Where “year” isn’t a calendar year, it starts in April, another minor complication.) In FileMakerPro I just define some “sum of” fields that automatically sum the computed fields and the sum resets at the start of each year.
How to do the same in Base?
I figure I could make a RunningMonthReportQuery that pulls values from MonthReportQuery plus sums all of the previous MonthReportQuery entries for the relevant fields.
Or maybe adjust MonthReportQuery so that it, itself, recomputes all of the running totals after figuring out where the start of the “year” is. But both of sound like there’d be an awful lot of work recreating sums from raw data instead of using sums of sums.
Is there some clever Base way to do this kind of thing that I’m missing?
