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

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

Macros

Accepted answers

chris_love

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

chris_love

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!

All comments

chris_love

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

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!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335