Ensure Fields Crew Macro "&" Symbol Causing Error

[JPMurphy91]

Hello,

I'm currently using the Ensure Fields Crew Macro to add fields.  See Macro attached.  One of the fields I need contains an "&" symbol and it seems that the macro isn't reading it as an typical alphanumeric character and is giving me the below error. 

Is there any way to alter the macro so it will accept the "&" as part of the field name?  

Thanks!

[RishiK]

@JPMurphy91 

You could open the macro and see if this can be modified however, it is the "&" that is causing the issues.  Is there no way the field can have a "_" in replacement?

[Claje]

Hi,

I've attached a corrected version of the macro that should work as expected.

I'll post a detailed explanation of the change a little later today.

[Claje]

Hi,

So the issue with the original macro is that it is updating the raw XML behind the tools - which is the right way to do what this is trying to do.  However, XML has a number of metacharacters that need to be escaped, and ampersand & is one of them.  Alteryx designer does this automatically behind the scenes, but when configuring an action tool like this, you have to do it yourself.

Fortunately, there's a great function in alteryx already called EscapeXMLMetaCharacters which can handle this for us.  So I updated the action tools to escape the xmlmetacharacters in our function, and that should resolve this issue.

[JPMurphy91]

Hello,

Thanks so much for the updated macro and the explanation.  I'm currently attempted to implement the macro into my workflow and it keeps saying "At least one valid field must be selected."  Any idea what the cause of this error might be?

Thanks again for all your help.

[JPMurphy91]

Hi RishiK,

The reason the field can't use "_" as a replacement is I would have to open hundreds of excel spreadsheets and edit the columns to be "_" instead of "&".  I was attempting to use dynamic rename to solve this issue, however this lead to a multitude of other errors.  

I believe a user below has provided a working solution, so luckily I won't have to dive into that any deeper.  

Thanks for your help and response!  

[Claje · Accepted solution]

Hi,

My apologies - the initial version had the xml metacharacter escape in the wrong position.  I've updated it and believe it now tests out for all functionality of the initial ensure fields macro.

[JPMurphy91]

Perfect.  Thank you!