Update a record number based on conditions

[Jarryd_Williams]

I am trying to do the following in Airtable:

If Person A receives a warning for “Being Late”, the record is marked as “1” or “First”. If the same warning occurs again, it’s marked as “2” or “Second”

If Person A Receives additional warnings for, let’s say “Stealing” or “Damaging Property”, it will mark each of these instances as “1” or “First”

If the Record was to expire: e.g.
Person A: Being Late “First” Expires
Person A: Being Late “Second” Active,

Then the Record that is “Second” becomes “First”

Is something like this possible in Airtable? Maybe I am just missing something or my creative juices have just run dry.

[TheTimeSavingCo]

Hey Jarryd, I think we could only do this with a script due to the need to re-order records once they expire. Let me know if this is something you’d like to explore.

Could you tell me more about why we needed the records to be updated with sequential numbers? Just asking as, if we only need the total count of active warnings per type, we could get that done really easily with a pivot table and the right base design