Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderators: ArturoRobles, ArturoRobles

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
By ekaboom
#788310
Hello

So I saw that you can add specific column information into output documents, however it adds every row to the document.

So there's no way to specify a specific row and column for grid value to put directly into the output document. Like adding the value from row 1, column 6 or adding all the data from a specific row. There's no way of directly doing that, Is that right?

So if I want to add specific values from a specific row to an output document, the only way I can do that is to copy those specific grid row/column values over to a variable, right?

Assuming all the above is correct, how would I move the values from only the last row of a grid into variables so I can add the last row of grid data to an output document?

Thanks
By zainab
#788312
As far as my knowledge about ProcessMaker is concerned you are absolutely right.

In order to store the values of only last row of grid create a trigger with the code:
Code: Select all
$num = count(@=grid);
@@count=$num;
@@[email protected]=grid[$num]['name'];
@@[email protected]=grid[$num]['last'];
Assuming @@one and @@two are the two variables to be used in the output document.

Assign this trigger to fire after the Dynaform with the grid. Hope this helps.
By ekaboom
#788315
Thanks so much again! This totally worked!

I really appreciate your insight. You've been spot on with all of your help.

Quick question (sorry it's probably a dumb one) about how processmaker and triggers work.

when you create this trigger that uses the variable @@count, is @@count just being used just for this trigger and not saved to the database or is @@count getting added to the database as a variable?

Thanks
By zainab
#788316
Your most welcome :)
Thank you for appreciating!

Well, there is no such thing as a dumb question :)

All these variables, that is @@count, @@one, @@two and @@grid are actually case variables which are created inside a project and stored exclusively for each case.

They can be used anywhere in a particular process, be a dynaform, output document, email or trigger. The storage of the variables is independent of the fact where they are used inside a process.

In short, don't think about count as a variable created inside a trigger, instead count is a variable of the process which is being used by the trigger and the value assigned to count in the trigger can be used anywhere and is stored in the database along with the case it belongs to.

I hope this clears your doubt.
By ekaboom
#788328
You have been more than helpful so the appreciation is abounding.

Thanks again for the thorough reply and patience with my ignorance.

One last question to make sure I understand. Do I actually need to create that variable independently by manually creating the variable, or just by having the variable in a trigger, does it get created by the trigger?

Thanks again for your time and patience with me.
By ekaboom
#788331
You have been more than helpful so the appreciation is abounding.

Thanks again for the thorough reply and patience with my ignorance.

One last question to make sure I understand. Do I actually need to create that variable independently by manually creating the variable, or just by having the variable in a trigger, does it get created by the trigger?

Thanks again for your time and patience with me.
Ok, so in addition to this last question. I have another question about your code.

If I want to add, divide, multiply, etc grid numbers together to make another variable would you do it like this:
Code: Select all
$num = count(@=grid);
@@count=$num;
@@[email protected]#grid[$num]['number1'];
@@[email protected]#grid[$num]['number2'];
@@[email protected]#grid[$num]['number1'][email protected]#grid[$num]['number2'];
@@[email protected]#grid[$num]['number1']/@#grid[$num]['number2'];
@@[email protected]#grid[$num]['number1']*@#grid[$num]['number2'];
or I guess it might make more sense to do this but not sure about the above or below?
Code: Select all
$num = count(@=grid);
@@count=$num;
@@[email protected]#grid[$num]['number1'];
@@[email protected]#grid[$num]['number2'];
@@addition= @#one + @#two;
@@division = @#one / @#two;
@@multiplication = @#one * @#two;
Thanks again.
By zainab
#788334
It is better to create the variable independently and then use it wherever required. But even if you just create the variable in the trigger it works and a case variable of that name is created, but in Designer it won't appear in the list of variables.
ekaboom wrote:One last question to make sure I understand. Do I actually need to create that variable independently by manually creating the variable, or just by having the variable in a trigger, does it get created by the trigger?

As far as your code snippets are concerned both the ways are correct and would work either way. But only one correction instead of referring the grid values as:
Code: Select all
@#grid[$num]['number1']
You need to refer it as:
Code: Select all
@=grid[$num]['number1']
Since this is the correct way to refer the grid values.

