Sequential Number, Resetting Daily

[W_Vann_Hall]

	A couple of hours ago, while I was working on this base and post, I decided to take a break and go for a walk on the beach. I got as far as the shipwreck of the William H Sumner, exposed by Hurricane Florence, before heading back. Once I got here, I sat down to finish this post and discovered @Justin_Barrett had beaten me to it. Justin’s solution is preferable to mine, as it is more general-purpose, but I’ve decided to post anyway, as drop-in code for anyone with a specific need to restart numbering each day.

For those of you who can stomach having to link every record of a table to a single record in a second table, this post and linked base demonstrate how to create a numbering system that resets every day. In the demo, I create a composite number displaying ‘Day#.Sequence#’ — ‘1.1’, ‘1.2’, ‘1.3’, ‘2.1’, and so on — but the formula can easily be modified for other uses.

First, the caveats — because there are always caveats:

1. The sequence is based on a date derived from CREATED_TIME() and an autonumber field. For the demo, I’ve created a special {Demo Date} field that allows one to test the routine without having to wait for midnight to roll around.
2. Because of the conditions given in Item 1, the routines are none too friendly when it comes to records being added out-of-order. (That said, there are ways¹ to do so.)
3. The routines do not keep track of deleted records. That is, if you have records numbered 1.1, 1.2, and 1.3, and you delete record 1.1, records 1.2 and 1.3 are automatically renumbered 1.1 and 1.2. I’ve not found a way around this.
4. As mentioned earlier, for this to work correctly, every record in your main table must be linked to a single record in a second table — but that’s not quite true. More accurately, I should say every record in your main table to be included in a given numbering sequence must be linked to a single record in a second table. The [Transaction] table gives an example of a dual sequential numbering scheme, where each invoice or payment received each day is sequentially numbered by transaction type (invoice or payment).
5. The calculated sequence number cannot be incorporated in a formula-derived primary field value because of Airtable’s block on circular references.

A link to the demonstration base can be found here.

Airtable

Daily Resetting Sequence demo - Airtable

Explore the "Daily Resetting Sequence demo" base on Airtable.

The base is reasonably self-explanatory, with a couple of exceptions:

1. Each record is stamped with a {Day:Number} value consisting of an index value for the day (see item 2, below) concatenated with the record’s autonumber. These {Day:Number} values are rolled up into a very long string in the [Count] table, which is then passed back to the main table via another rollup field. The sequence count for any given day is determined by performing two FIND()s against the string, with the location of the first entry for that day subtracted from the location of the record’s {Day:Number} entry, with the remainder divided by 12, the length of {Day:Number}. Since I start each day’s sequence with ‘.1’, I add 1 to the result; to start with ‘.0’, eliminate that final ‘+1’.
2. For the ‘day’ portion of the composite numbering, I’m working from an origin of today, 29 March 2019 — that is, today is considered day 1. I base this on the Unix Epoch day (the number of days since 1 January 1970) minus 17983, the Epoch day for 28 March 2019. Rather than hard-code it into the formula, as any sensible person would do, I’ve made it a variable called {DateOffset} in the [Count] table, which I then access through a rollup field. Feel free to revert to a simple formula field if desired.
3. As mentioned earlier, I’ve included a {Demo Date} field to allow the base to be tested in a time frame of less than 24 hours. For a production system, unhide {Date} and modify the aggregation formula in {Day} to access {Date} rather than {Demo Date}. (Adjust {Count::Date Offset} as needed.)
4. Obviously, this code doesn’t really require 5 separate working fields to run; instead, I think they can be compressed into 2: an autonumber field and a somewhat ugly formula field to handle the rest. I’ve chosen to keep the fields separate to make understanding and modifying the algorithm easier. Again, feel free to combine fields as desired.

---

Notes

1. If you need to support a process flow where one may occasionally overlook an item for a day or two, your best best is probably to create a {Date Override} date field, along with a {Cooked Date} formula field containing the following formula:

   IF(
    {Date Override},
    {Date Override},
    CREATED_TIME()
    )
   

   Use {Cooked Date} to generate {Day:Number}. If you need to add an out-of-order record but have it assigned the appropriate day:number sequence

   1. Add the record.
   2. Use {Date Override} to set the desired date (or datetime, if desired).
   3. Sort records by {Cooked Date} (primary) and {Nbr} (secondary).
   4. Reconfigure {Link2Count} to be a single-line text field.
   5. Re-reconfigure {Link2Count} to be a linked-record field pointing to [Count].
   6. I think Item 5 will result in [Count] containing a text field containing a list of all linked records at the time [Link2Count] was reconfigured. If so, delete this extraneous field

   Clearly, the percentage move is not forgetting to enter a record at the appropriate time…