Update field based on single select update

[warrior604]

Hello! I am hoping for some help on a problem I have. :slightly_smiling_face:

I have a Projects table and the following fields:

Status: a single select field that is either Confirmed, Completed, Prospective, or Declined/Did not pursue

3 Date fields for recording when the single select field changes to Confirmed, Completed, or Declined/Did not Pursue. Each with historical data I don’t want to lose.

I would like the following to happen but don’t know if I can do it in a formula or automation:

When the status field is updated to be “Confirmed” → Record the date in Date Confirmed
When the status field is updated to be “Completed” → Record the date in Date of Completion
When the status field is updated to be “Declined/Did not pursue” → Record the date in Date Declined

Any assistance is greatly appreciated! I am still relatively new to AT. :slightly_smiling_face:

Thanks

[ScottWorld · Accepted solution]

Hmm… there are probably a few ways of doing this, but I’m thinking that perhaps the easiest way to do this would be to create 3 different automations.

Each automation would be triggered based on the status field changing to a certain status.

Then, you would update the appropriate date field with the current date & time. To do this, you would need to create a formula field in your base with the formula NOW(), and then just insert this formula field’s value into the date field you’re trying to update.

[Joe_Hewes]

Depends how critical the data is to access, but don’t forget you can see amendment data in the history of each record too. If you just need to see when the data changed for reference this might be enough.
Don’t forget it only gets stored for a limited time though, depending on your plan: https://support.airtable.com/hc/en-us/articles/203941415-Record-level-revision-history-overview