Create a Nintex Workflow Lottery

In the Netherlands it's a tradition to have a tombola (lottery) around Christmas time from promotional gifts that colleagues receive from customers, partner or vendors.

In the Ask the Expert Webinar Vadim used a Get Random Value using SQL and that triggered me to use this in a lottery workflow. We are going to create a workflow that will randomly draw a winner from a list of users that signed up for an item in the tombola. 
We got a few rules to play with: duplicate entries from users will be removed. Users that have already won something are excluded from the other drawings, everybody must be able to win.

A simple and fun workflow for the holidays, created in the middle of summer! Let's get started.
We first need to create 2 lists, one list that contains all tombola items and one list where the users can sign up.

Create lists

Create the list Tombola_items, this list will contain the items and contains

  • Title, default
  • Winner field, a people select field

Create a list named Tombola with the following fields:

  • Title, default
  • Tombola_item, lookup at the list Tombola_items
  • For me, people select field (or use the default field created by)

Create some random data in both lists.
In the picture below we added a few users that signed up for items.

Create some random data

Create workflow

You would expect that we were going to create the workflow in the list Tombola, but it's easier to use the Tombola_item list. We need to query the list in both cases.
Create a new workflow in the Tombola_item and drag the following actions on the canvas:

  • Query list
  • Collection operation
  • Collection operation
  • Execute SQL
  • Collection operation
  • Set a condition
    • Log in history list (left outcome from Set a condition)
    • Calculate date (right outcome)
    • Start workflow (right outcome)


The following variables we're not harmed during the creation of this workflow

  • colUsers, Collection
  • nrUsers,Number
  • nrRandomUser, Number
  • Winner, Single line of text
  • dateNow, Date and Time

Query list

Query the list Tombola and get all items where Tombola_Item is equal to current item.
Store from the results the For Me (or Created by) field in the collection variable colUsers

Query List

Collection operation

The first Collection operation is used to remove all duplicate entries that we have received. People are sometimes forgetful and enter twice.
Removing duplicates is easy as we only have to select the collection to retrieve and store the data in and the option Remove duplicates.

Collection operation

Collection operation

Next Collection operation is for counting the number of users.
Simply add the collection containing all users, select the count option and store the result in a the number variable nrUsers.

Collection operation

Execute SQL

Now the magic, this all happens in the Execute SQL action.
We use a connection to a SQL Server to have SQL Query executed. We don't query any database on the SQL server, just query our nrUsers variable for a random number.
The SQL Connection and SQL Service Account used in the image below are workflow constants.
We store the results in the number variable nrRandomUser.

Execute SQL magic 

Collection operation

The next Collection operation is for getting the user that represents the random number. We store that user in the variable Winner.

No, not a collection operation again

Set a condition

The set a condition action is used to check if the current winner has already won something.
As stated in the beginning everybody needs have a chance to win something and not only the Gladstone Gander's of the company.
We use the workflow variable Winner to check if there is already an item where the field winner contains this user's name.
If no item exist we update the item with this winner, if there's already an item we start the workflow again.

Update item

Just a simple update item. Select the variable Winner and place it in the field winner.

Calculate date

So the workflow needs to be started again, problem is that a workflow doesn't want to start that easy if the same workflow is already running.
Only one running workflow per current item is allowed.
There is a trick to overcome this; just wait a few minutes.
But as we don't want to wait ourselves, we let the current workflow start the new workflow with a delay of a few minutes. 
Set the Calculate date as followed: Select the option Use date when action is executed and set the Minutes field to 1.
Store the date in the date variable dateNow.

Let's calculate today

Start workflow

Select the current workflow from the list Workflow to start and add a schedule.
Fill in the variable dateNow at the Start time field and save the action.

Let's start again

Save and Publish the workflow.
If you run the workflow on an item in the Tombola_item list this will get all users for that item and select one as the winner.
And if this person has already won something the workflow will restart, as you can see below.
OK for that last one, we added only a single person to an item and that person was already selected. The workflow keeps on trying till he gets a new winner.

Keep on trying boy
If you liked this article be sure to have a look at Nintex Workflow User's Guide for more tips, tricks and hands-on assignments.



1 comment

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.


BleiCon can provide standard or customized Nintex trainings from end-user to administrators.

Discover what Nintex training fits your organisation


Meulenspie 21
4847 TK Teteringen


BleiCon is Microsoft Certified Partner


Bleicon is Nintex Workflow Pro certified

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.