Skip to main content

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.

100928_pastedImage_2.png

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.

 

100929_pastedImage_8.png

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.

 

100930_pastedImage_13.png

The output from this control will be displayed as Option buttons as below.

100931_pastedImage_18.png

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.

100932_pastedImage_25.png

100933_pastedImage_30.png

The simple order form built using above:

100934_pastedImage_35.png

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.

100935_pastedImage_52.png

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.

Hey Christine,

nope.  Unfortunately at this stage, the SQL Request and Web Request controls do not work on Nintex Live published forms.

Vadim

Nintex Forms SQL Connect with Oracle DB? Any Updates regarding thi stopic?

If we need to populate data from Oracle DB on our Nintex Forms.

Thanks.

We still use InfoPath which, like Nintex Forms, can connect to SQL Server. So I simply created a Linked Server in SWL Server to Oracle, then I do all of my queries through the Linked Server. Info Path (and Nintex Forms) has no idea it’s getting or writing Oracle data, it just sees SQL Server. The way you get data in and out of Oracle it to set up a connection to SQL Server then send it an OpenRowset query. Google that, it’s easy. You create the Oracle SQL, then wrap it in quotes and then run a Select out of THAT. I can query Oracle straight from SQL Management Studio, as long as I have that Linked Server. It does require a real SQL Server installation, Linked Servers aren’t part of the freebie SQL Server.

Anyway, if you company has SQL Server too, this is an easy way to make it happen.

Sent from my iPhone

Thanks Paulie, but i'm worry about performance, what do you think?

Performance is difficult to comment on, not knowing anything about your environment. Just try it, set up a Linked server (Give it a name, maybe the database in oracle, that’s what I do), connect it to Oracle and give it a go. For us, running SQL server 2008 and oracle 11-something, a select query comes back as fast as going to oracle directly, but it’s not a lot of data.

To query Oracle through a linked server in SQL Server, you’ll see to create an OpenQuery, connect to SQL Server as if you’re running a normal SELECT statement, and execute it to get back a recordset. The linked server will take the SQL statement from inside the string and fling it at Oracle to get back the data. BTW I update Oracle this way also.

Here’s an example of one written in VB but most of it is a string literal. Execute this in SQL Server from the form and you’ll get back Oracle data:

Dim sSQL As String = "SELECT * FROM OPENQUERY(NameOfOracleLinkedServer, 'select EMPLOYEE_NUMBER, EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME from ORACLEDATABASE.TABLENAME WHERE EMPLOYEE_NUMBER = 'M1234')"

Hope that helps!

Paulie

It works like a piece of cake

Very Appreciated.

Asem

Great to hear!

Sent from my iPhone

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings