Copy records from one to another table ?
I have 4 tables :
- Forecasted Revenue
- Realized Revenue
Project ---< Investment
Investment ---< Forecasted Revenue (1 Investment record = 6 Forecasted Revenue records (6 years))
Project ---< Realized Revenue (1 Project record = 10 Realized Revenue records (10 years))
Note : The 2 revenue tables are split because they have the same field names but for one they are calculated and for the other they are numbers.
When I create a new investment for a project, the first thing I do is set the forecasted revenue (budget) for the 6 years (i.e. 2015 -> 2020).
For the Realized Revenue for that same project, I will have to fill in the actual revenue (actuals) until 2014 but for 2015 -> 2020, I would need to copy the revenue that has been filled in in the Forecasted Revenue table.
This "copy" from one table to another could be done :
- either directly : when I enter the data in Forecasted Revenue, it copies automatically to Realized Revenue
- via a button script : when I push the button, it takes all Forecasted Revenues for all Projects and copies it to the Realized Revenue table
Note : the Realized Revenue cannot be a "Calculated field" because I should be able to overwrite the budget numbers once the actuals will be available
- What is the best option?
- What should I do to have the setup of the best option working?
Thanks in advance for your input!