Report to show duration between each status change

  • 0
  • 2
  • Question
  • Updated 2 years ago
  • Answered
I set up notifications to go to a GMail account for any changes to a "Status" field (multiple choice). I then made a new table title "Edit Log" and linked it to the GMail data. Within this table, I have columns "Subject" and "Date Created" ....Within the Subject column for my notifications, I've chosen to display the Record ID, Date, and Status (ie Billed, Invoiced, etc.)

Can I somehow create a report to show duration between each change on a each record? I'm trying to find any delays in our billing work flow.

Example:
Record 20
Status changed "Report Generation" to "Billed" on 3/12/17
Then changed from "Billed" to "Invoiced" on 3/24/17

Those are reflected in my QB table as:

Subject                                       Date Created
20,3/12/17,Billed                          3/12/17
20,3/24/17,Invoiced                      3/24/17

In order to do what I need, I feel like I need additional fields for the Record, and Status, but not sure if it's possible to show duration once I get those fields to populate (Using functions "Right" and "Left"). I hope I'm making sense! Any input is appreciated.
Photo of Rebeccah Demaray

Rebeccah Demaray

  • 112 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 2
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
You should make the "Status Change" a child table of the the main Job.  

Then you can do it all within QuickBase without notifications, or sync.  Then you can easily see the duration of each status right in one spot. 
You can also make the status change, but clicking "one" button.   You can also see who, and when status changed.  

Its a much cleaner solution, happens instantly, much easier to maintain, and is not dependent on outside systems.
Photo of melizzza

melizzza

  • 628 Points 500 badge 2x thumb
Would you share your method in more detail?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,874 Points 50k badge 2x thumb
... but its still difficult to calculate the duration between status updates even with a child table.  Might be possible with summary fields to get the Date time of the last status change update  and then snapshots, but it would take some messing around to get that working.
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
Oh... I did it the other day in 30 min... 100% native.  no need for limiting snapshots, and zero form rules...
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
MS> its still difficult ...

Here is my advice: If you have more than one summary and lookup field included in your formula you should be using script for the entire solution. Mark may relax the one to some other number (say a milliion) but the larger the number the more pain there is in writing and maintaining the formula and extra fields required.
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
and "zero" reverse relationships I should add.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,874 Points 50k badge 2x thumb
Matthew, good trick if you can do this natively and without snapshots. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,874 Points 50k badge 2x thumb
I could demo my method if you contact me via my website. QuickBaseCoach.com