# Sum minutes between child records

• 0
• Question
• Updated 2 years ago
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.
• 110 Points

Posted 2 years ago

• 0
• 2,100 Points
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.
• 110 Points
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.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,064 Points
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.
• 31,678 Points
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)
• 110 Points
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
• 31,678 Points
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.
• 110 Points
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)
• 31,678 Points
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.
• 31,678 Points
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.
• 110 Points
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))
)