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.