Counting Linked Records Formulaically

[CAHerrmann]

I have a table of projects. A formula sets each project to “Active” or “Complete”, depending on two conditions: if a completion date has been entered; and if a call back record has been created.

The call back record is a linked record, created each time an issue arises with the project, post-completion.

I have a formula that sets the callback record to “No Callbacks”, “Callbacks Complete”, or “Not Complete”.

I need the project status to be set to active when that formula returns “Not Complete”. The formula for my callback status is:

IF({Call Backs}=BLANK(),
“3. No Callbacks”,
IF(COUNTA({Call Backs})={Call Back Completion Date Count},
“2. Call Backs Complete”,
“1. Not Complete”))

Unfortunately, my limited understanding of the COUNTA function led me to use it in a formula in a manner for which it is apparently not suited. When I have more than one callback on a given project, this formula fails.

Is there a way, formulaically, to count linked records without the nastiness of ARRAYJOIN and LEN(SUBSTITUTE())…and all the rest?

I wouldn’t put up such a stink and just go with that method, but some of my projects occasionally have commas in the name, which breaks the formula. I can eliminate those commas when I input information, but I am not the only user and, unless I am mistaken, there is no way to restrict entry of specific characters in Airtable.

My guess is that I have to create yet another field in Airtable to accomplish this, growing my field count to 53 or so.

[Jeremy_Oglesby]

Your easiest route will be to create another field, yes… but it’s a field you can hide, as you only need it to evaluate your formula.

There is a “Count” field type that counts the number of linked records in the linked record field you point it at:

So create that field, and then replace your COUNTA({Call Backs}) in that formula with a reference to that Count field.

And I wouldn’t sweat having 54 fields in your table – as you can see from my screenshot, I’m currently working on a table that has 71 fields in use, and I’m estimating I’ll need about 15 more to complete what I need this table to accomplish. And that table is a record type that links into another table that has 96 fields of its own. And those link to another table… and so on… this base I’m working in currently has 28 tables, all of which eventually converge into one main, working table.

[CAHerrmann]

That is what I’ve done already (I had already created a rollup field for counting the number of completion dates there were in the callback records, anyways).

I suppose my frustration with the process of creating new fields stems from several unresolved issues with the way Airtable structures data and restricts/does not restrict actions on that structure.

I’ll save those grievances for another post (and discussions directly with the Airtable team).