Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

A Better Auto Number

cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Aaron_Owen
7 - App Architect
7 - App Architect

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++ ).

16 Comments
Kasra_Kyanzade1
6 - Interface Innovator
6 - Interface Innovator

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
7 - App Architect
7 - App Architect

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
10 - Mercury
10 - Mercury

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
7 - App Architect
7 - App Architect

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
9 - Sun
9 - Sun

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
6 - Interface Innovator
6 - Interface Innovator

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
4 - Data Explorer
4 - Data Explorer

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
7 - App Architect
7 - App Architect

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.