A Better Auto Number

[Aaron_Owen]

So I have a couple of tables that I use to link deliverables to jobs and tasks to deliverables. Each line on these linked tables don’t really have a name that makes sense, so I use a number. I’ve used auto number reluctantly because it’s sort of a “dumb” auto number. By “dumb” I mean that it starts at 1 and increments from there. If I delete something out of the row, it picks up where the last increment was. If I delete something at the end of my list, now my nicely numbered rows are missing a number.

I’d like to be able to start the auto number at an arbitrary number, and turn on an option to make sure that it is “smart” about the way it increments so that it looks at the last number currently in the table and adds 1 (n+1) rather than a simple (i++ ).

[Kasra_Kyanzade1]

Thanks for the feedback! We’ll think about it.

Auto Number, as it exists today, was designed to let you assign unique identifiers to records, without ever reusing the same identifier.

There are two workarounds that can get you close to what you want:

1. To start at an arbitrary number, create a formula field that adds the starting number to the auto number field. e.g. to start at 100, the formula would be: 99 + {Name of Auto Number Field}. You can hide the auto number field and just look at the formula field.

2. To get rid of gaps, convert the auto number field to a regular number field, then back to auto number. But be careful with this! Since it renumbers the records, you’ll lose the old numbers.

[Polen]

This thread touches on a relatively irrelevant but persistent issue that I"m sure plagues other users as well. When I or a colleague is at the very end of a table entering or editing data it is very difficult --or impossible-- to avoid hitting Enter automatically when done with entering data. As you know, this creates a blank row below and the column in autnonumber displays the next number.

My tables often populate with records created via Zapier. This means that after a record is edited, info is added, or anything like that, the new row that’s created by this automatic Enter hitting will be blank and will remain blank. Because I do use the autonumber to identify records, I cann’t reset it as you suggest in #2. I have to live with a blank record or learn to not worry that sometimes there are gaps in the numbering. There is, however, the big risk of deleting records and not having the help of autonumber to help notice.

[Katherine_Duh]

It sounds like your problem is with the behavior of ghost rows specifically (“ghost rows” being the rows created with a new autonumber but without any data). We might just change the behavior of ghost rows rather than overhaul the entire autonumber behavior. Do you have any specific requests for how you’d like the autonumber to work with regard to ghost rows?

As a side note: most developer-oriented databases (i.e. MySQL, PostGres, etc.) have behavior similar to ours, where the autonumber doesn’t fill in the gaps for deleted rows. More consumer-oriented database products (e.g. Filemaker, Zoho Creator) generally work this way as well. This allows each row, even deleted ones, to always have a unique autonumber.

[Polen]

Katherine, thanks for your reply. I would expect these ghost rows to “give back” their number to the counter. I wouldn’t consider a row that had data that then at some point was deleted a ghost row, but I don’t know if it is a distinction the system could make. I wouldn’t expect/want the autonumbering to fill the blanks created by user-deleted rows as I can see issues arising from there – mainly renumbering items where the autonumber is used as an ID or key. However, maybe there is a way to avoid adding an autonumber to a row until at least a character or even a space is typed or data is pasted? That I would endorse.

[Andrew_Enright]

I might suggest two auto-numbers. An absolute that behaves as-is, and and an “intuitive” (or whatever; maybe “human”) that discounts ghost rows, and potentially deleted rows within a given time-frame. AKA, rows deleted well after creation would not “give back” their number, but rows deleted within a given period after creation would.

[Melissa_Frank]

I would really like to be able to get a random number - like a GUID or UUID, but with a number of digits users could specify for their purposes. I can see why incremental auto-numbers are helpful in many cases; however, when I only add a handful of new records to a particular table once a month, and it’s helpful to be able to refer to the record by this number, a bunch of non-sequential numbers is a lot easier to work with (and not get mixed up). Maybe I’m alone on this one, but at least with the auto-numbers and the entered numbers fields it would be great to specify digits and not just decimal places. 1-9 do not sort nicely with two-digit numbers in most programs I work with, and Airtable removes the 0 if you try to enter 01 etc.

[adam]

Considering this topic was started > 1 year ago, I am curious if there have been any Airtable system changes or any other clever suggestions for handling the arbitrary starting number and/or mitigating the ghost rows numbering issue.

At the moment, I am trying to devise a simple ID column that starts at 2 and every following row is always n + 1. Starting at 2 was actually a mistake in the numbering system of my use case (which I am now deeply regretting btw, haha), that cannot be changed any longer.

I was even hoping for a way to calculate the MAX() number of a given column and use a function to figure out the next number in the series, but couldn’t figure it out. I guess similar to a database trigger where the trigger decides what the next value in the autonumber will be, in this case, MAX() of the column + 1. Thanks!

[Noel_Howell]

So I had to do a really awful workaround for this.

I created a second table with auto-numbering and then associated each record in the first table to a record in the new and then made a lookup column to bring in the auto-number. While that seems like a lot of extra work, I set up a Zapier ZAP to make a new record on the new table for every new record in the first table and then associate it.

Yeah thats a lot of steps to just fix the fact that I can’t autonumber off of a start # or for only certain record types…

[Brian_Schmitt]

I’m curious what your zapier setup looks like - i thought about going this route, but haven’t dug in enough on zapier to figure it out.

[Damon_Talbot]

Replying here though the request is not quite the same.

I would like an auto-number based on a single select field. In other words each selection is its own list. My example would be from our Destiny Search Project project. We have a volunteers table that also allows us to single select if the are searchers, DSP Admins, or Volunteer staff. We could us a seperate table but it would be extra steps when running an operation.