Discussions

Expand all | Collapse all

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

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

    Posted 07-10-2018 20:49
    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. 


  • 2.  RE: How to calculate total time for which a field held a particular value

    Posted 07-11-2018 04:54
    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.


  • 3.  RE: How to calculate total time for which a field held a particular value

    Posted 07-11-2018 12:03
    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).


  • 4.  RE: How to calculate total time for which a field held a particular value

    Posted 07-17-2018 16:50
    Sounds complicated. Any other easy way?