I have multiple fields spanning a time span of 36 months covering Budget, forecast, orders invoiced etc. These are numbered sequentially with the last 2 digits referring to the month in question. i.e. FC_period_01, FC period_02 etc
This sounds like you have used multiple fields where a better design would use multiple records--probably multiple records in a related table. This can be a much more flexible design and can also eliminate the problem with trying to reference the correct field. And a single calculation can be used that computes a different total for each month by using a different record for each month.
But when one does choose to use indirect field referencing, you can use:
GetField ( "table::field" ) to reference the contents of Table::Field, any text calculation that evaluates to the correct combination of a table occurrence name, two colons and the field name can be used in place of the quoted text in my example.
That's the "read" capability.
Then Set field by name can use the same type of calculation as the first parameter to "write" to a field by evaluating the text calculation to determine the table::Field reference needed to modify a field.
Other useful functions used in combination with these:
Get ( ScriptParameter )
GetFieldName ( Table::Field )
Get ( activeFieldName )
Get ( ActiveFieldTableName )
Get ( LayoutTableName )
It was the getfield step I hadn't seen so this has put me back on track. In combination with the let function too this works perfectly and will save considerable time in some areas.
I note your point on the multiple fields versus multiple records in a related table. Wouldn't it make a relatively simple layout like the one attached quite complex to join and map? Maybe I need to experiment to see the benefits...
What you show is often referred to as a "cross tab" report. Yes, the layout will be a bit more complex as you'd need a row of one row portals to show the data form different records in columns in this report. But many other parts of your database design becomes simpler and more flexible so you usually come out ahead in the long run by using a set of individual records in a related table instead of a group of fields all in the same record for this type of situation.