Macro - Prompt for specific columns

[xinruitang]

Hi all,

At work we have a very long workflow that we use repetitively that I would like to streamline and change it to a macro. The workflow only requires three columns: "Customer ID", "Date", "Value". 

As I try to make this macro as easy as possible for people to use, I want to minimize the modifications needed to the source file prior to running this macro. As the sources files usually have way more columns, and the columns might be in various names. For example, the Customer ID column might be called "Customer name", "Customer", etc, and the Value column might be called "Amount", "Revenue", etc. Is there a way I can prompt the users for the columns they think fit? The below are my ideal macro input interface:

Please select the column for "Customer ID (String)"

[Drop down of source columns]

Please select the column for "Date (Date)"

[Drop down of source columns]

Please select the column for "Value (Double)"

[Drop down of source columns]

I tried using the "Update Select with Reverse Field Map" function and it doesn't seem to be servicing the function that i wanted. Basically i want no matter what the input names are, the output names should be "Customer ID", "Date" and Value". Please let me know if you have any idea how to tackle this. Thanks! 

[MichalM · Accepted solution]

@xinruitang 

Have a look whether the attached works.

- I created 3 dropdowns which are dynamically populated with fields from a data stream connected to the macro

- The selected values will be fed into Dynamic Rename and renamed accordingly

'IF [_CurrentField_]="'+[#1]+'" 
THEN "Customer ID"
ELSEIF [_CurrentField_]="'+[#2]+'" 
THEN "Date"
ELSEIF [_CurrentField_]="'+[#3]+'" 
THEN "Value" 
ELSE [_CurrentField_] 
ENDIF'

[xinruitang]

Thank you @MichalM! This works like magic. 

[DiganP]

@xinruitang Attached is another example. Its a different approach from @MichalM  🙂