Calculate Duration between Log Edits

  • 0
  • 2
  • Question
  • Updated 3 years ago
  • Answered

Our application utilize a multiple choice 'Stage' field for sales opportunities.  The field logs changes. Here's an example of the log edits for that field:

[AUG-10-16 10:40 AM  John Snow] The King of the North

[AUG-09-16  1:24 PM  John Snow] You know nothing, John Snow

[AUG-09-16  1:23 PM  John Snow] Trial by Combat

I'd like to be able identify the duration of time that a record spends on each  'Stage'.  Is it possible to force the log edits to include this data or is there a formula that could be used that would search the log edit's to display the duration data for each stage listed in within the log edits? 
Photo of Ringoparr

Ringoparr

  • 854 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
This in not a casual undertaking.   Trying to parse elapsed time out of a simple log field imho will be beyond ugly

 I had a need to exactly the same thing for a client.  ie, to track time in each status for a Help Ticket system,  and I would be happy to do a demo of the setup.  In my case I had a proper child table where each status update to the main project record is ONLY able to be done by updating the status of the child records.  

In my case I provided a button to do a status update and the button stopped the click on the most recent status update record and started a new status record.  Then I could have a stupid simple summary report of elapsed time grouped by Status.
Photo of Ringoparr

Ringoparr

  • 854 Points 500 badge 2x thumb
I figured as much.  I like the idea of creating a Stage table that's a child of Opportunities.  But is it possible to adjust the Add Stage Url button to edit a particular field on all previous 'Stage' records associated with the Opportunity and create a new 'Stage' record?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
I don't see shy you need to update all previous Stages. Don't you just need to update the most current stage and then add a new one.
Photo of Ringoparr

Ringoparr

  • 854 Points 500 badge 2x thumb
Yes, you're right.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
So that is what I did.  I did a summary field to get the maximum rcrd ID# where the stop date/time was not blank.  Then I created a URL formula button to stop that date /time with the current date/time and then call up an GenNewRecrdForm to present the user wit the Add form for the next status update.