Track the previous value of a field / Time of stage

#1

Hi,

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.

Thanks!

#2

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.

#3

Thanks,

But not too sure how your solution solves my problem.

#4

Here’s where I’m at:

SOLVED:
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.

#5

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’…

#6

That’s correct but I’ve set it up different:

I have a view for each stage.

I have a zap for each view

So no matter what, when it enters a new stage (except closed because I have it set up that way) it will update the field.

I’ve tested this and it works.

**Full description: **

2 fields:
Stage
Stage Before Closed

5 stages
1. Not Started
2. In Progress
3. On Hold
4. Complete
5. Canceled

Stages 1-3 have a view set up with a filter for that stage
Stages 1-3 have individual zaps set up that will input the stage into the stage before closed when a record enters a view.

When it goes into 4-5, the stage before closed won’t change; showing the last stage before being closed.

Zap = 1. when record is new to a stage 2. Update record

This is working exactly how I intended. Now I need a time tracker for each stage (totally separate build)