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?
Solved! Go to Solution.
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.
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 ","?
In for each , store currency data in variable of Number type. and then perform Math calculation.
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.
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.
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.
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.
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).
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