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:

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

But what we want ideally is:

So that if another row is added, we see:

Note, ignore the data text, as the data usually doesn't relate to the effective sequence value. The correlation is so you can see how the rows are changing only.

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.

I might have overlooked something obvious with the above code, if so, please comment and let me know.

Comment