Calculate Field Name
I am looking for a simple way to calculate which field to use in a calculation based on the name of the field being used. Let me give an example.
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. I want to do some calculations on these fields without having to create individual calculations manually for each month - just 1 formulae that can be pasted into each calculation.
Say I wanted to measure sales against FC as a percentage and have the fields created as FC_percentage_period_01, FC_percentage_period_02 etc. I was thinking I can use 'right(getfieldname(self),2) to identify which month the calculation is for but can't figure out if its possible to use this number to tell it which other fields to then reference without using complicated case or if statements for every possibility.
This way if the calculation was pasted into a calculation field that ended 01 it would reference all the other relevant fields ending 01, if pasted into a calculation filed that ended 02 would reference all the other relevant fields ending 02 with no change to the formula and so on.
The percentage example above is simple and admittedly could be typed reasonably quickly anyway but some other calculations I have use multiple embedded ifs referring to dozens of fields each time and take substantially longer to create or amend.
I'm hoping this is possible and that someone can point me in the right direction!