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 3602 times
-a dropdown with the ID "selectOne" with the with following SQL query:
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 allSELECT @=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 allSELECT 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.
(37.34 KiB) Downloaded 359 times
(4.97 KiB) Downloaded 346 times