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!
