cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Nintex Newbie

Add numbers in multiple comma delimited items

Jump to solution

I have a SharePoint List with a single line of text column that contains an array of comma delimited numbers and I need to add all of the numbers based upon their position in the array. For example:

Item 1 = 0,2,0,1,0

Item 2 = 1,0,1,1,0

Item 3 = 1,2,0,1,0

Item 4 = 1,0,1,1,1

I need the Nintex workflow to return the workflow to return a result of 3,4,2,4,1. I also need to only add numbers in specific categories, so I need to filter which Items are added based on the Category. I'm hoping someone knows of a way to do this that won't require creating a separate variable for each position in the array as we anticipate a longer list of numbers. I know that because the numbers are in a single line of text that when I pull the numbers I'll will have to use the Convert Value to convert them to a number variable before using the Math Operation to add them together for the total.

 

Any help is greatly appreciated.

Labels: (1)
0 Kudos
Reply
8 Replies
Highlighted
Nintex Employee
Nintex Employee

Re: Add numbers in multiple comma delimited items

Jump to solution

Hi,

 

It was not advised what version of SharePoint you are using but the attached workflow was created in 2013 but should work on any on-premise build above that as well.

 

If you want to test the workflow import it to your list and configure the first action which is the Query List action to suit your list and column name.

 

The workflow is easily scalable.

At present it will work as per your example with 5 columns to add.

To expand you will only need to add one math operation to the switch action, one more number variable to collect the total for the column and increase the number of loops in the loop action which is currently set to 5. 

The build string at the end will also need to be updated to include any new column total variables so your result is correct.

 

At present the workflow only pulls out single digits as per your example but this could be fixed by using a Regular expression action that extracts the numbers between the commas instead of using the Build string action I have used to extract the digits.

 

Hope this helps.

0 Kudos
Reply
Highlighted
Nintex Newbie

Re: Add numbers in multiple comma delimited items

Jump to solution

Thank you, Simon! I'll test this out.

 

I was assuming I would have to set up a loop for each number, but I was hoping there might be some way to avoid that as the array will have 25 numbers.

0 Kudos
Reply
Highlighted
Nintex Employee
Nintex Employee

Re: Add numbers in multiple comma delimited items

Jump to solution
Yes you will need to loop for each row but the workflow will process this very quickly. There are many ways to skin a cat and this is just my solution to the problem.
0 Kudos
Reply
Highlighted
Nintex Newbie

Re: Add numbers in multiple comma delimited items

Jump to solution

Simon, what should be captured in the Query List action? It looks like I only need to select the list with the array, add the appropriate filter, and populate the varColArray variable with the array column. But when I ran the workflow an error was posted to the History list that AddArray failed to run.

 

BTW I am running this on SharePoint 2013.

 

Thank you,

Theresa 

0 Kudos
Reply
Highlighted
Nintex Employee
Nintex Employee

Re: Add numbers in multiple comma delimited items

Jump to solution

Hi,
The query list action will capture all the values and put them into a collection variable which is just an array.
it will look something like this {0,2,0,1,0},{1,0,1,1,0}......

 

 It looks like I only need to select the list with the array, add the appropriate filter, and populate the varColArray variable with the array column.

Yes this is correct.

 

But when I ran the workflow an error was posted to the History list that AddArray failed to run.

It sounds like your environment may have other issues. Have you installed any SharePoint CU's lately?

https://blogs.msdn.microsoft.com/rodneyviana/2018/09/13/after-installing-net-security-patches-to-add...

 

 

 

0 Kudos
Reply
Highlighted
Nintex Newbie

Re: Add numbers in multiple comma delimited items

Jump to solution

Thank you, Simon, for the help, but Loops for this situation won't work in our environment - they timeout because of the amount of time between each loop and the number of loops necessary.

 

A team member helped me out with the following solution.

  1. Query List
    1. List: N611 Test Risk Register Matrix
    2. Filter: Title is not equal to Total Risks
    3. Field: String = collString
  2. For Each (beneath 1. Query List)
    1. Target collection: collString
    2. Store result in: txtString
    3. Index: numIndex
  3. Regular expression (in 2. For Each)
    1. Pattern: “,”
    2. Operation: Split
    3. Input text: {WorkflowVariable:txtString}
    4. Store result in: collSplitString
  4. For Each (beneath 3. Regular express, in 2. For Each)
    1. Target collection: collSplitSpring
    2. Store result in: txtsplitString
    3. Index: numIndex2
  5. Run if (in 4. For Each)
    1. Condition: if any value equals value
    2. Where: Workflow Data = numIndex2 > equals > Value: 0
  6. Math Operation (in 5. Run if)
    1. Calculation: Workflow Data = txtsplitString > Plus > Workflow Data = numSplit0
    2. Store result in = numSplit0
  7. Copy & Paste 5. Run if (beneath 5. Run if, not inside)
    1. Run if
      1. Condition: if any value equals value
      2. Where: Workflow Data = numIndex2 > equals > Value: 1
    2. Math operation
      1. Calculation: Workflow Data = txtsplitString > Plus > Workflow Data = numSplit1
      2. Store result in numsplit1
  8. Repeat Step 7 as many times as necessary to account for all String entries (ex. 10 numbers needs 0 – 9 splits) changing the Value by 1 more
  9. Set variable (beneath 2. For Each, ensure it is not any of the For Each or Run if)
    1. Set: txtCombinedString
    2. Equals: Value > numsplit0,numsplit1,numsplit2,etc.
  10. Now that you have the Update Item
    1. Update: N611 Test Risk Register Matrix
    2. Where: Title > Equals > Value = Total Risks
    3. Field: Select fields to update
    4. Zone Array: Equals Workflow Data = txtCombinedArrays

View solution in original post

0 Kudos
Reply
Highlighted
Nintex Employee
Nintex Employee

Re: Add numbers in multiple comma delimited items

Jump to solution
Hi,
You said you cannot use a loop but you use a for each action which is a loop?
0 Kudos
Reply
Highlighted
Nintex Newbie

Re: Add numbers in multiple comma delimited items

Jump to solution

Sorry, I typed the wrong word - it was the Switch Action that seemed to be causing the workflow errors. When I moved all of the actions within the For Each Action the workflow ran.

0 Kudos
Reply