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, all the cases in the workspace must first deleted. To do this, go to Designer, then select each process and click on Delete Cases for each process.

SelectAndDeleteAllCasesInProcess.png

After deleting all the cases in all the processes, then go to the command line of the ProcessMaker server. Login to MySQL as the root user:

mysql -u root -p

Note: If using Windows, then first change to the directory where MySQL is installed before issuing the above command. For example:

cd C:\Bitnami\processmaker-3.3.0-1\mysql\bin

Once logged in to MySQL as root, then issue the following command:

UPDATE wf_workflow.APP_SEQUENCE SET ID=0;

Where wf_workflow is the name of the workspace's database.

Note: If the name of the database isn't known, then use this command to find it:

SHOW DATABASES;

If not wishing to delete all the existing cases, then restart the case numbering at a number which is greater than the existing number of cases. For example, if there are 843 cases, then the numbering can be restarted at 1000:

UPDATE wf_workflow.APP_SEQUENCE SET ID=1000;

Then, the next cases which is created will have the case number 1001.


Finally, exit MySQL:

exit;

If all cases haven't been deleted

If all the cases haven't been deleted from the workspace, then 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:

CaseNumber-DuplicateCaseNumberError.png

Unable to execute INSERT statement. 
[wrapped: Could not execute update [Native Error: Duplicate entry '1' for key 'INDEX_APP_NUMBER'] 
[User Info: INSERT INTO APPLICATION (`APP_UID`,`APP_TITLE`,`APP_DESCRIPTION`,`APP_NUMBER`,`APP_PARENT`,`APP_STATUS`,`APP_STATUS_ID`,`PRO_UID`,
`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.

CaseNumbers-DefinePMTable.png

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.

CaseNumbers-AddFirstRow.png

Then, edit Process A and add the following trigger:

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

@%caseNumber  = 1 + (int) $aRows[1]['LAST_PROCESSA_CASE'];
executeQuery("UPDATE PMT_CASE_NUMBERS SET LAST_PROCESSA_CASE=".@%caseNumber);

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.

CaseNumbers-ExecuteWhenCaseCreated.png

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

CaseNumbers-CaseWithCustomNumber.png

CaseNumbers-CustomeCaseNumberInCaseList.png

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 = ''; 
$query = "SELECT LAST_PROCESSB_CASE FROM PMT_CASE_NUMBERS";
$aRows = executeQuery($query);
if (empty($aRows)) {
    throw new Exception("Unable to get row number.");
} 

@%caseNumber  = 1 + (int) $aRows[1]['LAST_PROCESSA_CASE'];
executeQuery("UPDATE PMT_CASE_NUMBERS SET LAST_PROCESSB_CASE=".@%caseNumber);