JavaScript method control.executeQuery()

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
amosbatto
Posts: 5085
Joined: Mon Jun 22, 2009 10:28 am
Country: Bolivia
Location: La Paz

JavaScript method control.executeQuery()

Unread postby amosbatto » Fri Feb 24, 2017 12:07 pm

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]}
         ]);
      }
   }
} );
Amos B. Batto ♦ ProcessMaker Technical Documentation Writer & Forum Manager ♦ Cofounder of ILLA-A

User avatar
Andderr
Posts: 1
Joined: Wed May 24, 2017 11:00 am
Country: United Kingdom
Location: London

Re: JavaScript method control.executeQuery()

Unread postby Andderr » Wed May 24, 2017 1:47 pm

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);
} );
“That's the thing about people who think they hate computers. What they really hate is lousy programmers.” ― Larry Niven // Do you need some academic help? Don't hesitate to contact me on writingcheap.com I'd be happy to assist you ; )

User avatar
cook90
Posts: 1
Joined: Mon Jul 31, 2017 5:26 am
Country: United States of America
Contact:

Re: JavaScript method control.executeQuery()

Unread postby cook90 » Mon Jul 31, 2017 5:30 am

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 [url=help with college homework]help with college homework[/url]


Return to “Tips and Tricks”

Who is online

Users browsing this forum: No registered users and 1 guest