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

Moderator: amosbatto

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.
#822853
Hi,

I upgrade Processmaker now version 3.2.1, I want to keep data APP_DOC_UID in grid,
But I don't know what variables to use.
Could you advise me?

1. I create trigger before assignment.
Code: Select all
$caseId = @@APPLICATION;
$caseNo = @@APP_NUMBER;
$gridId = "contractorList"; //set to Id of grid field
$fileId = "contactFile";    //set to Id of the file field in the grid

//first delete all existing records from this case, if updating:  
executeQuery("DELETE FROM PMT_MY_GRID WHERE APP_UID='$caseId' ");

//then insert a new record for each row in the "contractorList" grid
for ($rowNo = 1; $rowNo <= count(@=contractorList);$rowNo++) 
{
   $remark = @=contractorList[$rowNo]['remark'];
   $fileNo = @=contractorList[$rowNo]['contactFile'];

   $query = "INSERT INTO PMT_MY_GRID (APP_UID, APP_NUMBER, ROW_NO, remark ,APP_DOC_UID)
      		 VALUES ('$caseId', '$caseNo', '$rowNo', '$remark', '$fileNo')";
   @@retInsert = executeQuery($query);
}
2. But $fileId no data, Debugger show this error :
Code: Select all
Could not execute update [Native Error: Duplicate entry '' for key 'PRIMARY'] 
[User Info: INSERT INTO PMT_MY_GRID (APP_UID, APP_NUMBER, ROW_NO, remark ,APP_DOC_UID) 
VALUES ('9792480465c611712bf5648050054581', '40', '1', 'test2', '')]
Attachments
Debugger.JPG
Debugger.JPG (42.5 KiB) Viewed 320 times
Debugger2.JPG
Debugger2.JPG (57.67 KiB) Viewed 320 times
#822864
If the APP_UID is the primary key in the table you won't be able to insert another row with the same primary key, if not try to include the primary key name and add null in the first Values:
Code: Select all
   $query = "INSERT INTO PMT_MY_GRID (ID, APP_UID, APP_NUMBER, ROW_NO, remark ,APP_DOC_UID)
      		 VALUES (null, '$caseId', '$caseNo', '$rowNo', '$remark', '$fileNo')";
   @@retInsert = executeQuery($query);
#822869
chanatsamon wrote: I addition APP_AUTO is the primary key already.
However, field fileNo is no data.
Do I declare a variable wrong?
If APP_AUTO is your primary key field, then you should make it an auto-increment field. That way the field will automatically insert a new value when you do an insert like this:
Code: Select all
   $query = "INSERT INTO PMT_MY_GRID (APP_UID, APP_NUMBER, ROW_NO, remark ,APP_DOC_UID)
      		 VALUES ('$caseId', '$caseNo', '$rowNo', '$remark', '$fileNo')";
Also, you need to escape the content filled by users:
Code: Select all
   $remark = addslashes(@=contractorList[$rowNo]['remark']);
   $fileNo = addslashes(@=contractorList[$rowNo]['contactFile']);
You can use mysql_real_escape_string() in place of addslashes() if you are using PHP 5.6.
#822872
Hi,

I'm sorry, I'm not good at English.
What I want is keep data APP_DOC_UID from contractorList_1_contactFile.
How do I write PHP?
Attachments
Debugger3.JPG
Debugger3.JPG (44.83 KiB) Viewed 298 times
MY_GRID.JPG
MY_GRID.JPG (28.99 KiB) Viewed 298 times
(25.13 KiB) Downloaded 8 times
(3.81 KiB) Downloaded 7 times
#822873
This is much easier if you upgrade ProcessMaker, so you can use a MultipleFile field instead of a File field.

If you must use a File field, then here would be your trigger code:
Code: Select all
$caseId = @@APPLICATION;
$caseNo = @@APP_NUMBER;
$gridId = "contractorList"; //set to Id of grid field
$fileId = "contactFile";    //set to Id of the file field in the grid

//first delete all existing records from this case, if updating:  
executeQuery("DELETE FROM PMT_MY_GRID WHERE APP_UID='$caseId' ");

$c = new \Cases();
$aVars = $c->LoadCase(@@APPLICATION)['APP_DATA'];

//then insert a new record for each row in the "contractorList" grid
for ($rowNo = 1; $rowNo <= count(@=contractorList); $rowNo++) 
{
   $remark = addslashes(@=contractorList[$rowNo]['remark']);
   $fileId  = '';

   if (isset($aVars['contractorList_'.$rowNo.'_contactFile']) and 
        $aVars['contractorList_'.$rowNo.'_contactFile'] != '[]') 
   {
       $fileId = json_decode($aVars['contractorList_'.$rowNo.'_contactFile'])[0];
       $fileName = json_decode($aVars['contractorList_'.$rowNo.'_contactFile_label'])[0];
   }

   $query = "INSERT INTO PMT_MY_GRID (APP_UID, APP_NUMBER, ROW_NO, remark ,APP_DOC_UID)
      		 VALUES ('$caseId', '$caseNo', '$rowNo', '$remark', '$fileId')";
   @@retInsert = executeQuery($query);
}
Set this trigger to fire in the step after the Dynaform or (before assignment if the Dynaform is the last task in the task).
Do NOT set this trigger to fire immediately after the Dynaform, because at that point the data isn't yet saved.
#822879
Hi Amos,
Thank you very much, I can keep APP_DOC_UID to PM table.

