I’ve had this peculiar requirement wherein I have a character field that is some 20 characters long. A custom record that was created – CUSTOM_REC – has this field as the key field.
Now, this field is available in a grid and I can enter either characters or numbers of my choice. Before that, each time a new row is inserted, the character field starts at 001 and goes all the way upto 999.
Here’s the catch – every time I insert a row, the PeopleCode needs to check a few things:
- Check for the maximum number in the database for CUSTOM_REC – a regexp will have to be in place to filter non-numeric data
- Check if the current value that has been incremented is greater than the maximum value that is currently available in the grid
- Once the maximum value is reached, increment that by 1 and assign to the new row that was inserted.
This PeopleCode was written in the RowInsert event of CUSTOM_REC.
&RS1 = GetLevel0()(1).GetRowset(Scroll.CUSTOM_REC); If None(&Max_Value) Then SQLExec("SELECT MAX(FIELD) FROM PS_CUSTOM_REC where REGEXP_LIKE(FIELD, '^[[:digit:]]+$')", &sMax_ID); End-If; &RS1 = GetLevel0()(1).GetRowset(Scroll.CUSTOM_REC); &Max_Value = Value(&sMax_ID); For &I = 1 To &RS1.ActiveRowCount If IsNumber(&RS1(&I).CUSTOM_REC.FIELD.Value) Then &Cur_ID = Value(&RS1(&I).CUSTOM_REC.FIELD.Value); If &Cur_ID >= &Max_Value Then &Max_Value = &Cur_ID; End-If; End-If; End-For; &sFinalValue = Rept("0", 3 - Len(String(&Max_Value + 1))) | (&Max_Value + 1); &RS1(CurrentRowNumber()).CUSTOM_REC.FIELD.Value = &sFinalValue;
Hope this helps! 🙂 Have fun!!