Yep - I think this is a shortcoming of Airtable formula fields. In fact, it’s why there shouldn’t even be a field of type “formula”.
Ideally, we need cells that may (or may not) be changed by formulas. In the current design, formulas change columns - period. And despite many clever tricks that I’m sure we’ll see in this suggestion, I’m pretty sure any attempt to mimic a persistent value given one or more condition, requires a recursive formula which is also not allowed in Airtable.
Ideally, in my not so humble opinion…
- Fields of ANY type should be able to listen to events that occur in other fields.
- Fields of ANY type should be able to be instrumented with formulas; extra points if the definition of “formulas” were expanded to “scripts”.
Airtable talks a good game concerning process management, but it fails to meet an essential aspect of process modeling - the ability to recognize state change events and perform conditional steps based on change events.
One might debate that a formula field driven by a change event in a status field for
done meets this process requirement. But it doesn’t because the recomputation of the formula (upon such change) is unconditional - it cannot be blocked.
Remedy or Workaround?
The way I create solutions that require persistent datestamps based on state transitions is to abandon formulas altogether. Instead, I use the API to monitor state and state changes and simply update fields (like dates, strings, and numbers) to reflect an accurate picture when designated states are indicated.
This is a costly approach - kind’a like turning off a light switch with a wrecking ball. But it’s the only rational approach (in my view).
If someone has a better approach, I’m all ears.