[jalvarezrend]

Hello,

I have pasted a multi-select box dropdown in one of my forms. Now I want to set another one as dependent of this one. I want the second one to be populated using a SQL Query based in what the user has introduced in the first multi select box dropdown.

Is this possible? How can I make the SQL Query?

Regards,
Jose Enrique,

[amosbatto]

You need to use an SQL query like:

Code: Select all

SELECT X, Y FROM Z WHERE A IN ('B', 'C', 'D')

Create a multiple-select dropdown box in your DynaForm whose ID and variable is named "selectServices". Then, add another dropdown whose ID and variable is named "selectTask", which will be the dependent field to the "selectServices" dropdown. This second dropdown will use an SQL query like:

Code: Select all

SELECT X, Y FROM Z WHERE A IN (@=selectedList)

Also create a textbox whose ID and variable is named "selectedList". JavaScript will be used to hide this textbox. Do NOT use a hidden field, because changing its value will not trigger the SQL query in the "selectTask" dropdown.

Then, add this JavaScript to the DynaForm:

Code: Select all

$("#selectedList").hide();

$("#myMultiselect").getControl().change(function() {
   var allSelected = '';  
   for (var i = 0; i < this.options.length; i++) {
      if (this.options[i].selected)
         allSelected .= (allSelected != '' ? ', ' : '') + "'" + this.options[i].value + "'";
   }
   $("#selectedList").setValue(allSelected);
   //$("#selectTask").executeQuery(); // uncomment this line if using PM 3.0.1.7 or earlier
} );

[joserendal]

Hi Amos,

I have tested your solution, placing the mentioned SQL Query in my dropdown. It's not displaying anything, and I'm seeing in the browser's console Bad Request (400) errors.

Do i have to associate the dropdown fields to a variable or to do anything special?

Thanks for your answer,
Best regards.

[amosbatto]

The code I gave you should work, but this JavaScript code should work better since it also selects the values in the dependent field when the DynaForm loads:

Code: Select all

$("#selectCountry").getControl()[0].multiple = true //convert to listbox
$("#selectCountry").getControl().parent().append("<p>Press the CTRL key while clicking to select multiple items.</p>");
$("#selectedList").hide();

function setDependentVal() {
  var aVals = $("#selectCountry").getControl().val();
  var allSelected = '';
  
  for (var idx in aVals) {
    allSelected += (allSelected != '' ? ', ' : '') + "'" + aVals[idx] + "'";
  }
  
  $("#selectedList").setValue(allSelected);
  //$("#selectTask").executeQuery(); // uncomment this line if using PM 3.0.1.7 or earlier
}

$("#selectCountry").getControl().change(setDependentVal); //when value changes in dropdown
setDependentVal();      //execute when Dynaform loads

Where selectCountry is the independent multiple-select dropdown and selectedList is the hidden textbox and selectTask is the dependent dropdown.

To see it work, import this Dynaform:

Multi-select dependency.json

(3.63 KiB) Downloaded 364 times

[jalvarezrend]

Hi Amos,

Great, it works perfectly now. Thank you so much.

Kind Regards,
Jose Enrique.