Increment a number field per client, start over each month


#1

Use Case:
I have a base that enables teachers to log sessions with students. In the Sessions table, there’s a field for Session Date (date), Session Number (integer), and Student (one-to-one link to a record in the Students table).

The goal is to automate the Session Number field with the following business logic:

  1. Each month, the first Session with a given Student should be assigned Session Number=1. The second session that month gets Session Number=2, etc.

  2. When the month rolls over, the Session Numbers start over again. So, first session in July for Student A would get Session Number=1, etc.

Have gone down a rabbit hole with conditional formulas and am quite stumped. Any help much appreciated! :slight_smile:


#2

While there are ways to allow one record to create or modify a value based on a value in another record, what you want to do is going to run into conflict with Airtable’s check on circular references.

For instance, the techniques in the referenced post ‘pass’ values from record to record through a rollup of key:value pairs — e.g., 0001:325,0002:250,0003:498 . Unfortunately, you can’t access that rolled-up string, search for the key '0002:', extract the value that follows it, and create a value for record 0003, because Airtable has no way of knowing you’re extracting data from a different key than the current one: It sees it as a potential circular reference, and blocks the formula.¹

If you take a look at the base referenced in the cited post, you’ll see the [DayByDay], [WorkDay], and [ArbitraryDay] tables all include a manual workaround: Namely, the previous value is returned as a rollup called {PrevBalance}, the value of which must be copy-and-pasted into an adjacent field called {Previous Balance} for the calculation to complete. This gets around the circular reference issue, but adds a degree of manual manipulation some may not enjoy. In your case, you’d have to perform that sleight of hand for every new student session added — with a little extra complexity tossed in to handle the month-to-month reset.

Now, even though this is a manual process, it still may be preferable to asking the user to increment and reset session numbers manually: The user either remembers to do the copy-and-paste (or click-and-drag of the fill handle) or he doesn’t, but there are many ways he can screw up having to maintain session numbers manually. In addition, you could also integrate the base with such a middleware solution as Zapier or Integromat and automate copying the value.²

If you decide to give either of these approaches a try and run into trouble, don’t hesitate to contact me here for assistance.


  1. The worry, of course, is that someone might extract the value for key 0003, increment it, and use the result as the value for … key 0003, setting off an infinite race condition, looping until Airtable or the server hosting your particular base crashes — so one really can’t complain about protection against circular references.
  2. For that matter, there may be ways to use Zapier or Integromat to generate session numbers without having to build all that multi-record calculation infrastructure, as well.

Reset Auto Number Each New Date