Nintex Workflow – Get Random Value using SQL UDA
Recently, my colleague Brad Orluk and I participated in a Nintex Ask the Experts webinar (Click on the link to watch the July ’14 Webinar). This is where we go through a number of questions that were submitted prior to the webinar and also through some of the live questions we receive.
One very interesting question we received was how to have a number of approvers and then randomly pick them to be used for an approval task. They didn’t want it to go through any order. They wanted an approver to be picked at random.
As with all these types of requests, there are a variety of ways to do it. I made one such solution which I’ll describe here, but it is far being the only one. When I talked about it in the webinar, I showed it as a workflow solution. But I also mentioned that it is definitely something that could be made reusable in other workflows. This is why I’m sharing this post. It’s taking the process of getting a random value and putting into a reusable piece of workflow logic, called a Nintex Workflow User Defined Action.
VIDEO
User Defined Action
The UDA (User Defined Action) is actually quite a simple one. It’s only 3 actions. The action takes 2 parameters.
1. Input – Collection of Data (this is a collection of values that is passed in from the Workflow)
2. Output – Random Value (this is the parameter the UDA fills in after getting the random data)
This is what the UDA design looks like
As you can see, it’s not overly complicated. But these 3 actions can save you and your colleagues a lot of time, if they have to build his out themselves. Remember, it’s not the time it takes to add these actions and configure them. It also takes time to find a way to get the solution you need. Finding a way to get a random number in a Nintex Workflow etc. All that takes some time. A UDA like this is a great action to share with everyone in the organization who builds Nintex Workflows.
Firstly, since the UDA is quite generic, we expect the collection of data to be provided to us from the workflow that is calling it.
The first action get the number of values in the collection and stores it in a number variable that we will use later.
This “number of values” is used in the Execute SQL action. Why are we using a database action here? I needed to find a way to get a random number in Nintex Workflow. I could build a custom inline function to do this (like I here – Developing a Custom Inline Function ). But when releasing posts like this, I try to make no code solutions, since it’s easier to share out that way. Using the Execute SQL action, means I can get SQL to general a random number for me. The SQL query to do this was actually quite simple:
SELECT CONVERT(INT, ({WorkflowVariable:numCountApprovers})*RAND())
Even though we are using an Execute SQL action, we aren’t querying any database table directly. We are simply using SQL to generate the random number for us. In this case, we’re using the RAND() function and multiplying the result by number of values in our collection.
The result of the Execute SQL action is a number between 0 and the number of values in our collection (minus 1). This gives us an index we can use into the collection of data.
The final action in the UDA is another Collection Operation action that we use to perform a “GET” based on the index above.
The result is stored in the “Random Value” parameter. We now have a UDA that can give a random value from a collection of data that we give it.
Workflow
Using the above mentioned UDA is very simple.
Firstly, the workflow needs to get a bunch of data into a Collection variable. This can be done with a Query List action (if the data is in a SharePoint list), or through a Query XML action, Regular Expression action or even an Execute SQL action. All these actions will store data into a Collection variable. We then pass that into the UDA and we will get back a random value from that collection.
Requirements
The UDA does have a couple of requirements.
It is using an Execute SQL action. Even though we aren’t querying data in a database, we are still connecting to SQL. This means that a connection string and credentials are required.
The Execute SQL action uses Workflow Constants.
1. DBConnectionString – text constant that holds the connection string to SQL
2. DBQueryAccount – credential constant that holds the username/password to connect to SQL
If you want to use the UDA that is available in the download section below, you need to make sure you have constants with those names on your site/site collection/farm or if create the constants and update the UDA (Execute SQL) accordingly.
Conclusion
As with all UDAs, the purpose of this post is to share out some workflow logic that is hopefully reusable to many people. If you have suggestions or questions, please feel free to comment at the bottom on this post.