cancel
Showing results for 
Search instead for 
Did you mean: 

Nintex Forms Enterprise - SQL Request Control

shuklah
Nintex Newbie
20 63 22K

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
vadim_tabakman
Nintex Newbie

Fantastic post Hemang!!

bschott
Nintex Newbie

Hemang will all of the documentation covering the new Enterprise Forms functionality be made available in this format or can we expect something in a consolidated form coming soon?

alexjolynintex
Nintex Newbie

I love it!

And all the partners to whom I have presented it are already crazy about it!

andy_taslim
Nintex Newbie

Hemang, thank you for the post.

I have installed the latest Nintex Form (version 2.7.0.0). But I could not find the SQL Request control on the Form Control listing during Form Design. Do I need to do something specific to enable it?

Stonehage
Nintex Newbie

It's part of the new 'Enterprise' option. I don't believe this existed until the other day? I'm currently trying to find out the cost of upgrading.

Not applicable

Hi Anthony and Andy Taslim​, yes this control is available in the Enterprise version of Nintex Forms which was released last week (22nd July) What's New from Nintex? July 2015 Product Release. You'll need to contact your partner for pricing details.

bschott
Nintex Newbie

Emily will all of the documentation covering the new Enterprise Forms functionality be made available in this format or can we expect something in a consolidated form coming soon?

andy_taslim
Nintex Newbie

Thank you Emily and Anthony for the reply.

I believe it is just a matter of updating the license file in the Nintex Form, am I correct?

Btw, I grabbed the Nintex Form partner demo license key, but there was nothing related to Nintex Form 2013 Enterprise, it was only for Nintex Form 2013. Please advice.

Not applicable
Hey Andy, you'll need to set up a trial to test out Nintex Forms Enterprise. Please email sales@nintex.com and they'll be able to help you out! 
Not applicable
Hey Brian, we don't have any documentation like that out yet, I'll talk to our product team and let you know. 

Cheers
Em
dan_stoll
Nintex Newbie

You'll also have to install the latest version of forms for the features to work   do this before you upgrade the license file .

ashishnathaniel
Nintex Newbie

Happy to see that Nintex has added this feature.

praios81
Nintex Newbie

When I tried to install the new bits to the environment where Forms were already installed I was only able to install it by modifying the config file like described here. https://community.nintex.com/community/build-your-own/blog/2015/03/23/cannot-install-nintex-workflow...

So maybe it's better to update the bits first which worked fine in another environment. The new actions weren't visible first but as soon as I also updated the license the new actions were instantly visible.

Regards,

Enrico

thurjs35
Nintex Newbie

It's a great feature and very much needed, I'm just disappointed that Nintex has decided to add an "Enterprise" version of the product.

Chris_Ben
Nintex Newbie

Hi Nintex peeps.

This is a great feature.  I notice when we execute the stored proc we can filter the results but seems to operate differently to how a cascading lookup control might work.  e.g. we can't filter by a named control, we have to filter by an item property therefore we cannot change the contents of the drop-down dynamically.

The filter also seems to be limited to passing a single parameter to the stored procedure.  Is there a way to pass multiple parameters to it?

In the example above we might want to pass two parameters - the min and max units in stock and only display items that have stock levels between these parameters.  Is there a way to do that with current functionality?

Thanks,

Chris

alexjolynintex
Nintex Newbie

Hi Chris,

we are aware of this limitation. The SQL Request component is still a baby, he don't listen to anyone . Wait for the next version of it, and you will be able to filter your query by named control.

We don't have any proper date to provide for the release of this new version of the components, but as soon as we have it, be sure that I will make a lot of noise around it!!.

Chris_Ben
Nintex Newbie

Love your work guys!

brian
Nintex Newbie

Is the SQL request control supported on Nintex Mobile and Nintex Live? If not, is it planned to be?

inimri
Nintex Newbie

Hello Alex,

i read Nintex Form 2.8 release notes and its mentioned that i can send 2 parameters in my request, but how about retrieving a dataset (more than 2 columns) from the sql request or web request?

is it doable through those controls?

are web request and sql request controls supported in Nintex mobile?

alexjolynintex
Nintex Newbie

Hi Ibrahim,

you can only link those queries to standard components  (DDL, Checkboxes, radiobuttons, textboxs). What would you do with a dataset?

Sadly those components are not supported in Nintex Mobile: it would oblige us to create an interaction between your Mobile app and the SQL database.... Security would be a huge issue!

alexjolynintex
Nintex Newbie

No, Nintex Mobile is not supported YET on Nintex Mobile or Live !! (don't even try to get the other part of the answer , I won't tell you)

Not applicable

Can i get a trial version to try out Enterprise edition Features ?

alexjolynintex
Nintex Newbie

If you are a partner, on the partner portal you can download it. You will just have to upgrade your licence ID.

If you are a customer, you should get back to your Nintex partner.

brian
Nintex Newbie

There are a handful of Nintex Form controls not supported on Nintex Mobile (now also including this new SQL request). I hope Nintex can close this gap soon so we can enable our mobile workforce with equally powerful forms.

Not applicable

I've been playing around with this, and I'm trying to figure out how to set multiple values after making the selection from the SQL picker. The ParseLookup works to set whatever field you specify as the Value field in the SQL Request control, but what if there were 5 other fields besides Units In Stock that you wanted to display? I don't want to have 6 different SQL request controls, because that would mean a lot of unnecessary calls to SQL Server.

