Effective Sequence PeopleCode


There are cases where you'll need to automatically increment the value of an effective sequence (EFFSEQ) field through PeopleCode and decrement it when a user deletes a row.

The following code is my own solution to this problem. It ensures that the effective sequence always increments by 1 correctly, and correctly adjusts the effective sequence if a user deletes a row out of order.

The code goes in two events.

EFFSEQ.RowInsert

When a new row is inserted, set the effective sequence value to match the ActiveRowCount property of the rowset in the buffer:

Local Rowset &rs_EXAMPLE_ONLY = GetRowset();
 
EXAMPLE_ONLY.EFFSEQ = &rs_EXAMPLE_ONLY.ActiveRowCount;

As the active row count reflects the number of rows in the buffer, this will always be in sync with the effective sequence.

EFFSEQ.RowDelete

The above code works fine, provided a user doesn't go and delete a row out of sequence. For example if we have 3 rows:

EFFSEQ DATA
1 First Entry
2 Second Entry
3 Third Entry

And the user decides to delete Row 2 (EFFSEQ = 2) then the result is:

EFFSEQ DATA
1 First Entry
3 Third Entry

But what we want ideally is:

EFFSEQ DATA
1 First Entry
2 Third Entry

So that if another row is added, we see:

EFFSEQ DATA
1 First Entry
2 Third Entry
3 Fourth Entry
NOTE: ignore the text in the data, as the data doesn't relate to the effective sequence value. The text is there in this example so you can see how the effective sequence is changing.

To get the above working, use the following PeopleCode:

Local integer &i;
Local Record &rec_EXAMPLE_ONLY;
Local Rowset &rs_EXAMPLE_ONLY = GetRowset();
 
For &i = 1 To &rs_EXAMPLE_ONLY.ActiveRowCount
   &rec_EXAMPLE_ONLY = &rs_EXAMPLE_ONLY(&i).EXAMPLE_ONLY;
   If &rec_EXAMPLE_ONLY.EFFSEQ.Value > EXAMPLE_ONLY.EFFSEQ Then
      /* Decrement by 1 as the user has deleted effective sequence out of order */
      &rec_EXAMPLE_ONLY.EFFSEQ.Value = &rec_EXAMPLE_ONLY.EFFSEQ.Value - 1;
   End-If;
End-For;

This code, simply finds all effective sequences greater than the one deleted and decrements them all by 1.


Revision #1
Created Sun, Aug 4, 2019 11:52 PM by PeopleSoft Wiki
Updated Sun, Aug 4, 2019 11:57 PM by PeopleSoft Wiki