[fardon]

Hello friends, I need to fill a grid depending on what you type in a texbox or select a Dropdown box and give me back all the data there. My database it's an external database.

I tried with this code:

$query = 'SELECT FULL_NAME AS Name, STARTING_SALARY AS Salary HIRE_DATE as HireDate FROM EMPLOYEES';
$result = executeQuery($query);
if (is_array($result) and count($result) > 0)
@=EmployeesGrid = $result;

Im using processmaker 3.0

Help!!

[amosbatto]

Sorry fardon, I haven't had time to prepare an example to show how to do this. I'll try to find time tomorrow or Monday.

[michaeltimoteo]

Hi fardon,

This is one of my solutions, using AJAX.
Documentation: http://wiki.processmaker.com/index.php/ ... _DynaForms

1.) Create a database where you will get the info for the grid (if not yet created). Below is a screenshot of my example created database schema and table.

db.PNG (30.19 KiB) Viewed 5686 times

2.) Create a query.php script and store it in your /opt/processmaker/workflow/public_html

Code: Select all

<?php

//path to the file containing connection info for MySQL:
//include 'password.php'; //i comment it out because i am too lazy to create another script
//employee_id is the one going to be triggering the independent field
header('Content-type: application/json');
if (isset($_GET['employee_id'])) {
   $q = $_GET['employee_id'];
}
else {
   die ('[]'); //return an empty array
}

$con = mysql_connect('localhost', 'root', 'kamote1234') or die('[]'); //this is your database credential
mysql_select_db("mike_db", $con); //this is the db
$sql = "SELECT FULL_NAME, HIRED_DATE, STARTING_SALARY from EMPLOYEES where EMP_ID = $q"; //this is the query
$result = mysql_query($sql) or die('[]');
$ret = '';
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $ret .= (empty($ret) ? '' : ', ') .
      "{ 'full_name' : '{$row['FULL_NAME']}', 'hired_date' : '{$row['HIRED_DATE']}' , 'salary' : '{$row['STARTING_SALARY']}' }"; //this will be the result in json

}

mysql_close($con);
die('[' . $ret . ']');

?>

3. Create a js script that will call the ajax

Code: Select all

//this ajax is not perfect so please feel free to modify it anyway you want
$("#employee_id").setOnchange(doQuery);
function doQuery() {
   var q = getFieldById("employee_id").getText();
   
  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");
                
         for (var i = 0; i < rows.length; i++) {
    
           $("#employee_info").setValue(rows[i].full_name,i+1 , 1);
           $("#employee_info").setValue(rows[i].hired_date,i+1 , 2);
           $("#employee_info").setValue(rows[i].salary,i+1 , 3);
       
         }
       
         
      }
   }
   xmlhttp.open("GET", "http://192.168.137.128:8080/query.php?employee_id="+q, true); //the 192.168.137.128:8080 is where i could access my PM
   xmlhttp.send();
}

4.) here is a sample screenshot of my project that I also attach

screen.PNG (72.89 KiB) Viewed 5686 times

I hope this helps you in your problem

[fardon]

Don't worry amosbatto, if you have another way to do that I want to would be wonderful

[fardon]

Thank you very much michaeltimoteo, but the php script how way I can create it???

Regards,

[amosbatto]

Thanks michaeltimoteo for writing up a good example of how to use AJAX in PM3. Eventually I'm going to have to add this to the documentation for PM3, because so many people need it and there is no easy way to do it.

Thank you very much michaeltimoteo, but the php script how way I can create it???

fardon, you have to manually create the PHP file on your server using a plain text editor. If using Linux, you can do this from the command line:
su
nano /opt/processmaker/workflow/public_html/query.php

If using Windows with the old installer, you can use Notepad to create the file, in a location like:
C:\Users\USERNAME\AppData\Roaming\ProcessMaker-3_X_X\processmaker\workflow\public_html\query.php

If using Windows with the new Bitnami installer, it is some place like:
C:\Bitnami\ProcessMaker-3_X_X\processmaker\workflow\public_html\query.php

The JavaScript code needs to be placed in your DynaForm file.

[karlosKamacho]

hello Fardon, to fill the grid using a trigger we need to take into account the following:
- For example in your SQL query <<then FULL_NAME AS Name>> column of the grid has to have the id as "Name" and so on for each column.
- If you use a Dropdown not forget that has its value and text for example: my variable is called "day"
value-> text
1-> Monday
2-> Tuesday
- To use the "value" of the dropdown would have to use @@day or for text @@day_label all within the php code.
The triggers are executed before or after, to what I understand from your question is you want to put a search parameter and then run the trigger, then you would have to have a "Form A" having parameters and a "Form B" having the grid filled.
- If you want the grid is filled in the same form, the solution michaeltimoteo is also a good option.

regards