Sum minutes between child records

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I created a button to create child records of a stop/start sequence from the parent record.  Basically the user stops and starts the ticket due to interruptions.  We need to calculate the minutes between the stops and starts so that we can deduct the time of interruptions from the total time of the work on the parent trouble ticket.
Photo of Randy

Randy

  • 110 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Chuck Grigsby

Chuck Grigsby

  • 1,978 Points 1k badge 2x thumb
Hm I never really messed with time line charts but wonder if you could use that. It may give a nice timeline that spans out between the Date Created field on each child record since they are already in a start/stop order. 
Photo of Randy

Randy

  • 110 Points 100 badge 2x thumb
Thanks for the suggestion, but not sure that will work.  I'm looking for is a sum count of minutes that can be brought back over to the parent account.  For example, the hit stop and start that equals 10 minutes, so the actual ticket work time needs to be deducted 10 minutes.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
You can calculate the total time worked by subtracting two summary fields which sum up the [end time] and [start time]. This is because the sum of the individual time differences between the [end time] and the [start time] is equal to the sum of the [end time]s less the sum of the [start time]s.
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
If you haven't already, you will need to have each of those start/stops be part of a child table.

Every time you click "start" it creates a new record, every time you click "stop" it edits that record with a stop date/time.  Then each record will have a duration.

(Reverse this if you want the "stop" duration to be the record)

Then you will have the first start time, and the final stop time, and all the active times and duration that you need.

(this is basically an advanced way to track log changes)
Photo of Randy

Randy

  • 110 Points 100 badge 2x thumb
That makes more sense.  I need to modify my code to edit the existing record, rather than create a new one.  My child table looks like this:
Record ID: 17
Status: Stop
Status Change: 9/22/2017 11:04AM
Ticket ID: TID0031

Record ID: 18
Status: Start
Status Change: 9/22/2017 11:06AM
Ticket ID: TID0031

Record ID: 19
Status: Stop
Status Change: 9/22/2017 11:15AM
Ticket ID: TID0031

Record ID: 22
Status: Start
Status Change: 9/22/2017 11:16AM
Ticket ID: TID0031
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
I would have the "Start" and the "stop" be on the same record.  So you can get a duration on that record, then summarize the "total" duration.

IF they are separate records, you get a mess trying to get a duration with multiple records.
Photo of Randy

Randy

  • 110 Points 100 badge 2x thumb
I can't thank you all enough for your direction.  But how would you edit this so that it doesn't create a new record for the 'stop', but just edits it?  I think the 'start' should create a new record.  I can't seem to figure out what the latest record to use API_EditRecord on for the edit of the new stop field.  Here is my code that has an if statement that dynamically changes the URL:

