Discussions

Expand all | Collapse all

Report to show duration between each status change

  • 1.  Report to show duration between each status change

    Posted 03-27-2017 16:58
    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.


  • 2.  RE: Report to show duration between each status change

    Posted 03-27-2017 17:18
    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.


  • 3.  RE: Report to show duration between each status change

    Posted 11-29-2017 01:06
    Would you share your method in more detail?


  • 4.  RE: Report to show duration between each status change

    Posted 03-27-2017 17:43
    ... 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.


  • 5.  RE: Report to show duration between each status change

    Posted 03-27-2017 17:49
    Oh... I did it the other day in 30 min... 100% native.  no need for limiting snapshots, and zero form rules...


  • 6.  RE: Report to show duration between each status change

    Posted 03-27-2017 17:52
    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.


  • 7.  RE: Report to show duration between each status change

    Posted 03-27-2017 17:53
    and "zero" reverse relationships I should add.


  • 8.  RE: Report to show duration between each status change

    Posted 03-27-2017 17:57
    Matthew, good trick if you can do this natively and without snapshots. _


  • 9.  RE: Report to show duration between each status change

    Posted 11-29-2017 02:31
    I could demo my method if you contact me via my website. QuickBaseCoach.com