Find the biggest number used in a field, add one and use that number in the new record created via form

Airtable Questions Fields and Formulas
1

Using the Auto-number type field can be great, but it is also very limiting when you need to start with a certain number in particular. And if you create a record and then delete it, the next record you create will not have that deleted record’s number, but the next one in sequence.

For this Project Tracker, the client started using Airtable for job # 2000 (and they have a hundred more after that), which means they created 1000 EMPTY records just so that they could get to the real data at # 2000+. It’s also no good to go out of order, so if anyone accidentally creates and then deletes a job, their list of jobs is not sequential. Yes, we can limit record creation with permissions, but I want to know if there is another way I can automatically create the next job record and set its number by finding the highest number used and adding 1 to it. And if a record gets deleted, I want to be able to start where we left off! For example, I am working in a test version of the base to play around, and now that I’ve deleted a few test records, the auto-number field is not allowing me to start the next job where I want it to.

There has to be an automation or a scripting way to “find” the record with the highest number, add 1 to it, and then use that to populate the Job Number field.

So here’s where I started: Their base had 2000 records, half of which are useless. The Job Numbers were automated, and so I added a new Number field and copied over the auto-numbered Job Numbers so that it could be used in an automation or script and I could get rid of the auto-number field later without losing the Job Number data.

I also tried adding a “Next Number” formula field that took the record’s Job Number, and added 1 to it, in the hopes that I could somehow reference that in an automation when the next record is created via a form.

What I DON’T know how to do is to find that highest number and stick it into the Job Number field of the newly created record.

I’m really not good at scripting in my attempts thus far, but I figure there has to be a script for this?? A button? an Automation? I’m feeling pretty dumb and pressured to figure this out asap, so any help would be SO appreciated.

Thanks!

2

Welcome to the community, @Shrizz !

I don’t know of an easy way to go back and fill in gaps in the sequence.

But you don’t need to create 1000 blank records to start at a certain number.

You can just create an autonumber field, which will number your records starting from 1.

Then, you can create a formula field that equals your autonumber field + 1000.

Then, that formula field will be the field that you refer to. And it will always increment by 1.

3

There are several different ways of generating a sequential number. Most of them involve automations that run when a record is created.

Here is one method:

Have three fields:

  • an editable number field for holding the {Sequence number}
  • an {isLatest} checkbox field that indicates which field is the latest field
  • a {Next Sequence Number} formula field that adds 1 to the the editable {Sequence number}, if the {isLatest} checkbox is selected

When a new record is created

  1. Do a “Find Records” action to find the record that has the checkbox selected.
  2. Update the triggering record with the formula result of the next sequence number in the found record and also set the {isLatest} checkbox.
  3. Update the found record to clear the {isLatest} checkbox, so only one record will have the {isLatest} checkbox selected.

You also need to number any existing records and select the {isLatest} checkbox for the current largest number.

However this system has some issues.

  • It will not fill in missing numbers for deleted records. (Although if deleting records is rare, you can manually change numbers.)
  • If multiple records are created before automations have time to run, you can end up with records with the same number and multiple {isLatest} records, which can break the system.

Another possibility is to have an automation that runs on record creation that runs a script that identifies what the next number should be. The script could be written to look for missing numbers in the middle, or find the next number in the sequence. However, this scripting method will also have problems if multiple records are created before the automations have time to run, resulting in multiple records with the same number.

Another possibility is to leave records un-numbered until a scheduled automation script numbers them, say once a day, or every hour. The script could handle filling in holes in the sequence and you wouldn’t have to worry about records accidentally ending up with the same number, but you would have to deal with un-numbered records until the next script run.

I’ve seen other systems that involve linking records to a control record that determines the next sequence number, and other systems that find record without using an {isLatest} checkbox. However, those systems have even more drawbacks than the systems that I have just described.

However, I also recommend that you think about why it is important to have a numbering system that you described. It can ‘look pretty’ and ‘feel nice’, but there rarely is enough business need to have sequential numbers and filling in gaps to make it worth the trouble to setup and maintain a numbering system. Needing all numbers to be unique is common and valid. However, using an autonumber field does that very well.

4

Wow, thank you, Kuavonne, for your thorough response!! I will work through these options and see if any fit my situation. And I agree, I don’t think the numbering system should be so important, but it’s not my company :wink:

5

@Kuovonne Your first solution is the WINNER. I’m not too worried about the issue regarding many records added before the automation has time to work, because they really won’t be adding lots of records at a time. Using a form means the process gets slowed down as well. And being able to manually override is perfect. THANK YOU!

1 Like