How to update field name in a macro by action tool

[florijn]

I am trying to creating a macro to calculate moving average based on the group of a field. Work is like the following and attached.

The macro itself works. But in a test workflow, I just changed the field names and the workflow gives a error: Error: Moving Average (5): Tool #2: The field ""Level"" is not contained in the record. I used in action tool Update Value in action type to replace a specific string "Company" in the macro text input. My understanding is that the updated field name from the test workflow is not in the macro input data. Is this right? Can anyone help how to set up the action tool for the field name update?

Level	Amount
A	1
A	2
A	3
A	4
B	5
B	6
B	7
B	8

Thanks

[DavidP]

Hi @florijn 

Edit: I just realised that you are updating the Tile tool already, so it's just the RunTot_Value field that would give you an error. My solution below will still work, but you could probably also do it with a Dynamic Rename tool. I'll have a look now and post another option if I get it working.

Looking at your macro, and specifically the input to the formula tool as shown below, The formula tool relies on the fact that there is a field [RunTot_Value]. Similarly, the Tile tool relies on the fact that there is a field [Company].

If you change these fields upstream to something else, they won't exist and one or both of these will give an error.

So to fix it, you either have to update the Tile and Formula tools with Action tools as well (which might become a little messy), or do something else to ensure that the field names coming out of the Running total tool is always constant and then use those in your Tile and Formula tools.

You can do this (Option 2) with a Union tool and some static field names, as shown in the 2nd screenshot below - amended macro also attached.

[DavidP]

Here's the Dynamic Rename option

[florijn]

Thanks. @DavidP 

I tried to use both options, one by union and another by dynamic rename, to change the headers. But both gave me an error for the test data in the original post. Can you advise where the blank header is from?

[florijn]

Sorry, I re-do the test after selecting right fields. But the error "The field Level is not contained in the record" is still there.

[echuong1 · Accepted solution]

The reason for the error is because you are fueling the check box options with the macro input - therefore it only has metadata for fields called "company" and "value." To get these to update with the data actually available in the dataset, you'd need to create a chained app.

Alternatively, an easier way around this is to enable the field map in the macro input. This will allow you to map the fields present in the original dataset, to the field names that are coded in the macro. The output will have standardized names - whatever you built in the macro, in this case "company," "value," and "Moving Average."

Similarly, the user will be prompted to choose the field that they would like to represent the company and value fields. This will be fueled by the data present. I've outlined two examples in the attached.

Hope this helps!

[florijn]

Thanks @echuong1 

I did not notice the check box to show field map.

@DavidP 

Based on your solution, I further made some changes. In short, I changed the headers at the beginning and end of macro workflow to make sure the transform process using fixed headers without any impact from incoming metadata. Thank you again.