[ekaboom]

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

[zainab]

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;
@@one=@=grid[$num]['name'];
@@two=@=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.

[ekaboom]

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

[zainab]

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.

[ekaboom]

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.

[ekaboom]

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;
@@one=@#grid[$num]['number1'];
@@two=@#grid[$num]['number2'];
@@addition=@#grid[$num]['number1']+@#grid[$num]['number2'];
@@division=@#grid[$num]['number1']/@#grid[$num]['number2'];
@@multiplication=@#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;
@@one=@#grid[$num]['number1'];
@@two=@#grid[$num]['number2'];
@@addition= @#one + @#two;
@@division = @#one / @#two;
@@multiplication = @#one * @#two;

Thanks again.

[zainab]

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.

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 (53.96 KiB) Viewed 13055 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 (10.19 KiB) Viewed 13055 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.

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

[ekaboom]

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.

[ekaboom]

Just to clarify.

This still works, right?

Code: Select all

@@division = @#one / @#two;

Is this the best way to refer to number variables outside of a grid?

[amosbatto]

Yes, that is the best way to refer to variables is you want floating point numbers.

[ekaboom]

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;
@@one=@=grid[$num]['name'];
@@two=@=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?

[amosbatto]

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);
} );

[ekaboom]

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

[zainab]

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.

[MostafaTawfik]

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?

[amosbatto]

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---".@=commentsGrid[$num]['commentUser']."------".@=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();