Counting and Assigning Sequential Numbers to Repeating Records

You’re going to have a devil of a time getting Airtable to assign these numbers automatically, as the product currently lacks support for persistence in calculated fields. (That is to say, any value resulting from a formula or aggregation formula is, by definition, dynamic; any change to the inputs results in an immediate change to the output.) In addition, any routine that determines the current highest count for a school and attempts to assign an incrementally higher value is going to trigger Airtable’s internal protections against circular references. About the best I can suggest would be to create a different numbering system, one that makes use of RECORD_ID() or CREATED_TIME(), the only two accessible, truly persistent values within Airtable, or of autonumber, which is persistent under normal conditions. If you are stuck with the phase numbering system you outlined, about all you can do would be to assign each number manually, while using Airtable to keep track of the current highest count. (That is, a new project arrives for Cooper. You look and see Cooper’s current project count is 4, so you enter 5 for the new project’s phase. Cooper’s project count is updated accordingly.)

To do this, you need a table of schools, with each record in the table corresponding to a specific school; that may be your [Master] table, or it may not; if not, you’ll need to create a [Schools] table. Each school record should be linked to every [Project] record representing a project involving that school. Now, define a rollup field that follows the link to [Project] and rolls up {Project::Phase} (that is, the {Phase} field in the [Project] table) using a MAX() aggregation function, as well as a formula field that relates schools and max phases as such: {Name}&': '&{Phase}. Finally, somewhere other than [Schools], create a rollup field that follows the link to [Schools] and rolls up {Name+Phase} using the aggregation function ARRAYJOIN(values,'\n'). (This might be done in a table created for this purpose containing a single record that links to all records in the [Schools] table.) You could then keep this list of values always displayed by incorporating it in a Summary or Page Designer Block.

Admittedly, not a very satisfying solution, but one that is currently within reach.

That’s what I was afraid of. The pitfalls in functionality between Google sheets/Excel and Airtable are so detrimental that it renders the product almost useless, and certainly not worth the monthly fee when Google sheets is free and far more dynamic.

Thank you so much for taking the time to respond!

Ah, the curse of the spreadsheet metaphor.

It’s funny, but my first PC came bundled with a suite of programs from Perfect Software, including something called ‘Perfect Calc,’ an early VisiCalc clone. Back then,¹ though, if you weren’t an accountant or bookkeeper, you probably weren’t familiar with hard-copy spreadsheets, so the promise of an ‘electronic spreadsheet’ held little meaning. For longer than I care to admit, I would invoices distributors by entering unit prices and units shipped in a PC-Write template and then pop open a Sidekick calculator to arrive at a total. Ultimately, I became comfortable enough with the spreadsheet metaphor to become adept at Excel, but I can remember, in late 1987, when I first became a homeowner, I remember writing a short Pascal program to calculate the proportions of principal and interest over 144 mortgage payments. (I think what finally won me over was discovering I could get better-looking charts and graphs from Quattro than from the standalone Fast Graphs application I’d been using.)

The irony is, ten years later, when I gave my parents their first computer, that original spreadsheet metaphor was nearly worthless: Computer spreadsheets did so much more than merely spreadsheeting, my father, who for the previous 50 years had kept the accounts for his auto dealership, household, and church in hard-bound 11″ x 17″ ledgers, was as much at sea regarding Excel as I had been with Perfect Calc. Up to the day he died, any financial information I received from him would have been entered in a word processing program (MS Works, this time), with the total — calculated on an old pull-handle adding machine — later penciled in.

Today, the electronic spreadsheet has become so ubiquitous I suspect most users don’t know it ever came in a non-electronic version. Accordingly, in today’s ‘spreadsheet metaphor,’ the PC spreadsheet has become the figure — the point of reference — for something else (Airtable, dashdash, Mesh, etc.) as the ground.

The problem is we lose sight of it being a metaphor. The fog doesn’t literally come on little cat feet, and Airtable isn’t a spreadsheet: It is an RDBMS that appropriates some of a spreadsheet’s terminology and visual cues, but it is not a plug-compatible replacement for Excel or Google Sheets.

There is a large body of problems that could be solved with either Airtable or a spreadsheet. In addition, there are many a spreadsheet can’t easily solve, just as there are many that can’t easily be implemented within Airtable. If yours is of the first problem type, and you don’t anticipate future requirements mandating use of a database system, you should use whichever is most efficient for you to program and maintain.²

1. circa 1983.
2. When I first started using Airtable, I often found myself reaching for Excel or LibreOffice Calc for some tasks — sometimes even to perform calculations I later embedded in an Airtable formula. Today, not so much: Any speed advantage gained from a spreadsheet’s easy 2D navigation is offset by my having to look up the syntax for virtually every function I include. I have a base called ‘Scratch’ I use for testing formula snippets before posting — and for the sort of quick number crunching for which I used to turn to Excel.

I figured out a way to make this work, and posted details in the Show and Tell category, including a link to a sample base so you can dive in and see the formulas required to make it work.

hi can you help me with this i need to number each group from 1-5 in so on