PeopleCode | Automatically defaulting a field to the next available number.
A. Sequential Numbering
A common system requirement is to set up a field that defaults to the next available numeric value, such as an Employee ID or a Customer ID. This field often forms part of a primary key, which means it needs to be unique for each entity created in the system.
Less experienced developers will often create code such as the following:
SQLExec("select last_num from ps_next_num_tbl", &last_num);
&next_num = &last_num + 1;
SQLExec("update ps_next_num_tbl set last_num = :1", &next_num);
There’s nothing hugely wrong about writing code along these lines, however there is a much easier way to set the ‘next’ number for a sequential field.
B. GetNextNumber
Before using one of the functions in the ‘GetNextNumber’ family, you will need to set up a place in the database to store the next available number. This is usually done in a custom installation record. For an example of a delivered ‘next ID’ field, have a look at the PS_INSTALLATION record. This contains many fields used for sequential numbering (the fields are often called xxx_LAST).
As the name implies, the ‘GetNextNumber’ function will retrieve and increment the next available number as stored in the configuration table. The function requires two parameters: (i) the name of the record/field containing the next available ID, and (ii) the maximum value allowed (if the result exceeds this maximum, an error will be displayed).
Once the function has been called, you can check the return value to see if the function worked as expected. A positive number represents the actual assigned ID, as well as confirming that the call was successful. A negative number indicates that a problem occurred. The code snippet below executes GetNextNumber, and then checks the return value to see if the assignment was successful:
Local number &next_num = GetNextNumber(NEXT_NUM_TBL.NEXT_NUM, 999999); Evaluate &next_num When = %GetNextNumber_SQLFailure /* SQL Select problem */ When = %GetNextNumber_TooBig /* maximum value exceeded */ When = %GetNextNumber_NotFound /* no number found */ When-Other /* number successfully populated */ End-Evaluate;
For simple requirements, ‘GetNextNumber’ will do the job fine. By ‘simple’, we are talking about a small number of users who only access the page occasionally. If however, you have a more complex requirement, you might strike a couple of problems with ‘GetNextNumber’.
Firstly, because the update of the sequence number does not occur until the page is saved, this means the user holds an exclusive lock on the table. Any other user wanting to generate an ID for their session will have to wait until the first user saves or exits the page. Secondly, ‘GetNextNumber’ only allows you to increment the sequential number by a value of ‘1’.
Fortunately there are a couple of related methods that help to get around these problems.
C. GetNextNumberWithGaps
This works in a similar way to ‘GetNextNumber’, except that it provides much greater power in terms of setting the sequential number. Up to five parameters can be provided:
(i) The name of the record/field storing the next available ID
(ii) The maximum number allowed for the field
(iii) The incremental value for the sequential field
(iv) An optional SQL ‘where’ clause. This allows you to set up a table with different sequential numbers, possibly on a department or even individual Employee ID basis. The SQL where clause can be used to restrict the ID to the current record.
(v) One or more parameters for the SQL where clause
Here’s an example of calling the function (the return values of the function are similar to ‘GetNextNumber’):
Local number &employee_next_id = GetNextNumberWithGaps(
NEXT_NUM_TBL.NEXT_NUM, 999999, 100,
"where emplid = :1", %EmployeeID);
Similar to ‘GetNextNumber’, ‘GetNextNumberWithGaps’ will not commit the ID until the page is saved, potentially locking other users out of the same page. This, of course, brings us to the third and final of the ‘GetNextNumber’ functions…
D. GetNextNumberWithGapsCommit
As suggested by the long-winded name, our next function is the ‘granddaddy’ of the ‘GetNextNumber’ family. It works exactly the same way as ‘GetNextNumberWithGaps’, meaning it is not necessary to even provide an example. However, ‘GetNextNumberWithGapsCommit’ has one important difference: it performs an immediate commit on the underlying ‘next ID’ table. This function is therefore ideal for those situations where multiple users are accessing the same next ID table.
‘GetNextNumberWithGapsCommit’ has one downside. This occurs in the specific case of the next ID being created on initial entry to the page. Because the ID is immediately reserved for use, if the user happens to exit the page without saving, the particular ID reserved for that user will be lost forever. The ‘next ID’ table has already been incremented and committed, so this table does not ‘know’ that the source component has been cancelled.
As a result, you may end up with gaps in your sequential numbering. This may or may not be a problem depending on your particular requirement. If this could be a problem, the easiest solution is to call the numbering function on save of the component, not on entry.
See Also:
Tip 059: Table Locks