Using SQL Query in Nintex Workflow

In this article we show how we can use the SQL query action in Nintex Workflow to retrieve data from a SQL database and use it in a workflow.

 

The example workflow will look up the price of a single item in a database and multiply this with the requested amount from a SharePoint list and store the result in that SharePoint list.
As database we use the Northwind demo database from Microsoft that can be downloaded from Codeplex. Add the database to the SQL instance and add an account that will be used to retrieve the content from the database.

Create list

Create a list in SharePoint that contains the following fields:

  • ItemID, Textfield 
  • Amount, numberfield 
  • Total, currency

Create workflow

Create a new workflow in the list and drag the following actions on the canvas

  • Execute SQL
  • Math Operation
  • Update item

 

Variables

The following variables need to be created

  • PricePerUnit, number
  • PriceTotal,number

 

Execute SQL

Open the execute SQL action to configure this.
The Execute SQL requires two settings, the Connection String field and the Query Field.

The Connection String field needs to be filled with the parameters required to connect to the database.
In our example this is the connection to the SQL server DEV-SP2013 and the database northwind: Data Source=DEV-SP2013;Initial Catalog=northwind;Integrated Security=SSPI

We enable Use Windows authentication and add a constant.

The Query field is used to select which information needs to be retrieved; we want to get the field UnitPrice from the table Products when the ProductID is equal to the SharePoint listfield ItemID.
All fields in a SharePoint list are available in a workflow so we can connect this to the SQL query like follows: SELECT UnitPrice from Products where ProductID={ItemProperty:ItemID}.

Store the result in the variable PricePerUnit.

 

Tip: Use the Run Now option to test your Query

Math Operation

The result that we receive from the SQL Query needs to multiplied by the requested amount. We use the Math Operation action for this.

Configure the Math operation like followed:
Select Workflow data and then the PricePerUnit variable
Select Multiply by
Select List lookup and then Current item, followed by the field Amount
Store the result in the variable PriceTotal.

Set Field value

We then return the calculated price in the current item with the action Set Field value.

Open the Set Field value and configure as followed:
Set the field Total equals to the workflow Data variable PriceTotal.

Save the workflow and publish to make it available. When the workflow is executed the request price is retrieved from the database and multiplied by the requested amount.
You can see below that the total price is set at €216,00 euro (12x 18.00)

For most users working with ID's isn't user-friendly. In one of the following articles we will use a BCS Connection to retrieve the name of the article instead of the ID.

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

Leave a comment

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

NINTEX TRAINING

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

Discover what Nintex training fits your organisation

CONTACT

BleiCon
Meulenspie 21
4847 TK Teteringen

PARTNERS

BleiCon is Microsoft Certified Partner

CERTIFIED

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.

Ok