Multi-Field Formula | Is it possible to use [_CurrentField_] with a prefix or suffix

Question

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 amountFormat Invoice amountVAT amountFormat VAT amount12,34DecimalComma12.34DecimalPoint

Peachyco · Answer

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:

For RSM-LPolman CurrentField with prefix.yxmd

RSM-LPolman · Answer

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.

ShankerV · Answer

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