I would like to create a historical Project Status report. Realizing that data is live I was thinking that I could possibly create child records per month that would calculate the status at the time ...
Step 2: You will have 3 fields (to start) - Month ID (Text field) - Month ID Helper (Formula-Text field) - First Day of the Month (Date Field)
Step 3: In [Month ID Helper] field you will need to make it into a unique text string based on the [First Day of the Month] date field. I sometimes use something like this:
ToText(Year([First Day of the Month]))&"-"&Right(ToText(Month([First Day of the Month])+100),2)
The result for Jan 2017 will be "2017-01", but you can change this option to display any "ID" you'd like.
Step 4: You will then need to create or import the records for the time frames you are looking to report on. Could be a lot, Jan 2000 to Dec 2025. Easiest way is to create an excel list of all the 1st days of the month, and then just import that column.
Step 5: After you have imported the records and have a value in the [Month ID] field, you need to make that field the "key" field for the table.
Phase 2:
Step 1: to be continued... need to save and return later