Unofficial documentation how to do interesting things and work around problems in ProcessMaker

Moderator: amosbatto

Forum rules: Unofficial documentation for features which have not been tested by Quality Assurance or may change in future versions of ProcessMaker
By amosbatto
#789379
Warning: This function was removed from version 3.2.1 and later.

The control.executeQuery() method is used to manually execute the SQL query for a control. The results of the query are returned in an array of options objects. Note that this method is only available in the Backbone object for a control, but not in its jQuery object.
Code: Select all
getFieldById("control-id").executeQuery()
Return Value:
The results of the SQL query in the database, in the following format of an array of options objects:
Code: Select all
[ 
  { value: "value1",  text: "label1"},
  { value: "value2",  text: "label2"},
  ...
]
If the query contains dependent fields, then the values of the dependent fields will be inserted into the query. These results can be passed as a parameter to the control.mergeOptions() method. The SQL query can only return a maximum of two fields. Any other fields in the query will not be included in the returned array. If needing to return more than 2 fields, then join together multiple fields with CONCAT() separated by a distinct character which can be used to separate the field values with JavaScript code. See example 2 for how to use CONCAT() in the query and JavaScript to separate the returned value into multiple fields. If the SQL query only returns one field, then its value will be duplicated in both the value and text elements.

Example 1:
A textbox with the ID "filterContacts" is dependent field to a checkgroup with the ID "selectContacts". This checkgroup has the SQL query:
Code: Select all
SELECT CONTACT_CODE, CONTACT_NAME FROM CONTACTS WHERE CONTACT_NAME LIKE "%@#filterContacts%"
The following JavaScript code is used to manually update the list of options in the checkgroup when the user leaves the "filterContacts" field:
Code: Select all
$("#filterContacts").blur( function() {
   var aOptions = getFieldById("selectContacts").executeQuery();
   getFieldById("selectContacts").mergeOptions(aOptions);
} );
Example 2:
The value entered into a textbox with the ID "filterServices" is used to query the database by a hidden field with the ID "queryServices". This hidden field has the following SQL query:
Code: Select all
SELECT ID, CONCAT(NAME, '|', DESCRIPTION, '|', TELEPHONE, '|', WEB_PAGE) FROM SERVICES WHERE NAME LIKE "%@#filterServices%"
In this example, "filterServices" is the independent field and "queryServices" is its dependent field. Note that the executeQuery() method expects to return only two fields (the key and label), but in this example more than 2 fields need to be returned, so they are concatenated with the | symbol (or some other distinct character).

When the user clicks on the "searchServices" button, the control.executeQuery() method is called to execute the SQL query for the hidden field. The second field located in aServices[idx].text is separated into multiple fields using .split("|"). The results are then added to a grid control with the ID "servicesList":
Code: Select all
$("#searchServices").click( function() {
   var search = $("#filterServices").getValue();
   if (search == "") {
      alert("Please enter a search term for the list of services.");
   }
   else {
      var aServices = getFieldById("queryServices").executeQuery(); 
      for (idx in aServices) {
         var aFields = aServices[idx].text.split("|");
         $("#servicesList").addRow( [
            {value: aFields[0]},
            {value: aFields[1]},
            {value: aFields[2]},
            {value: aFields[3]}
         ]);
      }
   }
} );
#791507
The issue was in this code, and I've finally found the solution!
Thanks a lot!
amosbatto wrote: The following JavaScript code is used to manually update the list of options in the checkgroup when the user leaves the "filterContacts" field:
Code: Select all
$("filterContacts").blur( function() {
   var aOptions = getFieldById("selectContacts").executeQuery();
   getFieldById("selectContacts").mergeOptions(aOptions);
} );
User avatar
By cook90
#794150
Hey there! This is a really nice trick and i'm sure it will help me a lot but this part doesn't work for me properly.
Code: Select all
SELECT CONTACT_CODE, CONTACT_NAME FROM CONTACTS WHERE CONTACT_NAME LIKE "%@#filterContacts%"
I've made a post on stackoverflow and they said that something is wronf about this line. Maybe any other options? :c
I feel like i need help with college homework

Hi, I want to set permissions for group but proces[…]

Slow grid above 20 lines

I have the following problem: I have a grid that i[…]

I cleared the logs in shared/log but it still erro[…]

Thanks amosbatto, How can i create a new plugin […]