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:

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.