cancel
Showing results for 
Search instead for 
Did you mean: 

Cascading SQL Requests in Nintex Forms

Nintex Employee
Nintex Employee
2 5 1,461

This question seems to come up pretty frequently, so I wanted to provide a quick walk through on how to use a SQL Request control as a cascading lookup to your backend databases.  In this scenario, the Safalo corporation requires a Nintex Form for their agents that interfaces with their larger Property Management database.  In this form, they need a few pieces of information to be exposed on the form including their Property State, City, and Address along with their Agent contact information.

 

Form.png

 

In this scenario I have three SQL Request controls configured as dropdowns (pulling in my property information) and two controls configured as labels (displaying my Agent Information).  Below, I'll walk through the configuration of each of the controls along with a quick video of the form in action.

 

First, I setup my control to pull in the state in which my property is located. I have named the control 'Property State', I have configured my server / database settings, setup my query and configured my field values.  The screenshots below will provide a bit more context.

First Screenshot.png

SecondScreenshot.png

**In the fields you'll see I selected state and state; this is ok when we are not at a unique value ie: there are multiple different properties within that same state

 

Next I configure my second control to pull in the City data. In this instance, I only want cities within the state selected in the first control, so I setup the connection pieces the same as the previous control (drop down, server, database, secure store ID) and update the pieces relevant to the data (control name, Query, and fields). 

 

City.png

 

For the dropdown queries I only have one left to configure, the Address.  Again we will follow the same steps from the previous two controls for the connection pieces.  As we did with the city control for states, we will update the query to target addresses only in the city specified in the Property City control.  Lastly we will include the property_ID as the value, as we will only have one unique property we are managing at this address.

 

Addresss.png

Once we have the property info, we want to pull in information for the agent that is managing this property.  We are going to continue to use the same setup for the connections, but in this instance, we are going to display the data as a label rather than a dropdown.  In this form I have two pieces of information coming from the Agent, the Agent ID and the Agent Email. The agent ID is going to populate based on the property that was selected, and the agent email will come from the Agent ID that is populated in the form.


Agent ID

AgentID.png

Agent Email

Agent Email.png

 

Now that everything is configured, we can test this in realtime:

2019-04-22_11-18-08.gif

5 Comments
Ice_Sinsab
Nintex Newbie

Hi Mr.Burked,

I found out this one is really useful.
I tried to work on this one feature as well but still stuck with the thing that I need to input in Secure Store target application ID.

Could you provide more details about how to get input for mentioned field please?

Nintex Employee
Nintex Employee

@Ice_Sinsab Secure Store Target Application ID will be configured on the SharePoint side in Central Administration.  The link below will provide a good walkthrough on the how and why with secure store configuration.

https://www.sharepointpals.com/post/how-to-create-a-new-target-application-in-secure-store-service-s...

Ice_Sinsab
Nintex Newbie

Hi,

 

I tried to complete this thing but result with this error;

Test Result
Could not impersonate the elevated user. LogonUser returned error code -2146892994.

 

Could you advise on this please?

Thank you

 

Nintex Employee
Nintex Employee

Hi @Ice_Sinsab,

 

2 things I would check:

1) Did you setup the Secure Store application as a group login (are you included in the users with permissions to use)

2) Does the account you setup in the Secure Store app have permissions to the database you're querying and the SharePoint site you're working on?
 

joyleak
Nintex Newbie

I am working on cascading dropdowns, however I am using Manager, Employee Name, and Director. I am able to configure the dropdown when I select the Manager's name, it filters the Employees  that report to them. I am unable to get the Director portion to work. I am stuck at where you got Property_ID from in your example. Is this a column or a column in SQL?  I am stuck.