shuklah
Nintex Newbie

Hi Ben,

The SQL Request control is designed to bound single field from the query results. I recommend putting a feature request for this in the user voice.

Not applicable

The Secure Store Shared Service is not available in SharePoint Foundation 2013. Is there a workaround for the SQL Request Control for companies who are using SharePoint Foundation 2013 and Nintex Forms Enterprise? 

seid
Nintex Newbie

Hi, I have a question regarding this feature. Other than querying databases to populate controls, is it possible to write querys that inserts,updates and deletes from the database?

vadim_tabakman
Nintex Newbie

Hi Shakha,

since you could call a Stored Procedure, you could do anything a Stored Proc could do.

Vadim

EuanGamble
Nintex Newbie

Hi Brian Crane​,

The SQL Request Control will only work with the secure store service.

Cheers,

Euan

sojanm
Nintex Newbie

Hi Hemang,

I am  having strange problem with SQL Request control, The control is getting disabled when I used it in repeating section and with filter (named control or form variable) in sql query. It is not disabled when I use it without a dynamic filter.

I used both Stored procedure and SQL.

Any Idea on this issue.

Thanks,

Sojan

vadim_tabakman
Nintex Newbie

Hi Sojan,

I actually found this a few days ago also and I raised it as a bug.  So the development team is aware of it.

cheers,

Vadim

sojanm
Nintex Newbie

Thanks Vadim, Quick reply, Any idea when will it be fixed.

vadim_tabakman
Nintex Newbie

Hi Sojan,

Don't know I'm afraid.  I only logged it a few hours ago.  But that will give it visibility.

Fingers crossed my friend.

If it's affecting a production form you are building, shoot support an email and that will raise it higher.

Vadim

sojanm
Nintex Newbie

Hi Vadim,

Any update on this feature.

Thanks,

Sojan

shuklah
Nintex Newbie

Hi Sojan,

Unfortunately the current version of SQL request control is not designed to work with dynamic filters when used in repeater. However our product team has prioritized this request and we are aiming for delivering this in July release of the product.

sojanm
Nintex Newbie

Thanks Hemang, Really waiting for that. Good to know that it is going to be in July release.

Sojan

younkins
Nintex Newbie

Any particular reason windows authentication only works with the secure store service? With the workflows you can do the sql request using a secure store or a workflow constant credential. Getting a secure token set up in our environment is time consuming.

paulie_decesare
Nintex Newbie

Question: On my form I have to populate a drowdown list with our customers' info, which resides in Oracle. Does Nintex Forms 2010 do that directly with or without the "SQL Request" addition, or do I need to use something like a Linked Server in SQL to grab the data, then query THAT?

greenawayr
Nintex Newbie

12 months on and this is still the case. I created a form that allowed users to search for customer using a free text query that works great. When they select the correct user from the SQL Request dropdown a bunch of other controls go away and get the rest of the customer info. a total of 7 SQL requests to retrieve the info.

inimri
Nintex Newbie

Hello Ryan,

I'm using a similar way to yours,

there is a way you can minimize the SQL controls use,

if the customer info you retrieve from the database are connected to the same ID,

you can configure the SQL Control to send the customer ID to your procedure and retrieve two columns from the database (field1,field2) instead of (ID,field2)

then connect a calculated field to the SQL control to retrieve field 1 or field2.

this way, you can cut down the SQL controls in your form to half.

i know this not what you are looking for,

but when "saving without closing" is implemented, i believe it will solve a major part of this issue since you can use a web request to retrieve all the data you need and connect it to a repeating section and configure all the fields inside it.

greenawayr
Nintex Newbie

With Microsoft introducing the Cloud Gateway for on premise servers to allow data connections to things like PowerBI, PowerApps, Flow etc, are we getting any closer to investigating a solution from Nintex to allow the SQL Request control to work in mobile.

lgiambar
Nintex Newbie

My SQL request is working when I click run now, but will not work on the actual form? Any advice? I posted screen shots here..

 

bhashwar_bhatta
Nintex Newbie

I am trying to write to SQL table. I have a table "bJCJM" where i have columns Job (job number) and JCCo (company number). I want to validate a entry from nintex form against Job if Job (job number) already exists.

I am even struggling to show fields on preview.

tomcastiglia
Nintex Newbie

Hello SP P‌ - Its best for you to pose this as a question in the Getting Started forum.  Just click on Actions > Question as shown below.  This way community members can respond directly to your question.  Please include some screenshots of your form and/or how you configured the SQL Request control.

bhashwar_bhatta
Nintex Newbie

Thank you Tom, will do so.

thanker
Nintex Newbie

Is there a way to introduce a delay while calling the procedure withing the SQL request, as some parameters are used based on other run time sql request from the interface. 

Can we control the on load sequence of the SQL requests.

please refer to below question for more details.

 

vadim_tabakman
Nintex Newbie

Hi Shaker,

Maybe use the WAITFOR command in SQL : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/waitfor-transact-sql

I've not tried this, but sounds like it's what you need.

Vadim

thanker
Nintex Newbie

The main problem is that: Just need to control the sequence of the SQL request execution while the form is loading.

This is the main issue, which I think it should be controlled from withing nintex side.

thanker
Nintex Newbie

then if the last producer is delayed, while execution, still the parameters needs to be filled before logged into the procure itself.