Calculate time in stage

  • 0
  • 2
  • Question
  • Updated 1 month ago
  • In Progress
Hi All,

I am creating an app to track performance by a team and need to be able to say how long an activity has been with them and how long they are waiting on another team. To do this I am looking at having a status field that tracks when the stage is changed. 



Can I use a time calculation to calculate the time in stage from the field history or is there a more sensible way to do this?
Photo of James

James

  • 172 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 2
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Doing a "log Edits" never gives you the reporting capability that you need.

Create a child table of "Status Logs" and then you can see when it changes from one status to another.  You'll be able to see who changed, when, and easily calculate the time.  even summarize and combine many of the same status times together.

I've been setting one of these up right now for tracking the lead status for a door to door sales company.


***No reverse relationships needed***
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
One click Status Updates (from forms, reports or dashboards)
Photo of Stephanie

Stephanie

  • 720 Points 500 badge 2x thumb
Matthew, this looks like exactly what I am trying to do in my application.  I currently have a Detail Status field and the status selected can go back and forth: Researching to Reviewing Video back to Researching.  I would love to be able to track the time spent in each status and have the ability to add the same status times together.

What fields do I need to created on my new Status Logs table?

How can I make this tracking happen in the background so that the user experience stays the same, where they simply select the current status and the logs are automatically tracked?

Thank You
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
If an application could have the status change based on the passage of time, how could those types of changes get logged automatically within this same framework? Likewise, grid edit throws a monkey wrench into all of this. How could those get logged as well?

Thanks,
Michael
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I'm not sure I follow you completely, but...

If the status changes over time, without a human touch, you would need a cron job running and some advanced logic in the script to update the status. (Either using this set up, or the traditional dropdown)

Grid edit is a way to mass update records, if you really are mass updating status changes, then I think there might be something wrong with the process, or your should consider an import.
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
In our PM system, the status is a formula field. It changes based on the passage of time, entries into a number of other fields, and context within a child table. Grid edit is used to update a number of these other fields, which then affect the status. 
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
That is another good way to do status changes.
If you are automating the status changes, like that they you wont need this set up.  

But if you want to log the changes in the stats, you will need to set up a logs table, and use either webhooks, or QB actions to create new "status logs"
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
Thanks, that makes sense. Can QB Actions handle the time-related status changes for logging?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
No, Actions can only work during manual edits.  
If its automatic changes (over night), then you will need a cron job that runs externally to compare record details, and watch for changes.
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
Thanks for clarifying that. Bummer. This seems like an area that is needlessly complex, and as a result diminishes QB as a platform. So now I have to go hire a programmer/consultant to write some php code and then rent an external server to run it on, just to do something pretty straightforward from a business logic perspective. Kind of knocks the wind out of a "Citizen Developer".
Photo of melizzza

melizzza

  • 436 Points 250 badge 2x thumb
I'm looking into a possible solution with DOMO reporting that integrates with Quickbase. I've messaged them just now to see if their system could report on durations spent in a certain status. I just signed up for a 1 year free trial, and I can see there are many ways that they could overcome the "eyesore" pain of some of quickbases visual 'features'. This duration based reporting would just be another bonus :D