Skip to main content
Question

Sum minutes between child records

  • September 22, 2017
  • 0 replies
  • 77 views

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.
This topic has been closed for replies.

  • Quickbase Alumni
  • September 22, 2017
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. 

  • Author
  • Quickbase Alumni
  • September 22, 2017
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.

  • Quickbase Alumni
  • September 22, 2017
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.

  • Quickbase Alumni
  • September 22, 2017
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)

  • Author
  • Quickbase Alumni
  • September 22, 2017
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

  • Quickbase Alumni
  • September 22, 2017
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.

  • Author
  • Quickbase Alumni
  • September 22, 2017
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))
)