ProcessMaker Forum | BPM Workflow Simplified

Posted: **Mon Feb 11, 2019 2:47 am**

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

Posted: **Mon Feb 11, 2019 3:04 am**

Sorry,

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

Posted: **Mon Feb 11, 2019 4:33 pm**

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

Posted: **Mon Feb 11, 2019 8:43 pm**

Hi,

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

Posted: **Mon Feb 11, 2019 9:45 pm**

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.

Posted: **Mon Feb 11, 2019 10:32 pm**

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?

Posted: **Mon Feb 11, 2019 11:00 pm**

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.

Posted: **Tue Feb 12, 2019 12:59 am**

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

Posted: **Tue Feb 12, 2019 2:58 am**

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

Posted: **Tue Feb 12, 2019 6:29 am**

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

Posted: **Tue Feb 12, 2019 11:10 pm**

Hi Amos,

What am I doing wrong, can you help me?

Posted: **Tue Feb 12, 2019 11:11 pm**

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.

ProcessMaker Forum | BPM Workflow Simplified

How to insert into APP_DOC_UID in grid to PM table

How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table

Re: How to insert into APP_DOC_UID in grid to PM table