Also if you want to perform calculation in the grid, you can do it with the help of Formulas. Every field has a property associated with it called formula, using which you can perform the calculation of two fields and display in result in other field.

As you can see below, I select the Addition tab, corresponding to which the Properties associated with it appears. In the properties, locate the formula attribute.
formula.png
formula.png (53.96 KiB) Viewed 6937 times
In formula, click on the edit button and an editor similar to below image would appear. Perform the required calculation, suppose I want to add the grid columns one and two(one and two being the id of the grid columns), I would write one+two as seen below.
edit.png
edit.png (10.19 KiB) Viewed 6937 times
I hope I was clear enough. For more information related to calculation in grids, http://wiki.processmaker.com/3.0/Grid_C ... s_in_grids

Thank you once again for the kind words, and we are all here to help you with any questions regarding ProcessMaker.
ekaboom wrote:You have been more than helpful so the appreciation is abounding.

Thanks again for the thorough reply and patience with my ignorance.
Warm Regards,
Zainab
By ekaboom
#788343
Thank you very much for the reply to that question and your correction.

That totally makes sense that the variables are added and explains why your code worked whether I had the variable added to the process or not (or if it gets deleted).

It's good to know that the variables will be created even if you don't add the variables manually.

And I get why you'd want to create the variable in the process so you don't lose track of the variables you have.
Also if you want to perform calculation in the grid, you can do it with the help of Formulas. Every field has a property associated with it called formula, using which you can perform the calculation of two fields and display in result in other field.
Thanks for this advice too. I actually do know about the formula option here. One of the few things I know about. :) My application actually requires creating dynaform variables (because I need the grid data to be put into a dynaform) but the variables I need are based on the math of several grid fields but I didn't want to be too redundant by creating any new grid fields since I have to create the variables anyway for the dynaform.

I started using the formula options but had to eventually bypass the formula option because my calculations needed to be rounded up to two decimal points so I ended up using the suggested javascript in the documentation to round up to just the two decimal places.

Thanks again. You have been more than gracious with sharing your experience, knowledge and help.
By ekaboom
#788371
zainab wrote:As far as my knowledge about ProcessMaker is concerned you are absolutely right.

In order to store the values of only last row of grid create a trigger with the code:
Code: Select all
$num = count(@=grid);
@@count=$num;
@@[email protected]=grid[$num]['name'];
@@[email protected]=grid[$num]['last'];
Assuming @@one and @@two are the two variables to be used in the output document.

Assign this trigger to fire after the Dynaform with the grid. Hope this helps.

Is there a same way to do this with javascript (and not a trigger) so it can happen inside of the same dynaform?
User avatar
By amosbatto
#788384
Create three hidden fields in the form which are associated with the variables "one", "two" and "division". Then you can use this JavaScript code:
Code: Select all
getFormById( $("form").prop("id") ).setOnSubmit( function() {
   var lastRow = $("#MyGrid").getNumberRows();
   var one = $("#MyGrid").getValue(1, lastRow); //first field in last row
   var two = $("#MyGrid").getValue(4, lastRow); //fourth field in last row
   $("#one").setValue(one);
   $("#two").setValue(two);
   var div = parseFloat(one) / parseFloat(two);
   $("#division").setValue(div);
} );
By ekaboom
#788402
This would work as well, right?
Code: Select all
function transferData() {
    var lastRow = $("#grid").getNumberRows();
    if( $('#Checkbox').getValue() == '1' ) {
$("#one").setValue($("#grid").getValue(lastRow, 1));
$("#two").setValue($("#grid").getValue(lastRow, 4));
    }};
$("#Checkbox").setOnchange(transferData);
It's based on setting the fields based on a checkbox being checked
By zainab
#788426
Yes, that would work perfectly well.

