First of all you will have to use PHP to query the database and generate the file, so this can't be done in JavaScript (except if you use JavaScript to call REST endpoints). You will have to install the PHPExcel library on your ProcessMaker server if you want to generate Excel files with PHP, but I recommend using a CSV (comma separated values) file, since it can created without importing external libraries and any spreadsheet program can open it.
Create a trigger something like this:
Code: Select allfunction prepareCsvField($str) {
$str = str_replace('"', '""', $str);
if (preg_match('/[,;"\n\r]/', $str) or trim($str) != $str) {
$str = '"' . $str . '"';
}
return $str;
}
$inpDocId = '1234567890abcdef234567890abcdef'; //set to ID of Input Document in process
if (!empty(@@idToFind)) {
$filePath = tempnam(sys_get_temp_dir(), "pm_") . ".cvs";
$fFile = fopen($filePath, "w");
if (!$fFile) {
throw new Exception("Error opening temporary file '$filePath'");
}
$id = mysql_real_escape_string(@@idToFind);
$sql = "SELECT FIELD1, FIELD2, FIELD3 FROM PMT_MYTABLE WHERE ID_FIELD='$id'";
$aRows = executeQuery($sql);
if (!empty($aRows)) {
//write first line with column headers:
$line = '';
foreach ($aRows[1] as $fieldName => $value) {
$line .= ($line == '' ? '' : ',') . prepareCsvField($fieldName);
}
fwrite($fFile, $line . "\n");
//write a line for each record returned by SQL query:
foreach ($aRows as $aRow) {
$line = '';
foreach ($aRow as $field => $value) {
$line .= ($line == '' ? '' : ',') . prepareCsvField($value);
}
fwrite($fFile, $line . "\n");
}
fclose($fFile);
$filename = pathinfo($filePath, PATHINFO_BASENAME);
@@fileId = PMFAddInputDocument($inpDocId, null, 1, 'INPUT', '', 'Add',
@@APPLICATION, @%INDEX, @@TASK, @@USER_LOGGED, 'file', $filePath);
if (!empty(@@fileId)) {
$g = new G();
@@linkUrl = ($g->is_https() ? 'https://' : 'http://') . $_SERVER['HTTP_HOST'] .
'/sys' . @@SYS_SYS . '/en/neoclassic/cases/cases_ShowDocument?a=' . @@fileId;
@@linkLabel = $filename;
}
unlink($filePath);
}
Set this trigger to fire before the Dynaform.
Then, in your Dynaform, create a Link field which has its properties set to:
display text: @@linkLabel
href: @@linkUrl
formToLinkToCsvFile.png (15.38 KiB) Viewed 3108 times
If you want to dynamically generate the CSV file from fields inside the same Dynaform, you should use a Submit button to submit the Dynaform and then use PMFRedirectToStep() to return to the DynaForm. See:
Redirection in Triggers with Submit buttons.
If you don't want to use browser redirection with PMFRedirectToStep() because you are using the ProcessMaker Mobile App, then you can use
JavaScript to call REST endpoints. First, call POST /cases/{app_uid}/variable to send the "idToFind" variable for the SQL query to the case. Then, call POST /cases/{app_uid}/execute-trigger/{tri_uid} endpoint to execute the above trigger. Then, call the GET /cases/{app_uid}/variables endpoints to get the values of the "linkUrl" and "linkLabel" variables and use the link.setHref() and control.setText() functions in set these properties in the Link field.