Reset Auto Number Each New Date

Unfortunately, I’ve not come up with a way to do so in the week and a half since I posted this…

Yes, this type of functionality would be rather useful. It is possible to do in excel with an =IF(A2<>A1,1,B1+1) and a few other ways. Sad panda.

OK, I did it — although I seriously considered not telling anyone, because it is undoubtedly the ugliest piece of code I’ve ever written. I’m hoping someone can figure out a cleaner, less convoluted way to do this. I made so many false starts, by the time I got it to work, it had a third again as many fields as it needed. I think I’ve trimmed it down to the bare necessities — but I sure hope not, because it still uses something like seven fields and an additional table just to generate an autonumber that starts over from 1 with each new day. And, seemingly like everything I write these days, it requires one of those all-to-one linkages between every record in one table and the single, summarizing record of a second.

If I’ve not yet scared you away, here’s a base that demonstrates my resetting autonumber―

―― you know, it seems almost disrespectful of 65 years of computer science to call it an ‘algorithm.’ ‘Scheme,’ perhaps, or maybe ‘conceit.’ Possibly ‘folly?’

My expectation is that ordinarily all fields besides {ID} and {Link to Calc} would be hidden; I’ve unhidden them here in order to discuss how the base functions.

The autonumber routine is driven by a created time field, {Created}; accordingly, the resetting of the ID index field occurs with the first record created each day.

{Created} is then converted to a Unix timestamp using DATETIME_FORMAT() with a format specifier of 'X'. The [textual] timestamp is converted to a numeric value and then divided by 86400 to return the Epoch Day — in this case (22 June 2018), day 17704, which I store in an integer as {DayValue.}

If you convert {DayValue} into a string and append a colon (':') followed by the Unix timestamp MODULO 86400, you get {Cooked Day}. What {Cooked Day} represents is the Unix Epoch Day for today followed by a five-digit number indicating the number of seconds since midnight. This gives us a unique timestamp for every second of every day from midnight, January 1, 1970, through some point on January 19, 2038, yet the value of every {Cooked Day} created on a given day will start with the same five-digit number. This is a crucial factor behind the successful operation of this routine.

So, putting it all together: Each new record contains the time of its creation, encoded in its {Cooked Day} timestamp. Thanks to the ubiquitous linking of all timestamp-able records to the single record in the [Calc] table, all {Cooked Day} timestamps are rolled up into a concatenated string, with individual timestamps separated by the vertical bar character.

To determine the autonumber for a given record, the position of the record’s timestamp is located in the concatenated string of all timestamps. From this number is subtracted the location of the first timestamp in the string for a record created that same day. The remainder is then divided by the length of the timestamp, and the resulting number incremented by 1.

For example, the timestamp of the first record created on a given day is also the first timestamp for that day, so the difference would be zero, which divided by 12 is still zero, which added to one equals 1. The second record’s timestamp would be located at position 13, which minus the first record’s position of 1 is 12, which divided by 12 equals 1, which added to one equals 2, making the second record’s autonumber 2. Creating the desired ID is a simple matter of connecting a DATETIME_FORMAT() function to a zero-padded autonumber.

Of course, when you build a formula or formulas heavily dependent on such things as CREATED_TIME(), its relationship with TODAY(), and a counter that supposedly resets at midnight, the only true way to test it is to work with it for several days and observe its behavior. Not having such time to spare, I duplicated [Table 1] and reconfigured several fields so I could hand-enter ‘created times’ and the like. From that limited and somewhat suspect testing, it appears everything should work: The first post of a new day has an index of 001; subsequent posts appear in numeric order; and the user should not have to do anything special — besides being sure to link each new record to the [Calc] record — to keep the system running.

:clap: :clap:

This is amazing. I only noticed one thing … there isn’t a way to create a new record that increments with a back date … i.e. create a record today w/ a backdate to increment to that date. Will poke around … wow!

I had enough trouble getting it to work as is. :winking_face:

As it stands now, I’m not sure simply changing {Created} to a date field will do what you want, mainly because I assume a sequential procession of dates. (For instance, imagine Date 1 with 6 records, Date 2 with 4, and Date 3 with 5. If the next record is backdated to Date 1, I think the ID for that record would be YYMMDD-016.)

Yeah, it rolls up based on order of creation for the linked records. About the only way to postdate is to delete the record in [Calc], sort your main records by date, and recreate the links (for instance, by copy/pasting '.' into {Link to Calc}.

If you load and copy the base again, you’ll find a backdate variation I created. It allows the entry of an {OverrideDate} if you need to generate an override. Two caveats: First, because I’ve now tossed a calculated date into the mix, you either have to force a timezone on all date fields or set all date fields to use GMT (which I guess is just another way of forcing a timezone). Second: It still doesn’t work, as the index number of the ID is still calculated based on order of creation of linked records.

One possibility would be to make your date field a linked record field — that is, instead of selecting ‘today’ to enter today’s date, you’d select create or select a linked record whose primary field was a date field set to today’s date, and modify all the rollup fields to use the linked date field rather than {Link to Calc}. Your backdated records would work, although not necessarily according to time-of-day, but it does complicate the record creation process a bit.

Will have to try that. Because the sequential AutoNum (-001, -002 …) sequence is for that day vs. overall number of records. Thanks. This is tremendously helpful in solving the problem and helping me learn Airtable :slightly_smiling_face:

I’ll be the first to admit the ‘solution’ I offered is [more than] a bit of a kludge — in part because it is so dependent on internal Airtable mechanisms. (This is probably a good time to mention my original implementation, at least, could stop working at some point in the future should Airtable change how it performs rollups. While I suspect there is sufficient other user code that depends on the order in which linked records are created that future changes would either not change how this process works or would provide for some sort of versioning, that’s no guarantee.)

Ah, no need to be so humble :slightly_smiling_face:

Just covering my rear end…

Hi, that is very impressive. I’m trying to create a way of pausing autonumber for certain conditions. I’m using it to record order numbers. some have single lines and some have multiple lines which need the same order number. I have been struggling for a while, but I just can’t get a workaround. Any ideas would be really welcome.

All the mechanisms I can think of to all for a ‘pausing’ of autonumber run afoul of Airtable’s prohibitions against [potential] circular references.

Well, except one: Create a table called [OrderNumber]. Define only the primary field as an autonumber field. In your main table, define an {OrderNumber} field as a linked-record field pointing to the [OrderNumber] table with linking to multiple records disabled. You’ll have to set your link to [OrderNumber] manually for each new record, but at least you’ll be able to track single- and multi-line orders correctly…

Hi guys,
it is amazing what you build.

in my case, I want to create the ID of some devices I sell. So, I would like to concatenate the version of the device with an autonumber that restart automatically when version changed.

So I have a column to name the version, and have other column to autonumber, but I need to make a formula to restart the autonumber each time that I change the version.

Do you think it is possible?

I just wanted to chime in and say… “I was attempting”… but damn, impressive. I don’t care how messy. :slightly_smiling_face:

Hi @W_Vann_Hall impressive work, thanks!
Is this solution still true 2-3 years later after this first post?
I wonder because, a string is suppose to have limit of 100.000 characters. Which means the DataString field in the Calc table will quickly hit its maximum character and the calculation will no longer work.
Am I wrong?
thanks!