convert currency string to number

[kbsitz]

Within my Workflow, I utilize a Query List action to get a set of records to obtain the values for a 'total' field and put them into a collection. Then use a For Each action.  The values are a string in currency format; ie:  $32,000.00.  I need to convert it to a number so that I can perform a calculation.

When I utilize the Convert Value action within the For Each action, I get an error: 

Value '$32,000.00' is not compatible with the selected output variable type Number (double).

I want to convert it to a number and keep adding it for each item within the collection to get the grand total.

Can someone offer some assistance?

[rohansm83]

Hi Kathy,

Your string contains currency symbol , so conversion is not possible using Convert Value action.

Workaround for this is to remove $ symbol from string using Remove Function,

eg. 

and then perform Math Operations.

[kbsitz]

When I tried this, I got an error:

Error Text:  Value 'fn-Remove($32,000.00,0,1)' is not compatible with the selected output

variable type Number (double).

So it seems, because my string is in currency format that I must remove both the "$" and any ","?

[rohansm83]

In for each , store currency data in variable of Number type.  and then perform Math calculation.

[kbsitz]

If I store the results on the For Each action to a number variable, then I get an error even though the original column type is 'currency'.  The For Each action only works if store the results to a string/text variable.

[rohansm83]

Hi Kathy,

1.  Query List result should be stored in variable of type  Collection.

2. For each collection variable and store currency column in variable of type Number.

[kbsitz]

Hello Rohan,

Yes...

1. Query List results are stored in a Collection type variable

2. For Each Action has the Target Collection as the Collection type variable above

However,

If I "Store result in" a Number type variable - then it errors.

If I change the "Store result in" to a Text type variable - then it works but then I have the whole conversion issue to worry about.

[rohansm83]

This is something strange. You need to contact Nintex Support team.

One more try,

Store Currency column value in variable of type text

Do not convert string to value , directly pass text variable in Math Operation action and check.

[kbsitz]

It is strange.  I originally thought that there may be a restriction in the For Each action to only handle text variables.

When I store the Currency column value in a text variable, the For Each action works.  But.... when I try to convert the string to a number value, I cannot because the format of the string has "$" and commas which cause parameter issues:  Results in error:

Value '$32,000.00' is not compatible with the selected output variable type Number (double).

[rohansm83]

Hi Kathy , have you tried this workaround,

Store Currency column value in variable of type text

Do not convert string to value , directly pass text variable in Math Operation action and check