Apr 01, 2019 09:47 AM
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:
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.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.[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).A link to the demonstration base can be found here.
The base is reasonably self-explanatory, with a couple of exceptions:
{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
’.{DateOffset}
in the [Count]
table, which I then access through a rollup field. Feel free to revert to a simple formula field if desired.{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.)Notes
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
{Date Override}
to set the desired date (or datetime, if desired).{Cooked Date}
(primary) and {Nbr}
(secondary).{Link2Count}
to be a single-line text field.{Link2Count}
to be a linked-record field pointing to [Count]
.[Count]
containing a text field containing a list of all linked records at the time [Link2Count]
was reconfigured. If so, delete this extraneous fieldClearly, the percentage move is not forgetting to enter a record at the appropriate time…