Variable input macro

[Gottfried]

Hello all,

I have to deal with two kinds of input files which differ only by two of their columns. One version has columns [amount] and [debit/credit] taking either value "D" or "C". The other version has instead columns [debiting amount] and [crediting amount], one of which gets a zero value for each record. Accountants in the place, you see my point... I made a small workflow that handles both versions and in all cases turn them into the first one which I like best, plus a couple of additional cleansing/preparation steps. I just have to plug my Input_Data tool of either version to a Union tool that happens to be the first step in my workflow (I use it to merge the input data with a dummy template table which has both types of columns at once...). So far so good.

I would like to turn my workflow into a standard macro. But then I need to start it with a Macro_Input tool which determines the structure expected by the macro's input pin (based on the "Template Input" in the configuration). Thus, I cannot make my macro compatible for both versions of the input files. If I use e.g. version one as template input when designing the macro, I'll get an error message that column [amount] and [debit/credit] are missing and vice versa.

Is there a workaround to solve this yet sticking to the choice of using a macro (as I could of course copy and paste the piece of workflow as a container to all workflows needing these preprocessing steps). In other words, is there a way to make a macro flexible about the columns/fields of its inputs?

(Note that the case I mention is just one instance of many possible similar scenarios you can easily imagine.)

Many thanks in advance!

G.

[DavidP]

The easiest option would probably to be to use a dynamic rename tool that renames the 2 fields in each case to something that is known. If the field names are always the same, you can use a text input field with the standardised field names and connect it to the right input of the dynamic rename.

This then forms a predictable input for your macro.

[Gottfried]

Thank you David.

I think you tackle the processing of varying input fields in the workflow. But this is fine with me. The issue I could not fix so far is rather about turning the workflow into a standard macro. Because a macro has a Macro_Input tool in which specific fields/columns are defined, I cannot plug differing file structures to the macro without getting an error message.

(the configuration of this tool forces the list of columns expected and accepted as input to the macro whereas I would like to be able to plug in two different kinds of input tables with two - slightly - different sets of columns)

In my example, if choose the "debit amount / credit amount" format to configure the Macro Input, then when I plug an "amount / debit_or_credit_sign" input file to the macro it fails. And vice versa. The two input file versions do differ by the naming and presence of these two columns. I do not have this problem when working directly without a macro as I just plug the input file to a tool that does not need any constraint on incoming columns. But then to be able to reuse it in many different workflows I have to copy and paste this sub-workflow (possibly containerized) into all of them, instead of just making a macro out of it.

... or did I misunderstand your advice?

[DavidP · Accepted solution]

Yes, so the idea is to ensure that the column names are the same going into the Marco, regardless which of the 2 files you.

Using dynamic rename before the macro is one way of doing this.

Another way is to tick the option in the input data tool that says 1st row contains data. That way the data is loaded with generic field names Field1, Field2, etc. This way the field names are the same whichever file you choose

[DavidP]

If you send an example of your data files I can show you what I mean.

[Gottfried]

Thank you David,

A combination of unnamed input fields (field names as first row) plus the dynamic rename tool did the trick.

Thank you for your help!

Best regards,

Gottfried