[chanatsamon]

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', '')]

[chanatsamon]

Sorry,

2. But $fileNo no data, Debugger show this error :

[ziadeh]

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

[chanatsamon]

Hi,

I addition APP_AUTO is the primary key already.
However, field fileNo is no data.
Do I declare a variable wrong?

[amosbatto]

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.

[chanatsamon]

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?

[amosbatto]

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.

[chanatsamon]

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

[amosbatto]

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

[chanatsamon]

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

[chanatsamon]

Hi Amos,

What am I doing wrong, can you help me?

[amosbatto]

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.