cancel
Showing results for 
Search instead for 
Did you mean: 

Nintex Forms Enterprise - SQL Request Control

shuklah
Nintex Newbie
20 63 23.9K

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.

Tags (1)
63 Comments
thanker
Nintex Newbie

Is there a way to populate a SQL request control by using a JScript on run time, 

I tried below but it doesnt work..

NWF$('#' + jsvalAmt).val("500");

???

Hemang Shukla

thanker
Nintex Newbie

Vadim Tabakman Any updates?

thanker
Nintex Newbie

This was resolved by setting the dropdown list SQL request control to be in New and edit mode, since it was only in New mode only

Nintex Employee
Nintex Employee

I'm afraid I've never tried that sorry.  So I don't know the answer to this.

Vadim

thanker
Nintex Newbie

Is there a way to run the validation on expression in edit or new mode: 
by accessing the values of items in Named control,

which means the for still submitted yet.

Or can we run a javascript function.

cmikhaiel
Nintex Newbie

Does the SQL request field works when it is published on an external facing form that has anonymous users submissions? 

It worked perfectly fine when using it for authenticated users but once we enabled anonymous submissions the fields come back as blank. I'm wondering if it is an authentication problem! 

‌, ‌ ? Sorry, for tagging you, but we really need an answer from some trusted people who might knows how this should work..  

Nintex Employee
Nintex Employee

Hey Christine,

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

Vadim

anairat
Nintex Newbie

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.

paulie_decesare
Nintex Newbie

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

anairat
Nintex Newbie

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

paulie_decesare
Nintex Newbie

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

anairat
Nintex Newbie

It works like a piece of cake

Very Appreciated.

Asem

paulie_decesare
Nintex Newbie

Great to hear!

Sent from my iPhone