Jun 01, 2022 06:05 PM
I’ve got a bit of a puzzle which needs cracking here.
I have a table in which each record is used to track jobs in my business.
I have a Single Select field which tracks the job status of each record.
Job status examples: Job Accepted > Worker > Reviewer > Manager > Complete
I need to record the amount of time that a record spends in each status.
I’m looking for something like this:
The catch: records will sometimes move backwards and forwards through the statuses. i.e., record status will go from Worker > Reviewer > Worker > Reviewer > Manager.
I can’t figure out how to make this happen (or if it is even possible).
Can anyone think of a way to do this? I’d love to hear an alternate approach if there is one!
Jun 01, 2022 08:28 PM
Hey twoodl, I think the only way we can do this is via a script I’m afraid. If you’re on a Pro plan we could put it in an automation, if not, we’d be have to make a button for you to click and have update the record (or all records if you’d like)
Let me know if this is something you’d like to explore and I can put something together for you
Jun 01, 2022 08:52 PM
I’m investigating this issue with the Pro plan in mind. I’m still on the Pro plan trial, so I’d love to hear how automations could accomplish this!
Jun 01, 2022 08:52 PM
Jun 01, 2022 10:23 PM
Hey twoodl, I’ve put something together for you here
The “Time Spent” numbers in the gif are so large as it’s outputting milliseconds; I did that to make it obvious to us that the numbers had changed. I’ve since updated the code to use minutes instead.
You can view the code and automation set up via clicking the automations button at the top right
The idea is that, every time we change the single select field, the automation runs and does the following:
Last changed tofield with the current date and time
Workernow, we’ll update the
Last changed to Workerfield with the current date and time
Last changed tofields to see which one has the latest value. We then assume that the one with the latest value is the single select option selected before this one
Last changed to Workerhas a date time of today, 1:00 pm
Last changed to Reviewerhas a date time of today, 1:01 pm
Last changed to Reviewerhas the latest timing, we assume that, before we changed it to
Manager, it was set to
Another option would be to have a table entirely dedicated to historical data which we use to calculate the time spent for from
Jun 01, 2022 10:49 PM
Thanks so much for putting that together - I can’t test it because “you cannot run scripts on the current billing plan” but it looks as though it would do exactly what I need.
Could you confirm something for me? Does this automation trigger the Apps sidebar to popup on the right side of the screen every time it runs? Or does the automation just run non-invasively in the background?
Jun 02, 2022 12:05 AM
Hey twoodl, no worries!
It’ll run in the background and will not trigger the Apps sidebar to popup