Nintex Forms Enterprise - SQL Request and Web Service Request enhancement

alexjolynintex
Badge +6

I'm delighted to talk about the enhancements that have been done on the SQL Request and the Web Service Request component in Nintex Forms!

 

For those who have missed the last train, let me resume. Nintex Forms has a new young brother called Nintex Forms Enterprise! Nintex Forms Enterprise provides some new features, including the hability to load components from data retrieved from SQL Database or Web Service calls . You can read more about that on the following blogs :

Nintex Forms Enterprise - SQL Request Control  , and Nintex Forms Enterprise- Web Request Control

 

Those controls  have been requested by our partners and customers for a long time. Those components have been greatly appreciated in the community but some of you have noticed some limits in their usage, the main one being that the query was only executed once, on the loading of the form!

 

This blog is exactly about that point! With the new October release you will be able to add parameters to your Queries, parameters coming from the form itself. Let's dive!

 

 

Basically, with this new release, you won't see any changes in the designer itself, but when it comes to parameters!! To enlight it I will take two examples :

  • Retrieving some data from a WebService Call and dynamically configure this WebService call
  • Retrieving some data from a SQL Database and update the SQL query according to the content of the form.

 

As a first example, I wil update the example presented by  Hemang Shukla​ in his web Request Control post(Nintex Forms Enterprise - Web Request Control)!

 

On the example here, you can see Melbourne's wind direction only, but a nice thing would be to be able to choose the city dynamically with parameters coming from a Choice field that I have on my form. On the Service URL field, just click on the small icon beside the textbox and then you will see the improvment : now you get the "Named controls" tab that allows you to insert the value of your forms controls as parameters of your url. Before you could only do it with ItemProperties. Note that we also removed the "inline functions" tab that was useless in this context!

Let's give it a try!

 

On the previous example, I just replace 'melbourne' (which is a quite nice city by the way), by my choice field that contains a list of town (melbourne, paris, london, casablanca). I have now my service URL that looks like this :

After publishing the form ,you notice that whenever you change the value on the first dropdownlist, the form is Not updated, but the WebCall field is!

 

Let's now take a look at the SQL control with my second example :We will now try to get the List of cities from a SQL Database that is somewhere on my SQLServer.

 

I created a data table on my SQLServer called Cities containing 2 columns, "Country" and "City". then I populated the list with a few town starting by the most beautiful one (my hometown of course!)

Now in my form I have simply used the SQL Request component that I have put and configured like this (of course, CrestanDemoData, is the name of my SecureStore Application for which the credential have been configured for someone who has access to my DB also called "DemoCrestanData"):

Now If I simply save and run this I will show a DropDownList (cause my component has been configured to display a DropdownList, but I could have chosen to display it has Checkboxes, RadioButtons, Labels or textbox). But What if, now, I have a dropDownlist that already contains my country's names? How to make my SQL query change according to this field?

 

And just because I'm a bit lazy, obviously, you can imagine than the list of Countries itself I had picked it up from the same component, simply using the following Query :

 

And the result, is the one we expected (and of course there are no postbacks when you change the country value). Now when I change my countries, my city list is updated too!

The rest of the parameters from those controls remain unchanged, but this small improvement opens a lot of doors for the users!!

 

Have fun and thanks again Nintex dev team, you rock !!

13 replies

C
Userlevel 5
Badge +9

That's really a great new feature !

X
Userlevel 5
Badge +12

Great post!

K
Badge +8

I'm trying to work with the SQL Control but in my Insert Reference area i don't get the Named Control like you show in your images.  I only get Common, Item Properties, and Inline Functions.  Did something change since this post was made.  If so would like to know because I need to use this control on a Form to pull information out of our SQL database.

User enters an employee ID then that will pull the Name out of the DB. But with the SQL Control I cant set the Named Control as a parameter like you have.

Any information regrading that would be helpful.

Dan_Stoll
Userlevel 7
Badge +11

Hey Kevin Kittinger​, sounds like you have the older version installed. The first cut was missing this functionality.. the ability to add dynamic options came second.. Grab the latest installer from here and update your dev/test to test it out and then your prod environment.

Product Downloads

Nintex Forms 2013 - Release Notes

https://www.linkedin.com/in/stolldan
K
Badge +8

Hey Dan Stoll​, we are running version 3.1.5.0. And the Product Downloads link you posted is given me a Access to this place or content is restricted. If you think this is a mistake, please contact your administrator or the person who directed you here.. Any help with this that you can provide.

