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.
- 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.
- 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.
