You can do that with a script.
The script should first find the records that you want to have
Then Loop through your record and set a Average
You can do ALTER statements in sql databases but I don't know of equivalent Script Steps in FileMaker that allows for on-the-fly schema changes.
You're doing a rolling average database. I would make generic fields to contain the results: prior_3_mo_avg, prior_6_mo_avg, and so on. You're basically eliminating the need to having the 4-digit year in the field names. So, bottom line is that you need generic/reusable field names.
Another idea is to always store the results in an archive table for charting/trending purposes.
You can not edit field or table definitions with scripting.
I guess it would be possible to:
1. Leverage a plug in like Monkeybread Software - MBS FileMaker Plugin 6.3
2. Use ODBC and SQL
Never done it so i can't confirm the veracity of either option.
I would suggest that you script the process of creating the averages and write them to a table.
1 of 1 people found this helpful
If I understand what you are trying to accomplish, it might be easiest/best to setup a separate table in which you store monthly totals. That way you can continue to add records not fields as time moves on. Your calculations in the parent table (Products?) can be dynamic with parameters set based on some global fields that you change based on what you want to see. For example, selecting 2015 in a global could show the average of monthly totals from 2015 for the current product.