[sgkalluri]

Hello everyone,

The sql property of a web control is normally set during form design. However, can this be set during run time using Javascript? In that way, one could potentially use one checkgroup variable to repeatedly executeQuery() and get desired results.

Thanks in advance,
SGK

[amosbatto]

For security reasons, ProcessMaker doesn't allow you to change the SQL query when running a DynaForm. What you can do is create a textbox whose value is used by an SQL query.

For example, you have a DynaForm with two controls:
- a dropdown with the ID and variable "sqlQuery", which has the following options:

selectOptions.png (30.03 KiB) Viewed 3566 times

-a dropdown with the ID "selectOne" with the with following SQL query:

Code: Select all

SELECT @=sqlQuery

When the user selects a query, it will be executed in the selectOne dropdown. The problem is that this is a potential security risk, because a hacker can send any SQL query to the database. Always make SELECT a fixed part of SQL property for the dropdown box, so a hacker can't use a DELETE, UPDATE or INSERT statement which would alter the contents of the database. It is recommended to fix more parts of the SQL query for more safety, such as:

Code: Select all

SELECT @=fieldsList FROM PMT_CLIENTS WHERE COUNTRY=@@selectCountry

Here is another example, which might help you.
You have a DynaForm with the following controls:
- a checkgroup with the ID "selectCountries"
- a textbox with the ID and variable named "countryList"
- a dependent dropdown box with the ID and variable named "selectClient", which has the following SQL query to populate its list of options:

Code: Select all

SELECT ID, NAME FROM PMT_CLIENTS WHERE COUNTRY IN (@=countryList)

Then use the following JavaScript to set the list of countries:

Code: Select all

$("#countriesList").hide(); //hide the textbox when the form loads 

//When a country is selected in the selectCountry checkgroup, 
//then it is added to the "countriesList" textbox to be used in the query:  
$("#selectCountries").setOnchange( function(aVals, aOldVals) {
  var countries = '';
  for (i in aVals) {
    countries += (countries == '' ? '' : ',') + "'"+aVals[i]+"'"; 
  }
  $("#countriesList").setValue(countries);
});

Note that hidden controls cannot be independent fields whose value is used by dependent field queries, so you need to hide a normal textbox with JavaScript.

Here is a sample process with the above examples in them and a sample PMT_CLIENTS table to test the second example.

Test_multi_select_with_dependents-3.pmx

(37.34 KiB) Downloaded 355 times

sys-workflow_2017-04-13_2005.pmt

(4.97 KiB) Downloaded 340 times

[sgkalluri]

Thanks very much again Amos. Thank you for the warning on the security part too!

SGK