O
Badge +1

Hello All!

I have the same problem with Named Controls - I'm trying to use value from External data filed as a parameter in SQL Request, but I do not see External Data field in Named Controls. I'm already update Nintex Forms to most fresh version.

Any help would be great! Thank you!

B
Badge +4

On your second SQL statement use like: SELECT [City]
FROM [DatabaseName].[dbo].[Cities] WHERE Country=Country

use the last 'Country' named control without ' '.

i was able to see the results when i test ran the query, but it is not showing in the form.

G
Userlevel 6
Badge +13

Hi, I don't know whether further advancements have been made but I have an issue with this current functionality.

I'm using the SQL Request control to help users search for a customer from our customer records. There's 20k+ records here so I have implemented a free text search that passes in the value of a named control in the form to the WHERE clause on my SQL. 

However, on form load, the SQL request will populate itself automatically with the full list of customers, which is slow, and then when the user does type a value in the search box, it will execute the query again, currently that cause a long running script error, which does resolve itself but is obviously not a good experience.

Is there a way of preventing the control from executing it's query until a named control is not blank?

S
Badge +7

Ok this is how I've done it....

First off I have 2 stored procs. One passing in the Search Parameter and subsequent results, and then a second to bring back data for your chosen selection from the search.

On the form I have the following.....

Single Line Text Box : String : Name - EntitySearch

SQL Request : Drop Down List : Name - EntityChoice : Parameter - EntitySearch

Calculated Value : Generic : Name - EntityChoiceCalc : Formula - EntityChoice

Example of Lookup that passes the Calc Field above otherwise it doesn't work if using the EntityChoice Field.

SQL Request : Label : Name - AdminLookup

Could be the stored proc that's the issue. Mines limits to 50 and if null or blank is passed it returns nothing.

Tony.

G
Userlevel 6
Badge +13

I ended up using some T-SQL with a variable to represent my named control and an IF statement.

DECLARE @search NVARCHAR(MAX), @sql NVARCHAR(MAX); 
set @search = 'Search';
IF (@search IS NOT NULL) AND (LEN(@search) > 1)BEGIN
        select @sql = N'With [cte_M10Search] as (SELECT * FROM [CubeDB].[dbo].[dim_CustomerAccountsPrimaryAddr]
where (PartyName LIKE ''%'+@search+'%'' or AccountNumber like ''%'+@search+'%'')AND DataAreaID = ''M10'')
select * from [cte_M10Search]
where InvoiceAccount = AccountNumber;  
' 
 END
  print @sql
  exec sp_executesql @sql‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
B
Badge +9

Anthony Parker, Ryan Greenaway,

I hope you can help me with my scenario. I have a text field called name and a text field called sort name in SQL as well as on SharePoint list. The way it works right now is that data is entered through legacy application. While entering data, the sort name is the sorter name of the name which is automatically set, and if duplicate it prompts users to change it. Users usually add 1 or 2 and save it, which is fine.

Now since they want this to be pushed through SharePoint since it requires bunch of approvals, everything works on insert except for sort name scenario. I had a work around to create unique sort name, but it only works 99.9% of the time, they want 100%. With the help of my DBA, she wrote a stored procedure, but it does not display anything while previewing.

sql request

and the stored procedure is as follows:

USE [database]
GO
/****** Object: StoredProcedure [dbo].[stored procedure name] Script Date: AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER proc [dbo].[stored procedure name]

(@number = 0,@sortname bSortName )
as

set nocount on

--declare @rcode int
--select @rcode = 0, @msg = 'AP Unique'

IF EXISTS (SELECT TOP (1) 1 FROM Table WHERE NUMBER =@number and SortName=@sortname)
Begin select @sortname= @sortname + '1'
End

select @sortname

S
Badge +7

I'm struggling to understand what you're trying to do

Can you expand a little. Like I'm 5

Also I assume the SP shows the correct result when executed via SQL Management Studio and the rights to execute the SP have been granted to the Nintex Account.

B
Badge +9

Anthony Parker,

Sorry for the late reply. Yes, it executes correctly when testing from management studio and the nintex account has all the rights as well.

I am trying to use the stored procedure to look for sort name and if it is duplicate add 1 to it. The sort name comes from vendor name which is entered on the form. A calculated value picks up the vendor name that is entered and that calculated value is passes as the parameter to check on the stored procedure.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings