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 started and 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.
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 started and 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.
I’ve rarely felt so understood! Thank you @Bill.French! And thank you for the proposed solution. I was looking at their API, but I never thought to use it in this way.
I’ve rarely felt so understood! Thank you @Bill.French! And thank you for the proposed solution. I was looking at their API, but I never thought to use it in this way.
My wife says this quite rarely. :winking_face: Let that one sink in a bit.
I’m glad it resonates. I was reluctant to suggest the API because it is a fair effort and it comes with a lot of infrastructure requirements. I tend to use Google Apps Script because (a) it’s free, server-side javascript, (b) it includes a fully-baked chron processor, and ( c ) it’s serverless and secure.
Not many people realize this, but if you have Gmail, you also have an amazing integration platform - all for free.
My wife says this quite rarely. :winking_face: Let that one sink in a bit.
I’m glad it resonates. I was reluctant to suggest the API because it is a fair effort and it comes with a lot of infrastructure requirements. I tend to use Google Apps Script because (a) it’s free, server-side javascript, (b) it includes a fully-baked chron processor, and ( c ) it’s serverless and secure.
Not many people realize this, but if you have Gmail, you also have an amazing integration platform - all for free.
What??? I had no idea. I spent a week writing a Google Cloud Function and integrating it with a third party chron job runner. You made my day. Thank you!
