Forum Discussion

RandyGibbons's avatar
RandyGibbons
Qrew Trainee
7 years ago

Sum minutes between child records

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.

10 Replies

  • 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. 
  • 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.
  • 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.
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    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)
  • 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
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    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.
  • 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))
    )
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      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.
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      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.
    • RandyGibbons's avatar
      RandyGibbons
      Qrew Trainee
      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))
      )