Passing Field Names with Non-alpha Characters into a Formula Field

[daniel_mmi]

I'm building a macro that reads the fields present in an input file and uses them to populate a dropdown menu. The resulting selection is passed into a Formula tool. If the original field name contains no spaces or strange characters (-, /, maybe more), it works perfectly. If the field *does* contain those characters, as in "E-Mail" or "Zip Code", the result will be passed as "E" or "Zip" respectively. Because there *is* no variable/field named "E", that's as far as it gets and throws an 'unknown variable' error.

I'm sure there is a simple solution, but I'm stuck *so* close to having a working macro.

Thanks!

Daniel Taylor

[chris_love · Accepted solution]

Hi Daniel,

Check what you are replacing in the Formula, don't replace the [Field], just replace Field. You'll need to leave the square brackets so that fields with spaces in get them properly.

I hope that makes sense.

e.g. 

IF [Sales] > 100 then 'High' else 'Low' endif

if you replace [Sales] with Sales Value it becomes

IF Sales Value > 100 then 'High' else 'Low' endif

which doesn't parse, so make sure you only replace the text Sales (not including the square brackets)

Chris

[daniel_mmi]

Thank you, that worked perfectly!

This is sort of a separate question, but in this case, is there a way I can write the expression such that it won't fail when it gets a [None] selection from the Drop Down?

[chris_love · Accepted solution]

it depends what you want to happen when there is a None section, it's certainly possible but more complicated. In this case I'd use an action to "Replace with Formula" and then craft individual formula to add into the tool depending on the selection. It's harder because you need to watch your quotes.

e.g. if the formula was as above but we wanted to set everything to Low when the Dropdown was "None" I'd use a Replace with Formula action with the following formula:

IF [Dropdown] = 'None' then '"None"' else 'IF [' +[Dropdown]+'] > 100 then "High" else "Low" endif' endif

Note how I'm mixing up Single and Double quotes to ensure they parse correctly when they get passed into the Formula. 

Good luck!

[daniel_mmi]

'Update Value with Formula' -> Perfect. Much simpler code, too.

if isempty([#1]) then '""' else '['+[#1]+']' endif

Thanks again. I'm just finishing building a macro that will add a set of our standard fields at the beginning of an input file, with dropdowns for field mapping, populated by the fields in the macro input. Not too shabby for my first week using Alteryx. Quite a product you guys have, here.

[chris_love]

Holy **bleep** - you've been using it a week! Kudos to you my friend, thats some learning curve right up to macros already. Great work.

Totally agree with you on how great a tool it is, I've been a customer for years and it still surprises me what you can do.

[Itay_K]

Hi,

Could you please upload your solution?

Thanks!