Timestamp Snapshot

[Josh_Inselberg]

I have one formula field that watches another field for a particular value like ‘Started task’ and will mark a timestamp when it sees it. I have another formula field watching for ‘Done task’. The problem is that when I change status from ‘Started task’ to ‘Done task’ in the watched field, my ‘Started task’ date disappears. I wish there was a way in a formula to do something like ‘compute only the first time you see this’, so that it sticks for all time.

Sure, I could manually enter the date, but that’s not why I love Air Table :slightly_smiling_face:

Thanks for listening.

-Josh

[Bill_French]

Josh_Inselberg:

I wish there was a way in a formula to do something like ‘compute only the first time you see this’, so that it sticks for all time.

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.

[Josh_Inselberg]

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.

[Bill_French]

Josh_Inselberg:

I’ve rarely felt so understood!

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.

[Josh_Inselberg]

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!