Please rate how useful you found this document: 
Average: 2.8 (4 votes)

Overview

Normally JavaScript can only access the data within the browser window. If needing to access data, execute commands or query a database on the ProcessMaker server, then AJAX (asynchronous JavaScript and XML) will have to be used.

Warning: Some code examples are provided in order to give an idea how to use AJAX in DynaForms, but be aware that AJAX programming is not officially supported, so please do not ask the ProcessMaker support department for help using AJAX. If needing to repopulate a field in an already open DynaForm, it is recommended to use Dependent Fields instead of AJAX. To force the dependent field to requery the database, just send a "change" event to its independent field with the fireEvent() function.

Repopulating a Dropdown Box or Listbox

AJAX can be used repopulate the list of options in a dropdown box or Listbox with a database query in an already open DynaForm.

For example, the list of options in dropdown box named "selectClient" needs to repopulated with the following query:

SELECT id, name FROM clients WHERE category LIKE '%X%'

Where X is the value of a textbox in the DynaForm named "searchCategory". Add the following JavaScript code to the DynaForm to be executed when the value of the "searchCategory" field changes:

function doQuery() {
   var q = this.value;
   if (q == "") {
      getField('selectClient').length = 0; //clear the existing dropdown
      return;
   }
   if (window.XMLHttpRequest) // code for IE7+, Firefox, Chrome, Opera, Safari
      xmlhttp=new XMLHttpRequest();
   else // code for IE5, IE6
      xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
 
   xmlhttp.onreadystatechange = function() {
      if (xmlhttp.readyState==4 && xmlhttp.status==200) {
         var opts = eval( '(' + xmlhttp.responseText + ')' );
         for (var i = 0; i < opts.length; i++) {
             var opt = document.createElement("OPTION");
             opt.value = opts[i].value;
             opt.text  = opts[i].label;
             getField("selectClient").options.add(opt);
         }
      }
   }
   xmlhttp.open("GET", "http://my-address/query.php?q="+str, true);
   xmlhttp.send();
}
getField("searchCategory").onchange=doQuery;

Replace my-address with the IP address (and path) where the query.php file can be found at a publicly accessible location on the server.

Then, create the file query.php in a publicly accessible location. The location <INSTALL-DIRECTORY>/workflow/public_html/query.php on your ProcessMaker server can be used, but be aware that any code located in this directory will be overwritten each time ProcessMaker is upgraded. Use PHP code like the following to requery the database:

<?php
//path to the file containing connection info for MySQL:
include '/secure/location/password.php';
header('Content-type: application/xml');
if (isset($_GET['q']))
   $q = $_GET['q'];
else
   die ('[]'); //return an empty array;
 
$con = mysql_connect($server, $username, $password) or die('[]');
mysql_select_db("contacts", $con);
$sql = "SELECT id, name FROM clients WHERE category LIKE '%$q%'";
$result = mysql_query($sql) or die('[]');
$ret = '';
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $ret .= (empty($ret) ? '' : ', ') . '{ "value" : "' . $row['id']  .  '", "label" : "' . $row['name'] . '" }';
}
mysql_close($con);
die('[' . $ret . ']');
?>

Then, create a file named password.php in a secure location on the server which defines the $server, $username and $password variables needed to connect to the MySQL database:

<?php
$server      = "localhost";
$username = "someuser";
$password  = "p4sSw0rd";
?>

Repopulating a Grid

AJAX can be used repopulate the rows in a grid with a database query in an already open DynaForm.

For example, a grid named "clientsGrid" has the grid fields "firstName", "lastName", "telephone" and "address" which need to be repopulated with the following query:

SELECT firstName, lastName, telephone, address FROM clients WHERE firstName LIKE '%X%' OR lastName LIKE '%X%'

Where X is the value of a textbox in the DynaForm named "searchName". Add the following JavaScript code to the DynaForm to be executed when the value of the "searchName" field changes:

function clearGrid(gridName) {
   var grd = getObject(gridName);
   var i = Number_Grid_Rows(gridName, grd.aFields[0].sFieldName);
   for (i; i > 1; i--) {
       grd.deleteGridRow(i, true);
   }
   //The first row can't be deleted, so clear the fields in the first row:
   for (i = 0; i < grd.aFields.length; i++) {
       getGridField(gridName, 1, grd.aFields[i].sFieldName).value = "";
   }
}
function doQuery() {
   var q = this.value;
   if (q == "") {
      clearGrid();
      return;
   }
   if (window.XMLHttpRequest) // code for IE7+, Firefox, Chrome, Opera, Safari
      xmlhttp=new XMLHttpRequest();
   else // code for IE5, IE6
      xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
 
   xmlhttp.onreadystatechange=function() {
      if (xmlhttp.readyState==4 && xmlhttp.status==200) {
         var rows = eval( '(' + xmlhttp.responseText + ')' );
         var grd = getObject("clientsGrid");
         clearGrid("clientsGrid");
         for (var i = 1; i < rows.length; i++) {
            if (Number_Grid_Rows("clientsGrid", "firstName") != 1) {
               grd.addGridRow();
            }
            getGridField("clientsGrid", i, "firstName").value = rows[i].firstName;
            getGridField("clientsGrid", i, "lastName").value = rows[i].lastName;
            getGridField("clientsGrid", i, "telephone").value = rows[i].telephone;
            getGridField("clientsGrid", i, "address").value = rows[i].address;
         }
      }
   }
   xmlhttp.open("GET", "http://my-address/query.php?q="+str, true);
   xmlhttp.send();
}
getField("MyTextbox").onchange=doQuery;

Replace my-address with the IP address (and path) where the query.php file can be found at a publicly accessible location on the server.

Then create a file named query.php in a location on the server running ProcessMaker. For example, the query.php file could be placed at /opt/processmaker/workflow/public_html/query.php and then it would be accessible in the internet at http://my-address/query.php. However, be aware that any code located in the workflow/public_html directory will be overwritten each time ProcessMaker is upgraded.

If using this code in a plugin, then the file could be placed in a location such as /opt/plugins/myplugin/myplugin/system/query.php on the ProcessMaker server, and it could be accessed on the internet at: http://my-address/myplugin/system/query.php

Add PHP code like the following to the query.php file to requery the database:

<?php
//path to the file containing connection info for MySQL:
include '/secure/location/password.php';
header('Content-type: application/xml');
if (isset($_GET['q'])) {
   $q = $_GET['q'];
}
else {
   die ('[]'); //return an empty array
}
 
$con = mysql_connect($server, $username, $password) or die('[]');
mysql_select_db("contacts", $con);
$sql = "SELECT firstName, lastName, telephone, address FROM clients WHERE firstName LIKE '%q%' OR lastName LIKE '%q%'";
$result = mysql_query($sql) or die('[]');
$ret = '';
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $ret .= (empty($ret) ? '' : ', ') .
      "{ 'firstName' : '{$row['firstName']}', 'lastName' : '{$row['lastName']}', " .
        "'telephone' : '{$row['telephone']}', 'address' : '{$row['address']}' }";
}
mysql_close($con);
die('[' . $ret . ']');
?>

Then, create a file named password.php in a secure location on the server which defines the $server, $username and $password variables needed to connect to the MySQL database:

<?php
$server = "localhost";
$username = "someuser";
$password = "p4sSw0rd";
?>