Forum Discussion

ZimilPatel's avatar
ZimilPatel
Qrew Member
7 years ago

How to calculate total time for which a field held a particular value

We have a ticketing system and to solve a ticket, we often need to gather additional information from the ticket opener and a TPA.  For each ticket, we log all activities (in a child table) in form of activity types like email received from,  email sent to, left voice message for, etc. Based on the latest activity type and date, we update a responsible party field (is a formula text field) in the parent/tickets table with the party who we are currently waiting on - Us, Ticket opener or the TPA.  During the lifetime of a ticket, I want to calculate how much time each party spent on that ticket. 

Let me know if you need more information. 
App builders
Ask about Quickbase functionality
Forms
  • DanieGrabe's avatar
    DanieGrabe
    Qrew Trainee
    7 years ago
    Hi Zimil,
    I have one solution that wil work for you.
    Create a new child table for your ticket table. To account for the current state, create a record in the child table for every ticket and dump the related ticket record id, current value of party and todays date (starting point). Also update the parent records with the record ID of the current child record you created.
    Then write a webhook on the party field that fires when that field changes, looking at the old value of the party field to insert an end date in the child record we created above - using the record ID you stored in the parent record.
    Another webhook can create a new child record with start date for the new party. Remember to store the record id of the new child record in the parent table.
    You can then do a summary report, grouping by ticket ID and party to see duration (simple date calculation) by ticket and party.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Daniel is in the right track here. Consider using an �Automation� to do the two steps as you can be sure if the sequence they will fire in.

    The trick will be to make a report link field which uniquely identifies which records to apply an end date to. Maybe the automation will allow editing all child records without an end date ( there should only be one).