How to do sum of column (Amount) in the list using Nintex workflow

[cdesai]

Hi,

I am having list (say list1) with column Amount

Title     Amount

abc     1,200

xyz     2,500

aaa     1,000

Now, I need summation of above 3 rows using Nintex WF. I have used collection variable and then looped the collection. However, Amount having comma is not calculated precisely. Seems, it is considered as string. Can anybody let me know better alternative ?

[soni_tumu]

Hello Chintan,

Can't you get the correct sum value when you use plus operation in Math operation action? And Amount in the above example is a column of type Currency right?

[cdesai]

Hi Soni,

Though Amount column is Number type in SharePoint. But, SharePoint put comma in Number field when saved.

[soni_tumu]

Yes Chintan,

But I do have a workflow I'm just summing up the Currency column data.

Can you let us know, What exactly are you trying to do. I will try to replicate the requirement and try to help you!

[cdesai]

Hi Soni,

I am summing up the Amount column in list1. I have used below action sequentially.

1. Query list to query list1 and taking ALL amount values in collection variable. lets say col1
2. For each loop to loop col1
3. in for each loop. using math operation to doing PLUS operation to sum amount. error comes in this step.
4. thats it.

[#6]

Try this, in the configuration of your for each you should have a Store In parameter. If there is a button there with 3 dots click it and set the return type to Integer, it'sprobably defaulting to String. If that is not available or does not work let me know and I'll try to replicate your issue.

[cdesai]

Hi Adam,

In math operation (in for each loop), I tried your step on vSingleAmtOfInv Integer variable and it has only 1 dropdown value in it shown in screen below.

vSingleAmtOfInv is Integer type

vAmtOfInvSum is Integer type

[#8]

That part looks correct, backing up further, how is your Query List action configured? Also, does it give an error or just doesn't calculate accurately?

[soni_tumu · Accepted solution]

Hello Chintan,

Can you try as follows:

Declare variables: ColID - collection type; ItemID - List Item ID type; Sum - Number type with default value 0

1) Query List: get the IDs to ColID variable of type collection

2) Foreach ColI, store result if ItemID variable. Here ItemID is a workflow variable of type List Item ID

3) Math operation

With this you can achieve the sum of all Numbers in your list into the Sum variable.

[cdesai]

Hi Soni,

I tried your approach earlier, but its error again. Because, Number column in your case should be having comma in thousand currency e.g. 1,200 or 2,000. Calculation in Nintex will surely work for hundreds currency like 350, 500 etc. So, in my case even numeric column (Amount column in my case) is having comma in currency which Nintex assume it to be string and not a numerics. Check below scenerio how SP does for hundreds and thousands currency. Number field in Number type. Now, here too Nintex will throw error while summing Number column.