Nintex Forms Enterprise enables your forms to connect to external data sources, such as SQL databases or web services, extending their reach by automating the inclusion of relevant information. In this post I will explain the power of SQL Request Control to query warehouse data from an external SQL Server data store in order to build a Product Order Form.
The SQL Request control simplifies connecting to a SQL Server database by configuring basic information such as Server Name, Database Name and Authentication mode (Anonymous, Windows and SQL). This Control supports multiple display formats similar to Nintex Choice Control - Option buttons, Checkboxes (Multiple Selection), Dropdown list or Text/Label. The SQL Request control leverages the SharePoint Secure Store Service in order to protect the SQL Credentials, which we would also recommend are configured under least privilege principle. Simply enter the target application id stored in Secure Store Service containing the credentials. For more information about Secure Store Service on SharePoint, see the Microsoft article Configure the Secure Store Service in SharePoint 2013.
For this Product Order Form, we will be displaying a list of products as Option buttons available for selection. This form will use the Northwind database with a stored procedure "GetProducts", specified in Stored Procedure Name field. This stored procedure returns a list of items ( Product Names and Units in Stock) from the Products database. The "Display Field" property allows designer to specify which result column to be displayed on the form. The "Value Field" allows to set the column name, value of which will be retained when an item is selected. For the order form I have selected "ProductName" column values to be displayed on the form and "UnitsInStock" (available units in warehouse) column values to be retained when a product is selected.
The "Run Now" button in the ribbon displays the Run Now dialog box. This dialog is designed to test database connection and test the query at design time.
The Test Result table displays query execution results.
The output from this control will be displayed as Option buttons as below.
Now when an item is selected the Units in Stock will be retained as "Value Field". You can now add a calculated value control on the form to display this value. Using parseLookup() runtime function as the "Formula" of calculated value control the "Value Field" information can be extracted.
The simple order form built using above:
You can now introduce other Nintex Form features such as custom validation/ rules to build smart forms. In this case I have added a simple business rule to avoid ordering product if the order quantity exceeds the units available in warehouse.
As you can see, the SQL Request control opens up a number of valuable scenarios where you may want to bring data into your forms directly from a SQL database without encoding it within a workflow, or managing another set of reference data. Over the coming months, we will be adding additional capabilities to the SQL Request and Web Request control to further enhance the capabilities for you to use in your Enterprise Forms.
