Multi field Mapping Macro Input issues

Question

I am having a little difficulty with creating a macro that will be used on multiple different data sets.

Input data - Could have between 5 and 50 fields and the field names will be different each time

The Macro will require 4 as a minimum and the formula in the macro is set up to take 8. The formula in the macro is driven from a date column and two numeric columns, the rest of the columns are strings used for grouping.

Issue 1 - Errors when running macro

Parse Error at char(0): Empty expression (Expression #1)
    Parse Error at char(0): Unknown variable "Customer" (Expression #2)

Issue 2 - how do I set up macro to still run if only 4 input columns selected (i.e.some drop down options left empty)?

Issue 3 - is it possible to relabel the macro output to same column names as input?

Note i have simplified macro function to a single formula to show cleaner example

As always your help is greatly appreciated! Thanks Community

Multifield calculation.yxmc

JoeS · Accepted Answer

Hi @Nmetzgen1

I have built out the answer to the first two questions.

I switched around the actions within the macro to use "Update Value with Formula"

Once I had done that I used the formula:

IF !ISEMPTY([#1])
THEN
"["+[#1]+"]"
ELSE
'""'
ENDIF

This checks to see if none is selected in the drop down and will populate the formula expression with two quotes "" AKA leaving it blank

As a side I noticed in your original action you had it set up to replace the whole expression.

You need to be careful with this in case a field contains a space. As it is literally what appears in the drop down that goes in the expression, thus rendering a syntax error if your field has a space in it.

I updated your actions to only replace the name of the fields and not the square brackets, like below:

Issue number 3 may well take a fair redesign and could be done a few different ways depending on what it is you are doing in your overall macro in order to suggest which is previous, and also what you want to do with fields that aren't being mapped (random others that may be on the file).

Different ways could be:

* Instead of mapping the fields to fields being used in a formula, you can use actions to update each tool where that field is being used (AKA the Filter for [Year] and summarize for all the fields etc)
* If you didn't need to keep fields on the file that were not being mapped, you could map them to unused field names e.g. NonRepeatableYear, then configure your tools to use these names, and rename them in the end using a select tool.
* If you did need to keep them, you may need to do something more complicated, and again would depend a little on your overall process as to what I would recommend, and its going to be fairly hard to describe, sorry!