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:
Formula field - Time spent with Worker: 100 minutes
Formula field - Time spent with Reviewer: 25 minutes
Formula field - Time spent with Manager: 15 minutes
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!
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
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:
Updates the appropriate Last changed to field with the current date and time
For example, if we’re changing it to Worker now, we’ll update the Last changed to Worker field with the current date and time
Looks through all the Last changed to fields 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
Consider the following example:
- Last changed to Worker has a date time of today, 1:00 pm
- Last changed to Reviewer has a date time of today, 1:01 pm
- We have just changed the single select field to Manager
Since Last changed to Reviewer has the latest timing, we assume that, before we changed it to Manager, it was set to Reviewer
Another option would be to have a table entirely dedicated to historical data which we use to calculate the time spent for from
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?