// Create Start Child Record
var text URLSTARTONE= 
URLRoot() & "db/" & [_DBID_TICKET_TIME_HISTORY] 
& "?act=API_AddRecord&_fid_8=" & URLEncode ([Record ID#])
& "&_fid_6="&URLEncode("Start")
& "&_fid_7="&URLEncode(Now());

//Change Start Parent
var text URLSTARTTWO = 
URLRoot() & "db/" & Dbid () & "?act=API_EditRecord&rid=" & [Record ID#]
& "&_fid_12="&URLEncode("O-In-Progress")
& "&_fid_17="&URLEncode("O-In-Progress")
& "&_fid_14="&URLEncode(Now());
//& "&rdr=" & URLEncode(URLRoot() & "db/xxxxxxxxxxx?a=td");

// Create Stop Child Record
var text URLSTOPONE= 
URLRoot() & "db/" & [_DBID_TICKET_TIME_HISTORY] 
& "?act=API_AddRecord&_fid_8=" & URLEncode ([Record ID#])
& "&_fid_6="&URLEncode("Stop")
& "&_fid_7="&URLEncode(Now());

//Change Stop Parent
var text URLSTOPTWO = 
URLRoot() & "db/" & Dbid () & "?act=API_EditRecord&rid=" & [Record ID#]
& "&_fid_12="&URLEncode("O-Stalled")
& "&_fid_17="&URLEncode("O-Stalled")
& "&_fid_14="&URLEncode(Now());
//& "&rdr=" & URLEncode(URLRoot() & "db/xxxxxxxxxxx?a=td");

var text URLTHREE =
//URLRoot() & "db/" & "db/" & [_DBID_TICKETS] 
//& "?a=dr&rid=" & ToText([Record ID#]);
URLRoot() & "db/" & [_DBID_TICKETS]  & "?a=dr&rid=" & ToText([Record ID#]);

If(
[Status]="O-Stalled",
$URLSTARTONE 
&"&rdr="&URLEncode($URLSTARTTWO)
&URLEncode("&rdr="&URLEncode($URLTHREE))
,
$URLSTOPONE 
&"&rdr="&URLEncode($URLSTOPTWO)
&URLEncode("&rdr="&URLEncode($URLTHREE))
)
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Create a summary field from the logs to the parent record.  You will summarize the "Maximum" Record ID#.  That will give you the last record ID for the 'Open' log.  Then use that as the value for the edit.
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
If you want to do a share screen a little later in the day I could help you get there.  There are some tricks to getting it act seamless for the user.  But you are probably getting close already.
Photo of Randy

Randy

  • 110 Points 100 badge 2x thumb
Thank you, that worked perfectly!  I created the 'maximum' summary on Record ID# to get the last record.  Here is the final code, I think it might help others in trying to create a dynamic button (the xxxxxxx is your Database ID):

//Change Start Parent
var text URLSTARTTWO = 
URLRoot() & "db/" & Dbid () & "?act=API_EditRecord&rid=" & [Record ID#]
& "&_fid_12="&URLEncode("O-In-Progress")
& "&_fid_17="&URLEncode("O-In-Progress")
& "&_fid_14="&URLEncode(Now());
//& "&rdr=" & URLEncode(URLRoot() & "db/xxxxxxxxxx?a=td");

// Create Start Child Record
var text URLSTARTONE= 
URLRoot() & "db/" & [_DBID_TICKET_TIME_HISTORY] 
& "?act=API_EditRecord&rid=" & [Maximum Record ID#]
& "&_fid_8=" & URLEncode ([Record ID#])
& "&_fid_6="&URLEncode("Start")
& "&_fid_10="&URLEncode(Now());

// Edit Stop Child Record
var text URLSTOPONE= 
URLRoot() & "db/" & [_DBID_TICKET_TIME_HISTORY] 
& "?act=API_AddRecord&_fid_8=" & URLEncode ([Record ID#])
& "&_fid_6="&URLEncode("Stop")
& "&_fid_7="&URLEncode(Now());

//Change Stop Parent
var text URLSTOPTWO = 
URLRoot() & "db/" & Dbid () & "?act=API_EditRecord&rid=" & [Record ID#]
& "&_fid_12="&URLEncode("O-Stalled")
& "&_fid_17="&URLEncode("O-Stalled")
& "&_fid_14="&URLEncode(Now());
//& "&rdr=" & URLEncode(URLRoot() & "db/xxxxxxxxx?a=td");

var text URLTHREE =
//URLRoot() & "db/" & "db/" & [_DBID_TICKETS] 
//& "?a=dr&rid=" & ToText([Record ID#]);
URLRoot() & "db/" & [_DBID_TICKETS]  & "?a=dr&rid=" & ToText([Record ID#]);

If(
[Status]="O-Stalled",
$URLSTARTONE 
&"&rdr="&URLEncode($URLSTARTTWO)
&URLEncode("&rdr="&URLEncode($URLTHREE))
,
$URLSTOPONE 
&"&rdr="&URLEncode($URLSTOPTWO)
&URLEncode("&rdr="&URLEncode($URLTHREE))
)