Skip to main content

Track the previous value of a field / Time of stage

  • April 15, 2019
  • 5 replies
  • 76 views
R

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!

5 replies

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • April 15, 2019

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.

R
  • Randy_Shoemaker
  • Author
  • Participating Frequently
  • April 15, 2019

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.


Thanks,

But not too sure how your solution solves my problem.

R
  • Randy_Shoemaker
  • Author
  • Participating Frequently
  • April 15, 2019

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.

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • April 15, 2019

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.


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

R
  • Randy_Shoemaker
  • Author
  • Participating Frequently
  • April 15, 2019

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


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)

R
Terms & ConditionsAccessibility statement