[simon581923]

Hi, I've designed a Dynaform

My boss asked me to add a serieal number on the upper left of the Dynaform page in the red block.
The format of the Series Number is YYYYMMDD###, ### is digit 0~9.
However, I'm not familar with JavaScript, and Also a beginner of ProcessMaker. But, I have almost 3 years of experence on ASP.NET/C#.
Can someone advice me how to implement the serial number we want in ProcessMaker?

[amosbatto]

1. Create a PM Table named "SERIAL" with an integer field named "LAST_NUMBER".
After creating the table, then add one row of data to the table where you set LAST_NUMBER to 1234 (or any number you want).

2. Add a variable to your process named "serialNo".

3. Then, create a trigger with this code:

Code: Select all

$sql = "SELECT LAST_NUMBER FROM PMT_SERIAL";
$aResult = executeQuery($sql);
if (!is_array($aResult) or count($aResult) == 0) {
  throw new Exception("No records found in the PMT_SERIAL table.");
}
elseif (count($aResult) > 1) {  
  throw new Exception("There should only be 1 record in the PMT_SERIAL table.");
}

@%serialNo = $aResult[1]['LAST_NUMBER'] + 1;
$sql = "UPDATE LAST_NUMBER SET LAST_NUMBER=".@%serialNo;
executeQuery($sql);

4. Set this trigger to fire when a case is created.

5. Then, in your DynaForm, go to the text field and set its "variable" property to "serialNo".

[amosbatto]

By the way, the above code should not be used if you might have multiple users creating new cases at the same time. Will you have that situation?

[simon581923]

Hi, Amos,
Thanks a lot for your code.
Refer to your code and some others request, I wrote my code as the following:

Code: Select all

$Today = FORMATDATE(getCurrentDate(),"yyyymmdd");
$sql = "SELECT LPAD(LASTNUMBER+1, 3, '0') LASTNUMBER FROM PMT_SERIES WHERE SERIES_TYPE='STAMP' AND SERIES_YM='".$Today."'";
$aResult = executeQuery($sql);
if (!is_array($aResult) or count($aResult) == 0) {
  @@NoSeries = "001";
  $sql = "INSERT PMT_SERIES VALUES('STAMP','".$Today."',1)";
  executeQuery($sql);
}
elseif (count($aResult) > 1) {  
  throw new Exception("ERROR#0001 There should only be 1 record in the PMT_SERIAL table.");
}
elseif (count($aResult) == 1) {
  @@NoSeries = $aResult[1]['LASTNUMBER'];
  $sql = "UPDATE PMT_SERIES SET LASTNUMBER=LASTNUMBER+1 WHERE SERIES_TYPE='STAMP' AND SERIES_YM='".$Today."'";
  executeQuery($sql);
}

@@textVar005 = $Today . @@NoSeries;

My boss asked to arrange the serial number format as YYYYMMDD0001. The result is as attached picture:


I also created a new table pmt_series to keep the last serial number as the following SQL script:

Code: Select all

CREATE TABLE `pmt_series` (
  `SERIES_TYPE` varchar(10) NOT NULL,
  `SERIES_YM` varchar(8) NOT NULL,
  `LASTNUMBER` int(4) DEFAULT NULL,
  PRIMARY KEY (`SERIES_TYPE`,`SERIES_YM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

During these days, I work with Processmaker, and amazing how Processmaker can do for workflow control.