Formula Tool dealing with Dynamic columns

Question

I have a formula tool that adds up the data in 3 columns, but each month the names on those 3 columns will change. The columns are the last 3 months of data, so for example it would be Aug/July/June. When September closes those columns will now be Sept/Aug/July. I had the formula tool adding Aug/July/June but now June doesnt exist as its rolled forward a month. How can I get the formula tool to add those three columns up each month dynamically.

As part of the Dynamic Column renaming I do have it adding the word Miss to the beginning of each of those three months. Is there a way to key in on the word "Miss" to add the columns up?

ed_hayter · Accepted Answer

I went with a field info tool to get the names in order of appearance then use record ID to rename the months to their column position then do the calc based on the column position rather than name.

FinnCharlton · Accepted Answer

Hi @cjaneczko , you can do this by parsing the month names from the field titles, then labelling the latest 3 month columns with numbers. The formula tool is then always working with the same field names: