Insert date on specific actions


#1

Is there a way to write a formula that inserts the date when a change is made to a record?
I would like to automatically show the date when I change a single select item from one selection to another. (I am changing from “in progress” to “closed” and I’d like to see the date I closed the record without having to remember to add that in a separate field).


#2

Hi

I don’t think there is any way to do this within Airtable - but you could use Zapier to do this for you - a simple two step Zap triggered by a new record in view - you would make a view filtered on status = closed for this.

The second Zap step would update a Closed Date field to Today.

Personally I think we have to consider Zapier the processing engine for Airtable- you can achieve a lot with the two combined. Perhaps Airtable should consider a partnership agreement with Zapier.


#3

As @Julian_Kirkness says, there’s currently no way to do this in Airtable. Even better, I think the Zap he described can be run from a free Zapier account, should you decide to go that route.

Another possibility would be to change the thing you’re changing. For instance, depending on how complex your potential statuses are, you might instead define, say, {Date Opened} and {Date Closed} fields. Your {Status} field then becomes a formula field with a formula something like

IF(NOT({Date Closed}=BLANK()),'Closed',
IF(NOT({Date Opened}=BLANK()),'In Progress','')

Now you change the value of the appropriate date field, and the system updates {Status} accordingly.

If you’re tracking the status of an entity that may undergo repeated cycles through ‘In progress’/'Closed/ statuses — for instance, a telco circuit that may fail and be repaired multiple times during its lifetime — you’re probably better-off by linking from the item record to a [Status] table and create a new status record for each change. This gives you a built-in timestamp based on the DATETIME_CREATED() of the current [Status] record as well as a view of the item’s history. I wrote about this earlier today in this reply to a similar post; it’s a technique I often use.