Nintex Workflow – Collection Variable

It seems that the Nintex Workflow Collection variable can be confusing, so I thought I’d try to shed some light on it. This really comes into play, when you start using the Execute SQL which currently only supports returning one column, or if you are using Query XML and the XPath.

If you have multiple collection variables, all with the same number of entries in them, you will need a variable for each type that’s in the collection. In this example, we will just have 3 collections of text strings. You will also need a number variable for the index into the collections.

Workflow Variables
I tend to prefix my variables with their type :
collFirstnames (Collection)
collLastnames (Collection)
collCodes (Collection)
textFirstname (Text)
textLastname (Text)
textCode (Text)
numIndex (Number)

Iterating through the Collections
The first thing to iterate through the collections is one For Each action. What we will be doing, is iterating through the collection of Firstnames, and each time through we will store the Firstname and also the index where that Firstname resides.

You can see from the configuration of the For Each action, we are storing the Firstname value in the text variable “textFirstname” and also the index in the number variable “numIndex”.

Now into the For Each action, we add 2 Collection Operation actions. One for the Lastname collection and the other for the Code collection. In the configuration of these actions, we specify the collection we want to use, the “Get” operation, the index which is what we get filled in form the For Each action, and the text variable we want to store the result in.

Now through each iteration of the For Each loop, we will have the firstname, lastname and code, in the variables textFirstname, textLastname and textCode.

We can use it anyway we see fit. In the example I provide, I am logging these values into the Workflow History List, just to make sure it works.

Where’s the data?
If we run this workflow now, we will get nothing because the collections are empty. So why would we get into a situation like this?

Example : Execute SQL

If you have a table with ‘n’ number of fields and you need to query that table to retrieve multiple fields (and multiple rows), you would need to use multiple Execute SQL actions.

eg.
Table – “tblUsers”
Field – “Firstname”
Field – “Lastname”
Field – “Code”

if you need all the Firstnames, Lastnames and Code, then you would need the following queries, one for each Execute SQL action :
SELECT FirstName from tblUsers order by Lastname
SELECT LastName from tblUsers order by Lastname
SELECT Code from tblUser order by Lastname

To make my workflow easier to visualize, I like to use Run Parallel Actions where I can. In this case, I have 3 branches, where each branch contains an Execure SQL action to get the data I need.

Now your Collection variable operations should work successfully and get the data you need.

xample : Query XML
We’ll start with some XML that you have received either from a Web Service, or some other source.

To get the Firstnames, Lastnames and Codes, you will need 3 separate Query XML actions, each of which would execute it’s own XPath.

Firstnames : //xml/Users/User/Firstname
Lastnames : //xml/Users/User/Lastname
Codes : //xml/Users/User/Code

Each of the Query XML actions would store the results in the collection variable and then we can process the collections as we did above.

The workflow ends up looking like this :

Other Collection Operations

Add – add a value to a collection into a particular index
Remove – remove a value from a collection at a particular index
Count – number of items in a collection
Get – get a value at a particular index (we use this in the workflows below)
Exists – does a value exist in the collection ?
Sort – either ascending or descending
Pop – pops the value off the end of the collection into a variable
Join – join all the values in a collection with a delimiter and store it in a variable (John;Mary;).

Important files
Collection Variable Workflows