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

  • 0
  • 2
  • Question
  • Updated 4 months ago
  • In Progress
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. 
Photo of Zimil Patel

Zimil Patel

  • 90 Points 75 badge 2x thumb

Posted 4 months ago

  • 0
  • 2
Photo of Danie Grabe

Danie Grabe

  • 92 Points 75 badge 2x thumb
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.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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).
Photo of Zimil Patel

Zimil Patel

  • 90 Points 75 badge 2x thumb
Sounds complicated. Any other easy way?