I have a task list and want to capture at which stage something gets canceled (not moving forward).
So if a record says “Not Started”, then it goes into “Canceled”. Then I want in another field for it to say “Not Started” so I know it was not ever started.
If I had a record that said “In Progress” then it goes into “Canceled”, I want the other field to record “In Progress” to know that this task was being worked but now is canceled.
Furthermore, if there is a way to capture how long a task was in stage “Not Started”, “In Progress” Ect so I can go back and measure how long things are taking in each stage. The durations should not go away. I want to see on one record, the time in each stage which aggregates to a total time since inception.
I have zapier and can build what I need there, just need to know how.
When the ‘last modified’ value for records and fields gets out of beta, you’ll have part of what you need — but what I think you want to do is to use linked records to track task statuses rather than single selects. Then, making liberal use of hidden fields and the CREATED_TIME() value, you can track status durations and ‘meta’ statuses.
Take a look at the [out] table in my Wardrobe Manager base from Airtable Universe, as it does a lot of what you’re looking to do. (Part of its complexity stems from having to track possible multiple ‘outs’ for a garment and the possibility of a garment going in and out of a given status multiple times — I cut my teeth on trouble-ticketing applications for technical support, so I’m a belt-and-suspenders kind of guy — so if you don’t have those requirements, you can certain trim back some of the excess.) Select <Developer view (all fields)> to unhide all fields. (I think I included the full text for all formulas in the field description, so you can get a feel for how it works by hovering over the circled ‘i’ in the field header without having to duplicate the base.) It’s been a while since I created that base, so I won’t swear it does things the way I would do them today, but it seemingly addresses your needs.
I created views for each stage. I then created a zap that equates the stage of the view to the field that will show the last stage before being canceled. So each time a task goes into a view, the new field is update.
But, I did not create this for Canceled; so any time something moves into canceled, the last stage will still be what it was before it was canceled.
Again, I’m not sure if it’s important for your use case, but — unless things have changed recently — that approach will only trigger on a record’s first appearance in a view. For instance, if you have an ‘On hold’ status, and a task goes from ‘Scheduled’ to ‘On hold’ and back to ‘Scheduled’, I think Zapier will still see it as being ‘On hold’…