SQL Request Control Conundrum

  • 20 October 2017
  • 1 reply
  • 7 views
G
Userlevel 6
Badge +13

Hi,

As per this thread, the SQL Request control does not retain values entered in NewForm.aspx when returned to Edit mode and Execute at runtime is set to yes.

My scenario is that I have SQL Request control set to Checkbox Multi Select. A user creates a new item and will selects items from the SQL Request control and submit. A user will return to edit the item and realistically will want to deselect existing options and select new options. The SQL Request control will not retain these marked options when the form is opened in edit mode, if the option to "Execute in Edit" is set to Yes, but it will execute the stored procedure in the form and list the returned values. If "Execute in Edit" is set to No the values will be retained but the stored procedure is not executed and the user will not have any new options to select.

Now, the items being selected are being written to a Child list so I have them available that way to list, but I can't do anything with them in the original form that way, and it is likely the user will want to deselect the items originally selected when the form was created. 

I'm looking for suggestions on how to overcome this issue with the SQL Request control. My thoughts are along the lines of multiple SQL request controls, one which works in New mode and then doesn't execute again, and another that is hidden in New mode and executes in Edit mode, then I'll have to use the workflow to delete all the child items everytime the form is edited, and then recreate the child items and any new items selected to be sure that any deselected items no longer exist in the child list.

Does this make any sense to anyone and if so, can you think of a better way of doing it, because this looks ugly to me.


Thanks

1 reply

G
Userlevel 6
Badge +13

So my solution to this (with JS help from Marian Hatala‌) was to treat the SQL Request control as a search results control only, when search results are selected by the user and a button clicked to confirm their selection, they are added to a text control which in turn is connected to a column. The button click also clears the search term and resets the SQL Request control. The user can then search another term if they wish and commit these to the text control again with a button click. They'll be appended to the existing results.

The piece of work in progress is how to allow users to remove items they previously selected.

I've created a workflow that tidies the string and semi-colon separates it, this allows me to set the column containing the items as the values for another choice control. When they user opens the form in edit mode, the choice control is dynamically populated with the values in the item column containing my values previously selected.

This javascript allows me to auto select all the items in the choice control

NWF$('#' + SelectedProductCodes + ' input:checkbox').prop('checked', true);

However, a workflow splits out these selected items and creates "child" items in another list. So my next step is to get the workflow to run when an item is edited and to query the child list for items which have been deselected in the edited form and delete them.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings