Reset Auto Number Each New Date
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.
