Be able to show the previous value for a date field in a formula field....

[Gareth_Williams]

Hi All! I am hoping this is an easy problem to solve 🙂

I thought this would be really simple, but I'm struggling.

I have a project "Start Date" field. Every time one of my users updates that "Start Date" I would like AT to automatically retain the last start date before that change in a "Last Start Date" field.

So if "Start Date" was 01/05/2024 and someone changed it to 12/05/2024, then "Start Date" would read 12/05/2024 and "Last Start Date" would read 01/05/2024. Each field would then update every time the "Start Date" field is consequently updated, which can happen numerous times.

Any help / advice would be very much appreciated. I am hoping that this can be done with AT as opposed to using any external connector.......

[BillH]

You should be able to use an Airtable Automation to do this.  It would trigger on a change to the "Start Date" and then update the value in the "Last Start Date" field.  

https://support.airtable.com/docs/getting-started-with-airtable-automations

[Gareth_Williams]

Thanks BillH - I have tried that previously and although it does add data into the "Last Start Date" field as an automation, it actually adds in the new "Start Date" as the automation only takes place once the change has been made.........

[BillH]

Yes, obvious, I will think about this some more while I sit in the corner with a funny hat on.

[BillH]

Not the most elegant, but you could create a "Temporary Start Date" field, and have the automation copy that first to the "Last Start Date", then update the "Temporary Start Date" field with the new "Start Date" (and probably hide the new field so it doesn't get changed outside the process.

Alternatively, if you have a more controlled process you could create a toggle field which when set to yes would trigger the automation (rather than trigger on the date change), and then the user could change the "New Start Date" field without overwriting anything.  This probably makes more sense if you're using an airtable interface and can use a button for the toggle.  

Hopefully someone can chime in with a more elegant solution.