I found another problem is to delete row.
1. I Add data 3 row
2. I Delete row no 2 (contractorList_2_contactFile)
3. But viewed from debugging, data is not deleted
Attachments
Debugger4.JPG
Debugger4.JPG (25.95 KiB) Viewed 292 times
Debugger5.JPG
Debugger5.JPG (45.79 KiB) Viewed 292 times
#822886
Here would be your trigger if you are using a MultipleFile field in a grid:
Code: Select all
$caseId = @@APPLICATION;
$caseNo = @@APP_NUMBER;
$gridId = "contractorList"; //set to Id of grid field
$fileId = "contactFile";    //set to Id of the file field in the grid

//first delete all existing records from this case, if updating:  
executeQuery("DELETE FROM PMT_MY_GRID WHERE APP_UID='$caseId' ");

//then insert a new record for each row in the "contractorList" grid
for ($rowNo = 1; $rowNo <= count(@=contractorList); $rowNo++) 
{
   $remark = addslashes(@=contractorList[$rowNo]['remark']);
   
   foreach (@=contractorList[$rowNo]['contactFile'] as $oFile) {
       $fileId = $oFile->appDocUid;
       $fileName = addslashes($oFile->name);
       $query = "INSERT INTO PMT_MY_GRID (APP_UID, APP_NUMBER, ROW_NO, remark ,APP_DOC_UID)
      		 VALUES ('$caseId', '$caseNo', '$rowNo', '$remark', '$fileId')";
       @@retInsert = executeQuery($query);
   }
}
#822890
I follow your advice. But why the data is not recorded?

Trigger before assignment.
Code: Select all
$caseId = @@APPLICATION;
$caseNo = @@APP_NUMBER;
$gridId = "contractorList"; //set to Id of grid field
$fileId = "contactFile";    //set to Id of the file field in the grid

//first delete all existing records from this case, if updating:  
executeQuery("DELETE FROM PMT_MY_GRID_2 WHERE APP_UID='$caseId' ");

//then insert a new record for each row in the "contractorList" grid
for ($rowNo = 1; $rowNo <= count(@=contractorList); $rowNo++) 
{
   $remark = addslashes(@=contractorList[$rowNo]['remark']);
   
   foreach (@=contractorList[$rowNo]['contactFile'] as $oFile) {
       $fileId = $oFile->appDocUid;
       $fileName = addslashes($oFile->name);
       $query = "INSERT INTO PMT_MY_GRID_2 (APP_UID, APP_NUMBER, ROW_NO, remark ,APP_DOC_UID)
      		 VALUES ('$caseId', '$caseNo', '$rowNo', '$remark', '$fileId')";
       @@retInsert = executeQuery($query);
   }
}
Attachments
Debugger6.JPG
Debugger6.JPG (24.73 KiB) Viewed 285 times
Debugger7.JPG
Debugger7.JPG (30.44 KiB) Viewed 285 times
#822900
Oh sorry, MultipleFiles are stored in an array, not in an object.

Try it this way:
Code: Select all
$caseId = @@APPLICATION;
$caseNo = @@APP_NUMBER;

//first delete all existing records from this case, if updating:  
executeQuery("DELETE FROM PMT_MY_GRID_2 WHERE APP_UID='$caseId' ");

//then insert a new record for each row in the "contractorList" grid
for ($rowNo = 1; $rowNo <= count(@=contractorList); $rowNo++) 
{
   $remark = addslashes(@=contractorList[$rowNo]['remark']);
   
   foreach (@=contractorList[$rowNo]['contactFile'] as $oFile) {
       $fileId = $oFile['appDocUid'];
       $fileName = addslashes($oFile['name']);
       $query = "INSERT INTO PMT_MY_GRID_2 (APP_UID, APP_NUMBER, ROW_NO, remark ,APP_DOC_UID)
      		 VALUES ('$caseId', '$caseNo', '$rowNo', '$remark', '$fileId')";
       @@retInsert = executeQuery($query);
   }
}
You can figure out these problems if you enable "Debug Mode" in the Process Properties.

Learn how to increase the max upload size setting […]

To fix this, you will have to edit the code for[…]

Using RTL.CSS in dynaforms

I reinstalled the processmaker and used the po fil[…]

Hi Member's I am trying to create a Dashboard of […]