You can also merge your code with the calculation code from Amos,
Code: Select all
function transferData() {
    var lastRow = $("#expense_grid").getNumberRows();
    if( $('#Checkbox').getValue() == '1' ) {
		$("#one").setValue($("#expense_grid").getValue(lastRow, 3));
		$("#two").setValue($("#expense_grid").getValue(lastRow, 4));
    	var one = $("#one").getValue();
    	var two = $("#two").getValue();
    	var div = parseFloat(two) / parseFloat(one);
   		$("#division").setValue(div);  
    }
  else if( $('#Checkbox').getValue() == '0'){
  		$("#division").setValue('');
  	}
};
$("#Checkbox").setOnchange(transferData);
In the above code, the uncheck function of checkbox is also included. So, if the checkbox is unchecked then, the value of the division field will become empty.

Hope this helps.
#793719
I used your approach but it's not working with me.
I have a trigger that made an insertion for comments into my custom table and wants to show it in a grid as "Comments History" by using another trigger for "SELECT * FROM COMMENTS WHERE CaseNo = '@#APP_NUMBER'".The two records wee put "After Assignment" for every task.

INSERT trigger:
Code: Select all
/*ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);*/

//set to ID of the DB Connection if using an external DB
//$dbConnection = '2866080745958cd42ad81e7077878978'; 
//set to table name
$tableName = 'BEL_COMMENTS'; 						
$case = @@APP_NUMBER;

foreach (@@commentsGrid as $row) {
	$user = $row['commentUser'];
	$comment = $row['comments'];
	$action = $row['approvalDropDown'];
	$dateAdded = date('Y-m-d H:i:s');
	$stepAdded = $row['hiddenStep'];
	$updated = $row['hiddenUpdated'];
	
	if(!isset($updated) || empty($updated)){
		$row['hiddenUpdated'] = 1;
		$updated = 1;
		$sql = "INSERT INTO $tableName (CaseNo, User, Comment, Date_Added, Step_Added, Action, Updated) ".
			"VALUES ('$case', '$user', '$comment', '$dateAdded', '$stepAdded', '$action', 1)";
		$result = executeQuery($sql);
	
	}
} 
SELECT trigger:
Code: Select all
/*ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);*/

//set to ID of the DB Connection if using an external DB
//$dbConnection = '2866080745958cd42ad81e7077878978'; 
//set to table name
$tableName = 'BEL_COMMENTS'; 						

$case = @@APP_NUMBER;
$sql = "SELECT * FROM $tableName WHERE Updated = 1 AND CaseNo = ".$case."";

@@result = executeQuery($sql);
$num = count(@=commentsGrid);

foreach (@@result as $row) {
	//print $row['User']."--------".$row['Comment']."------";
	foreach(@@commentsGrid as $grid){
		$grid[$num]['commentUser'] = $row['User'];
		$grid[$num]['comments'] = $row['Comment'];
		//@=commentsGrid['approvalDropDown'] = $row['Action'];
		//print "this is grid---".$grid[$num]['commentUser']."------".$grid[$num]['comments']."----grid row end---<br/>"; 
	}
}
I wrote a javascript into the document ready DOM event to append a grid row for every (foreach) result returning from the "SELECT" query containing each row element into its specified corresponding HTML element.
Code: Select all
var lastRow = $("#commentsGrid").getNumberRows();
$("#commentsGrid").append(
  '<div class="grid-cell-responsive" style="display: inline-block; width: 20%;"><div class="row form-group">'+
	'<div id="[commentsGrid][lastRow][commentUser]" name="field-[commentsGrid][lastRow][commentUser]" class="pmdynaform-field-text  pmdynaform-view-label pmdynaform-field">'+
	'<div class="col-xs-12 col-sm-12 col-md-12 col-lg-12" style="padding: 0px;">'+
		'<div class="pmdynaform-label-options form-control">'+			
			'<span title="" class="label-text"></span>'+            
                '<input class="value-hidden" type="hidden" value="" id="form[commentsGrid][lastRow][commentUser]"'+
                ' name="form[commentsGrid][lastRow][commentUser]">'+
                          '<input class="label-hidden" type="hidden" value="" id="form[commentsGrid][lastRow][commentUser_label]"'+
                          ' name="form[commentsGrid][lastRow][commentUser_label]">'+
			'</div>'+		
		'</div>'+
	'</div>'+
  '</div>');
