Populate form columns with SQL Request

  • 24 January 2018
  • 8 replies
  • 7 views
K
Badge +2

Hi,

I wonder is there a way to get data with SQL request and fill specific newform.aspx columns with data from request?

For example, a new list item is opened, then user inputs for example Name and clicks a button or similar to execute SQL request which populates from columns, for example Department and Phone. And then user fills in other columns and saves the new list item.

I guess this could be done with SharePoint External List, but this would need to synchonize the External List with the connected database every night automatically.

Maybe there is some other ways to make this work?

All the best,

Kari

8 replies

E
Userlevel 5
Badge +14

what platform should it be for?

with on-prem forms being on enterprise edition there is a dedicated SQL Request control

https://help.nintex.com/en-US/nintex2013/help/#Forms/RootCategory/Controls/Nintex.Forms.Controls.SQLRequest.htm%3FTocPat… 

some user properties are usually maintained in AD as well. so typically department and phone number you could get from there using userProfileLookup() runtime function

https://help.nintex.com/en-US/nintex2013/help/#Forms/RootCategory/Designer/Nintex.Forms.RuntimeFunctions.htm%3FTocPath%3… 

K
Badge +2

Hi Marian,

yes, this is on-prem SharePoint 2013 and possibly 2016 also.

I got the SQL request working in test mode, and the request returns meaninful data. It's just that the examples I found are displaying the retrieved SQL data as a drop-down or option list.

I'm looking for a functionality where user types in like an ID Number on a form column, then maybe clicks a button (or some other event happens), then SQL request is executed and the retrieved data is populated to predefined columns on form. The key for the query is unique so there is only one record (or none) to be returned. The purpose is to pre-fill form from existing data if the ID Number user has given is matched.

At the moment the SharePoint solution is based on InfoPath forms and InfoPath form has this functionality. I'm evaluating Nintex Forms (and later Workflow) as a replacement to InfoPath, as we would be very happy to change the forms technology to something else, like Nintex.

Maybe there is a Community article already about similar case, but I could not find it. Also, I may not recognize an alternative way to implement functionality like th.

-Kari

.

E
Userlevel 5
Badge +14

It's just that the examples I found are displaying the retrieved SQL data as a drop-down or option list.

there is s configurable option that allows to change control's layout to checkboxes, radio buttons, etc.

however, they are all read-only type of controls.

unfortunately there is no other functionality in nintex forms that would allow to prepopulate form/controls by a result set from SQL datasource.

K
Badge +2

Could the Nintex Workflow make a difference here?

BR,

Kari

E
Userlevel 5
Badge +14

yeah, with workflow it's much more flexible.

there is a execute SQL action, which can return as many columns as you need (unlike SQL request control in forms).

typically result set is stored into a set of collection variables (one collection for each column). then you can iterate over the collections (pre)process the data, validate it, and then  make an update to list fields.

only drawback is user has to wait until workflow proceeds to at least this action to see data in list item or list form.

‌ ‌

K
Badge +2

Ok, have to try Workflow also, even if may not be a solution in this case!

-Kari

B
Userlevel 5
Badge +8

Hi Kari,

 

I wrote a post to address a similar scenario. I know this question was from awhile back, but this might assist with some direction on how this could be done.

 

https://community.nintex.com/t5/Community-Blogs/Cascading-SQL-Requests-in-Nintex-Forms/ba-p/92983

H
Badge +3

Greetings,

Would this be a site WF or a WF that can start from a list item?

 

I have an SQL action that retreives 11 text type columns and 3 date type columns. I created collection variables for all columns along with the apprpriate text and date variables. I am trying to use the For each loop along with a collection operation. Also within the loop I inserted a Update Multiple items action in order to write the SQL output to a list. Unfortunatlly nothing is working.

Regards

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings