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
Solved! Go to Solution.
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:
Thank you for your help @JoeS !