PeopleSoft Manual Auto Numbering

Record Setup

My setup for an auto numbering in a peoplesoft record was simply to define the next value at save time.  There is another way to do this that includes built in functions and requires more setup in some of the PeopleSoft delivered tables.  However, my approach was for a small number of users and records so I used the simpler approach.  The Record just needs to have an ID value that will trigger some PeopleCode at save time.  Create your new record with the default value with a trigger such as "New".

People Code

Now in the PeopleCode we need to setup some logic in both SearchInit and SavePreChange.  The SearchInit checks the PeopleSoft variable Mode and if it's (A)dd Mode then we are going to make sure ID value is set to the same default value "NEW" because the record default is not used in this case.  The SavePreChange code will be triggered when the new record is attempting to save to the database.  The logic will check for our "NEW" value and if the ID is still that we select the max value on the table and add 1 before saving.

SearchInit

If %Mode = "A" Then
   MCM_MRF_IP_SCN.MCM_IP_ID.Value = "NEW";
   SetSearchDialogBehavior(0);
End-If;

SavePreChange

Local string &nextId;

If MCM_MRF_IP_SCN.MCM_IP_ID.Value = "NEW" Then
   SQLExec("Select max(MCM_IP_ID)+1", &nextId);
   MCM_MRF_IP_SCN.MCM_IP_ID.Value = &nextId;
End-If;

This code is limited to only numeric values in the ID column.  In our environment we are using a char to hold the numeric and our customer wants leading zeros on all the numbers.  There was also a special condition requested for the customer to have obsolete or test data in the table that would have been prefixed with an alpha char i.e. ID = "X01" or test data may be ID = "T01".   I was asked to ignore these cases that would have caused the query to fail anyways because they are not numeric and arithmetic can't be done on the value returned.  The final solution looked like the following:

Query for ONLY numeric ID's and add Leading Zeros

Local string &nextId;
Local string &qry;

If MCM_MRF_IP_SCN.MCM_IP_ID.Value = "NEW" Then
   &qry = "Select max(MCM_IP_ID)+1 from %table(MCM_MRF_IP_SCN) ";
   &qry = &qry + " where REGEXP_LIKE(MCM_IP_ID, '^[[:digit:]]+$') ";
   SQLExec(&qry, &nextId);
   &nextId = Rept("0", 3 - Len(&nextId)) | &nextId;
   MCM_MRF_IP_SCN.MCM_IP_ID.Value = &nextId;
End-If;