Set a custom case number

From pmusers
Jump to: navigation, search

Restarting case number counting

When new cases are created, ProcessMaker reads the value of the APP_SEQUENCE.ID field in the database, which is set to the last case number and adds one for the new case number.

It is possible to reset the counting of cases by setting the value of the APP_SEQUENCE.ID field to 0, however, this will cause an error when creating a new case, because ProcessMaker will try to create case number 1 and a case with the number 1 already exists, so the following error will appear:


Unable to execute INSERT statement. 
[wrapped: Could not execute update [Native Error: Duplicate entry '1' for key 'INDEX_APP_NUMBER'] 
`APP_PROC_STATUS`,`APP_PROC_CODE`,`APP_PARALLEL`,`APP_INIT_USER`,`APP_CUR_USER`,`APP_CREATE_DATE`,`APP_INIT_DATE`,`APP_UPDATE_DATE`,`APP_DATA`,`APP_PIN`) VALUES ('9119440805baab723b3fbb6003129045','#1','',1,'','DRAFT',1,'2613247125baaaaa536c7f2082990019','','','N','00000000000000000000000000000001','00000000000000000000000000000001','2018-09-25 22:30:59','2018-09-25 22:30:59','2018-09-25 22:30:59','a:2:{s:10:\"APP_NUMBER\";s:1:\"1\";s:3:\"PIN\";s:4:\"0KD3\";}','069d605d2b66bbbcd36a0892ea22ca44')]]

Case numbers must be unique. To get around this problem, either all the cases need to be deleted from the APPLICATION table or the value of the APP_SEQUENCE.ID field needs to be set to a number which is larger than all the existing case numbers. For example, if the last case number was 1380, then the APP_SEQUENCE.ID field could be set to 2000 and the next case number will be 2001.

Changing a case number

It is not recommended to change the case number of an existing case. If absolutely necessary, it can be done by changing the value of the APPLICATION.APP_NUMBER field in the database, but then change the APP_CACHE_VIEW.APP_NUMBER field to that same number in all the records for that case. There should be at least one record for each task in the case and there could be more if the case was reassigned or paused.

Custom case numbers

If needing a custom case number, it is recommended to use the case title instead to set a custom number. By default, the case title is set to the case number preceded by # (a hash sign), but it can be changed by right clicking on a task in the process designer and selecting Properties in the context menu. In the Activity Properties dialog box, go to Case Labels > Title and enter a value or a case variable to insert in the case title. When the case gets to that task in the process, its title will be changed to that custom value.

Separate case numbering in two different processes

The following example shows how to use the case title to separately number the cases in Process A and Process B, so the user can look at the case title and know how many cases have been created in each process.

Process: CaseNumbers-Set_custom_case_number1.pmx (right click and select Save link as)
Table: CaseNumbers-PMTable.pmt (right click and select Save link as)
Tested in: ProcessMaker 3.2.3 Community with Debian 9.5

First, create a PM Table, named "CASE_NUMBERS" with the LAST_PROCESSA_CASE and LAST_PROCESSB_CASE field to keep track of the last case number in each process.


Remember that when this PM Table is created, it will automatically have "PMT_" prepended to its name, so it will be named PMT_CASE_NUMBERS in the database.

Then, add a single row of date to the PMT_CASE_NUMBERS table, where the value of the LAST_PROCESSA_CASE and LAST_PROCESSB_CASE fields is set to 0. This will ensure that the first case created in each process with have a case title of 1.


Then, edit Process A and add the following trigger:

@%caseNumber = ''; 
$aRows = executeQuery($query);
if (empty($aRows)) {
    throw new Exception("Unable to get row number.");

@%caseNumber  = 1 + (int) $aRows[1]['LAST_PROCESSA_CASE'];

This code looks up the last case number for Process A in the PMT_CASES_NUMBERS.LAST_PROCESSA_CASE field and adds one to set the new case number in the @%caseNumber variable. Then, it writes that new case number in the PMT_CASES_NUMBERS.LAST_PROCESSA_CASE field, so the next case will increment from that number.

Set this trigger to fire when new cases are created by right clicking on an empty part of the process map and selecting Edit Process from the dropdown menu. In the "Process Properties" dialog box, select the trigger in the Execute a trigger when a case is created: dropdown box.


Now, when a new case is created in Process A, the title of the case will be separately numbered, starting from the number 1.



Separate case numbering can be enabled in the Process B in a similar way. The trigger in Process B needs to consult the PMT_CASES_NUMBERS.LAST_PROCESSB_CASE field:

@%caseNumber = ''; 
$aRows = executeQuery($query);
if (empty($aRows)) {
    throw new Exception("Unable to get row number.");

@%caseNumber  = 1 + (int) $aRows[1]['LAST_PROCESSA_CASE'];