Autonumbering new records: +1 from this pt forward to a column of non sequential numbers

[Russ_Layton]

I am using AirTable for a Customer Service Inquiry tracking database.

I want to be able to enter each “Case” (a record or row) into an AirTable base and for it to be given a “Case #” that is +1 from the highest number listed in the Primary Field column (column 1).

The challenge is that I have over 1000 records in the base and these records do not start at 1 and there are gaps in the list here and there as well.

I have tried using autonumber and have messed around with zapier with no luck. I am hopeful that someone in this community has figured out an elegant solution to this problem.

I want a formula or zap or something that when a new record is created, the first thing that happens is that the primary field is given a new record number that is +1 from the highest number found in this primary field (column 1).

[Elias_Gomez_Sai]

Is this what you want?

[Russ_Layton]

Unfortunately, no.

I do not want to change anything in the list of existing numbers in the primary field as these are historical cases where the numbers shown are records that cannot change.

I need to have the list continue from this point forward in a sequential fashion (+1) from the highest number currently in the list.

In the example you sent, I would need the next record added to be “Case 8” and the other cases already in the list (Case 2, Case 3, Case 5) to be unchanged.

[Jeremy_Oglesby]

I think you could do something like this:

1. Separate out the number from the case name into its own field (column), if it isn’t already (let’s call it {Old Case Num} for example)
   (this can be done with a FIND() or RIGHT() formula if doing so manually would be tedious)

2. Order your existing records by those case numbers in Ascending order (1 at top through highest at bottom)

3. Create your Autonumber field now (let’s assume for example that the last # in it is 1321)

4. Look at your last record’s case # (let’s assume for example that its 1483), and create a formula field (let’s call it {New Case Num}) that takes your autonumber field and adds enough to make it even to the case number for that record.
   (So in this case, autonumber=1321 and case#=1483, so formula would be {Autonumber} + 162)

5. In your Primary field that used to have “Case #1”, “Case #2”, etc, make it a Formula field with this formula (again, I’ll use example numbers):

"Case #" &
IF(
   {Old Case Num} < 1484,
   {Old Case Num},
   {New Case Num}
)

Now any new records made will always use the {New Case Num} field for their case number, which will continue to increment up from 1484 – and your old cases will still use their original case numbers.

[Russ_Layton]

Thanks… this method works!

Now the only problem I have is if someone makes a mistake and deletes a record, a new record will be created with a gap in between the case #'s.

I don’t suppose you have any ideas on how to prevent this from happening, do you?

I did some searching and it looks like the fix is to change the autonumber column to a different type of field and then back again to autonumber and it will fix it… would be great if there were some way to signal to the user that they needed to do this fix.

[Jeremy_Oglesby]

Hmmm - well, it takes a pretty intentional effort to delete a record, so teaching people to avoid that would be step #1.

But also, Airtable provides a snapshot system that you can roll back with. Make snapshots early and often! There is also a global cmd + z undo that can bring back an accidentallly deleted record.

But ya, in the case of an undetected accidental deletion, you are in a non-ideal situation.

[Cory_Walker]

@Jeremy_Oglesby
I used your suggested solution and it worked great if there was a number in the old case number field. However, if there’s no value there, it’s not returning my new case number field value in the formula field. Any idea what i’m doing wrong?

[Cory_Walker]

Strike that – i think i worked it out. Thank you for your insights on this post!