In my workflow I would like to use the multi-field formula tool and refer to [_CurrentField_] with a prefix or suffix. I was wondering if it's at all possible, and if so how to accomplish this.
Example:
When applying a change to [Invoice amount] and [VAT amount], in the expression I want to refer to 'Format '+[_CurrentField_] how would I go about referring to this column. Is it at all possible?
Invoice amount | Format Invoice amount | VAT amount | Format VAT amount |
12,34 | DecimalComma | 12.34 | DecimalPoint |
Hi @RSM-LPolman
This can be achieved with the below formula in Formula tool.
[Format Invoice amount] + tostring([Invoice amount])
[Format VAT amount] + tostring([VAT amount])
Assuming the values are in Integer datatype and hence wanted to combine with String datatype, used tostring function to concat 2 strings to get desired output.
Many thanks
Shanker V
Hi @ShankerV,
The workflow I want to apply this on has about 10 columns each with the prefix 'Format ', I specifically requested the use of the Multi-Field Formula tool so it's less work writing the expressions, easier to maintain in future changes and less crowded in my workflow. Thanks for your suggestion however I am specifically requesting the Multi-Field Formula tool.
I think your intention is to modify a field by dynamically referencing a corresponding field. Like, the program goes to [Invoice amount] and immediately knows to reference the value under [Format Invoice amount], and then do the same for [VAT amount].
If I understood your requirement correctly, then I think the Multi-Field Formula Tool won't work here. What you might have to do is Transpose the dataset, do the necessary matching and manipulations, and then Cross-Tab the dataset back to its original form, like so:
This is the original data:
And this is the final data: