Before I venture too far into a project with FM, I'm curious what can be done in the name of keeping my db trim.
A portion of my db performs calculations on values that are entered into record fields. Each record also contains a date range that can span a number of years. Calculations for each record determine a value at the end of the current year, a value during each of the interim years and a value for the last year of the date range.
I had considered populating the values into a table with multiple fields based on years (2017, 2018, 2019...). However, the data is highly repetitive for a single record since the interim years for a single record have the same value. The only different values are the first year and the last year (for a single record). There would only be a few hundred records in total.
Is there a way to tailor a report or view on the results of the calculated values by Year (2017, 2018...) without actually having specific years listed in the db? I already have the starting year and the ending year (based on the date range) and the years in between can be easily determined (based on the date range).
Note that I will have to get a sum of all records - start, interim and end, year totals for more calculations later in the db. Is a seemingly redundant "Years" table the answer or is there a more efficient way that FM offers?