$("#commentsGrid").append(
	'<div class="grid-cell-responsive" style="display: inline-block; width: 60%;"><div class="row form-group">'+
		'<div id="[commentsGrid][lastRow][comments]" name="field-[commentsGrid][lastRow][comments]"'+
			'class="pmdynaform-field-textarea  pmdynaform-view-label pmdynaform-field">'+
			'<div class="col-xs-12 col-sm-12 col-md-12 col-lg-12" style="padding: 0px;">'+
				'<div class="pmdynaform-label-options form-control">'+			
					'<span title="" class="label-textarea"></span>'+        
                		'<input class="value-hidden" type="hidden" value="" id="form[commentsGrid][lastRow][comments]"'+
                		' name="form[commentsGrid][lastRow][comments]">'+
						'<input class="label-hidden" type="hidden" value="" id="form[commentsGrid][lastRow][comments_label]"'+
						' name="form[commentsGrid][lastRow][comments_label]">'+
				'</div>'+
			'</div>'+
		'</div>'+
	'</div>');
So, anyone can help me?
User avatar
By amosbatto
#793751
There are a couple issues with your code. First of all, if you are accessing a grid or the results of executeQuery("SELECT ..."), then you are dealing with an array of arrays, so you need to access it as @=variable. If you access it as @@variable, then you are converting it to a string.

From you code, it looks like you are accessing a PM Table, since you are not setting the unique ID of a database connection when you call executeQuery(). PM Tables prepend "PMT_" to their names, so your SQL statements need to access PMT_BEL_COMMENTS, not BEL_COMMENTS.

Try it this way:
Code: Select all
//set to table name
$tableName = 'PMT_BEL_COMMENTS';                   
$case = @@APP_NUMBER;

foreach (@=commentsGrid as $row) {
   $user = $row['commentUser'];
   $comment = $row['comments'];
   $action = $row['approvalDropDown'];
   $dateAdded = date('Y-m-d H:i:s');
   $stepAdded = $row['hiddenStep'];
   $updated = isset($row['hiddenUpdated']) ? $row['hiddenUpdated'] : '';
   
   if (empty($updated)) {
      $row['hiddenUpdated'] = 1;
      $updated = 1;
      $sql = "INSERT INTO $tableName (CaseNo, User, Comment, Date_Added, Step_Added, Action, Updated) ".
         "VALUES ('$case', '$user', '$comment', '$dateAdded', '$stepAdded', '$action', 1)";
      $result = executeQuery($sql); 
   }
} 
In your second trigger, you are accessing the commentsGrid incorrectly. When you do this:
Code: Select all
   foreach(@=commentsGrid as $grid){
Then $grid is a single row in the grid, so this won't work:
Code: Select all
      $grid[$num]['commentUser'] = $row['User'];
Also, if you save to a local variable, then it will be lost when the trigger terminates. You need to save to a case variable to make your changes permanent.

Are you trying to add additional rows to the grid? If so, this is what you want to do:
Code: Select all
//set to table name
$tableName = 'PMT_BEL_COMMENTS';                   

$case = @@APP_NUMBER;
$sql = "SELECT * FROM $tableName WHERE Updated = 1 AND CaseNo = ".$case."";

@=result = executeQuery($sql);
$num = count(@=commentsGrid);

foreach (@=result as $row) {
   //print $row['User']."--------".$row['Comment']."------";
   $num++;
   @=commentsGrid [$num] = array(
       'commentUser' => $row['User'],
       'comments' => $row['Comment'],
       'approvalDropDown' => $row['Action']
   );
   //print "this is grid---"[email protected]=commentsGrid[$num]['commentUser']."------"[email protected]=commentsGrid[$num]['comments']."----grid row end---<br/>";
}
Or are you trying to update existing rows in the grid?

For your JavaScript code, you should be using grid.addRow(). Manually inserting HTML in the grid won't update the model information in the DynaForm and your grid might not be saved correctly.
Code: Select all
$("#commentsGrid").addRow();
$("#commentsGrid").addRow();

The 500 Internal Server Error is the general catch[…]

Really very nice blog information for this one and[…]

This is a long, lengthy and cumbersome process. T[…]

Great read for when on the road. The book provides[…]