cancel
Showing results for 
Search instead for 
Did you mean: 

Queries, and Collections, and Loops, Oh My!

Nintex Employee
Nintex Employee
15 7 28.2K

Hello again!

I wanted to take some time and go over the Query List action as there has been a rise in questions from the community on how to leverage it. Not only the query, but what to do with the data after we query it. Let's dive right in.

Query List Action

The action is straightforward enough; query a list for specific results. An example could be all items where [Created] = {varDate} or [ID] = {varLookupID} or even just return all items (I would advise against this if possible). But how do we store the resulting data? What about if we get multiple results? If you know that the query is going to return only one item every time, then a normal variable (based on the data type it will be storing) will suffice. Otherwise, you are going to want to store your results in a Collection variable for every piece of data you want to do something with. So, if you are querying a list with 10 columns, and you only need 4 of them (to send a notification), you will need to create 4 collection variables to store the resulting data.

Collection Variables

A collection variable allows us to store a grouping of similar data types (a set of start dates, or item IDs) in an array so that we can iterate through it and utilize the data in some way. We may want to use the data to do calculations, or perhaps build a string and populate an email. So how do we do that? We can use a Loop, more specifically, a For Each loop.

Looping

We can use a For Each loop to iterate through our collection and get the corresponding data singled out. We want to start with one of our collections and store the first item in a more manageable variable. Below I used the titleColl and stored the result in itemTitle starting at the position designated by the workflow variable index. By default, you do not have to use an index, but I find it easier to manage in the event that I want to target a specific location within my collection. Also, it keeps my logic streamlined and easier to follow by someone else.

Next we need to get the corresponding data based on the where we are at in the loop. We can run these in parallel and use Collection operations to GET our variables.

Within each of our Collection operation actions, we want to get the data at the same index as the others and then store it in an appropriate variable. For example, if we want to get the item's Start Date, End Date, and ID, we would target each of those collections at the specific index and store that data in a workflow variable like so:

I am not going to go into details about the other operations that you can do with collection variables, but I highly recommend the blog posts that Paul Crawford​ put together on Collection Operations  and more specifically Collection Operation - Get .

Ok, so now we have the specific item details that we wanted, but what can we do with it? Short answer, anything! You now have the most granular data on that item, so here is where the "I need it to do [this]" happens. For example, if you needed to send an email notification to the [Created By] for each item, you can now do that and populate the item with specific information. We see a lot of this for reminder emails based on a specific date. Query a list based on "status" and then see if any item(s) need to trigger a reminder based on a due date.

In this example, I am building a string to put in an email. I grab the itemTitle and add it to the emailString each iteration:

Here is the list (feeling strange today :(

And here is the resulting email:

Final thoughts

One thing that I did not mention is to increment the index. This allows you to keep track of where you are in the loop and if needed, you can move back and forth using that variable. To increment index, simply add a Math operation action and increase it by 1.

I do want to mention that I have been exposed to a different way of looping through collections by Cassy Freeman​. I have been experimenting with this new method and while it will provide the exact same results, it does perform a bit better with larger queries. Perhaps we can dive into that next time and shed some light on the differences and benefits!

Until next time!

P.S. - I attached the .nwf file for anyone that wants to see it in action. I am more "hands on" learner and like to see things in action and tinker to learn

7 Comments
Automation Master
Automation Master

Great article for those new to looping! :)

Nintex Newbie

Excellent write-up.  This came in handy with a project I am working on with my client.  Great job Jesse!

Nintex Newbie

Hello,

Being a beginner with Nintex Workflow, I’m having a little trouble following the Query List.  The instructions above reference ‘index’, however I’m not sure how/where that is created.  Is it a workflow variable, and if so, what type is it (single line of text, number etc)?

Thanks

Automation Master
Automation Master

it's mentioned in text it's workflow variable.

it should be numeric.

Nintex Newbie

So incredibly helpful - thank you very much for sharing!

Nintex Newbie

I am using Nintex for O365. I have multiple email addresses with semicolon delimiter and I am using regex to split them and then for each loop to iterate through each one and add each email addresss in the new list as new item. My issue is, it is adding first value as [“emailid”, in the list and each email id ends with comma until first multiple item is done and next item after that gets added as below. How do I clean this up? Please help if you can. 

Nintex Newbie

I was able to use this example to create a site workflow for my business process. Thank you!

I did have another process where I have a list with the following columns:

1. serial numbers (SN)

2. users (people picker) - assigned to each SN. 

3. verified date.

I'd like to be able to send ONE email to EACH user that lists each SN and verified date of the SN they are assigned to, when the verified date is 14 days or older. 

For Example:

If today is 6/25/19

1234, Doug, 6/1/2019

2345, Doug, 6/25/2019

5678, Joe, 5/15/19

7894, Joe, 4/4/19

Once the site WF is run, it will trigger one email to Doug listing 1 serial number and date and another separate email to Joe listing 2 serial numbers and the dates. 

 

I know I can use the example in the blog to get something close but can't figure out how to send one email to each user. 

Thanks in advance.