Using macro with formula tool to replace field names

[andy3590]

I am trying to use a macro text box and action tool to update the field name in a formula, but am having trouble with the syntax to update the formula appropriately.

Simplifying here, but here is the problem in essence:

I have data with four fields - Customer Name, product level 1, product level 2 and product level 3. What I want to do is input via a macro whether the output should pick product level 1, 2 or 3.

Formula is as follows: IF !isnull("TBD1") THEN "TBD1" ELSE "TBD2" ENDIF, and I am trying to use the macro input and action tool to replace "TBD1" with the field name I enter as text (could be product level 1, product level 2, or product level 3). I have the action tool set up to replace string "TBD1" and "TBD2" and my macro set up with input of [product level 1], but when I run, the macro is not replacing the field name correctly in the formula tool.

Please let me know if any clarifying questions and thanks in advance for any help!

[fmvizcaino · Accepted solution]

Hi @andy3590 ,

Here is one example on how to change a formula within a macro.

The main thing to configure is the action tool and you need to be careful to not remove something that will make your formula invalid. 

In my example I'm replacing the column name inside the brackets also.

Best,

Fernando Vizcaino