Discussions

Expand all | Collapse all

Calculating Sum of Duration From Multiple Time Stamps

  • 1.  Calculating Sum of Duration From Multiple Time Stamps

    Posted 30 days ago

    Hi, 

    I am recording time stamps using automation in the child table whenever there is a status update in the parent table and I need to calculate the time difference between 2 time stamps say start and stop. I was able to do it when there are only 2 time stamp but the problem is when I have more than 1 start and 1 stop. 

    I need the difference between the 1st start and stop and similarly for the consecutive start and stops. There might be 20 such start and stop before a project is completed and I need the sum of the difference between each start and stop. 

    Any help is appreciated. 

    Thanks in advance. 



    ------------------------------
    Srinath MR
    ------------------------------


  • 2.  RE: Calculating Sum of Duration From Multiple Time Stamps

    Posted 29 days ago

    Srinath,

    I would suggest an architecture change so that you can capture the Duration from a Child Table and then Summarize that Duration in the Parent Table.

    Here is a small ERD to show what I am proposing

    The [Duration] in the Child table would be a Formula Duration  [Stop]-[Start] where both are Date/Time fields.

    You then use a Summary field in the Relationship between Parent and Child to add together all the Durations from the Child table.

    As a work flow issue you will need to make sure that Users can only add a Child and set [Start] when the last Child record has a value for [Stop].  You can do this by creating another value in the Relationship called Max RID Child.

    Here is a screen shot of the creation process

    Now you know which Child record holds the most current Start and Stop.   There are a variety ways to control the Users ability to create child records and edit them.  My personal favorite is to use Formula Rich Text field where you evaluate the Child Record.  You can also set the ability to modify the records based upon the value of [Stop]

     



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 3.  RE: Calculating Sum of Duration From Multiple Time Stamps

    Posted 29 days ago

    Hi Don,

    Thanks for the response but if I restrict the access of adding new record only after the [stop] value of the last record is updated then I might not be able to record the logs of different project that is happening on the same time right? Also, the record in the child table is created by automation and not by any user. 

    And can you explain a bit on how to restrict creating a new record until the old record has the value for stop?

    Thanks,

    Srinath.



    ------------------------------
    Srinath MR
    ------------------------------



  • 4.  RE: Calculating Sum of Duration From Multiple Time Stamps

    Posted 29 days ago

    Srinath,

    Summary Fields only calculate calculate against the child records that they are related to.  Assume your child Table is called Time Cards. Your Projects Table will be the Parent of the Time Card table.

    If you create the Summary Field on the Maximum RID of the Time Cards table, the value that it returns to a Project will be unique.   It is the maximum value of the RID for a Time Card for its Parent.   It is NOT the maximum value of RID for all Time Cards.  This is the crucial part and the magic of Quick Base, the relationship between the Project and the Time Card ensures that none of the other Time Cards for other Projects will impact it.

    I would have the User manually "Log In and Log Out" of the Project Creating the Time Card.  You can do this with a single Formula Rich Text field.  Your business process rule is pretty simple

    • If there is no Current Time Card, let the User create a Time Card
    • If there is a Current Time Card, only let the User put in a value for Stop

    First you want create your Summary Field and then clear the check box on it

     This will let you evaluate whether or not the Project currently has a Time Card that need a value for [Stop]  If all the Time Cards have a Stop value then this Summary Field will have a Null value.  

    Your Rich Text field could work something like this if Start is FID 6, Stop is FID 7, Related Project is FID 9

    // Image for the User to see of a Clock

    var text Clock = "<img src=\"https://images.quickbase.com/si/32/049-clock.png\">";

    // Messages for the User

    var text InMSG = "Click to Log In";
    var text OutMSG = "Click to Log Out";

    //  URLS to LogIn and Log Out of the Time Card

    var text URLIn =

    URLRoot() & "db/" & [_DBID_TimeCard] & "?act=API_AddRecord&apptoken=xxxxxxxxxxxxxx" & 
    "&_fid_6=" & URLEncode (Now())&
    "&_fid_9=" & URLEncode ([Record ID#]);

    var text URLOut =

    URLRoot() & "db/" & [_DBID_TimeCard] & "?act=API_EditRecord&apptoken=XXXXXXXXXX&rid=" & URLEncode ([Max RID Time Card])&
    "&_fid_7=" & URLEncode (Now());

    // Business Process Logic to LogIn or Log Out of Project

    If( IsNull([Max RID Time Card])=true,
    "<a href=\"javascript:" & "$.get('" & $URLIn & "', function(){" & "location.reload();" & "});" & "void(0);\">"&$Clock&"<br>"&$InMSG & "</a>",
    "<a href=\"javascript:" & "$.get('" & $URLOut & "', function(){" & "location.reload();" & "});" & "void(0);\">"&$Clock& "<br>"&$OutMSG &"</a>")

    URLIn is the API call to Create the Time Card and sets Start to the current time

    URLOut is the API call to edit the Time Card that has the RID of the Max Time Card for the Project and sets Stop to the Current Time

    The Javascript is from Kirk Trachy's Magic Buttons application.  It just works and I cannot really tell you why as I am not a Java guy.  But it is very cool that you just click the image and it does all the work and brings the User right back to where they were.  The Users love it because you have done all the work.



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 5.  RE: Calculating Sum of Duration From Multiple Time Stamps

    Posted 29 days ago

    Thanks Don. It did work. I appreciate it. 



    ------------------------------
    Srinath MR
    ------------------------------



  • 6.  RE: Calculating Sum of Duration From Multiple Time Stamps

    Posted 29 days ago
    Edited by Blake Harrison 29 days ago

    If you do not need to keep the Start/Stop timestamps for historical purposes, you can set it up like this:

    • Start Button - API to push current date/time to Start field, Update Hours Worked with Hours Update, and clear the Stop and Time fields
    • Start - Date/Time field to accept current date/time from Start Button
    • Stop Button - API to push current date/time to Stop field
    • Stop - Date/Time field to accept current date/time from Stop Button
    • Time - Numeric or Duration Formula (depending on what you need) to calculate difference between Start and Stop
    • Hours Worked - Numeric or Duration field (depending on what you need) to capture cumulative time spent on task
    • Hours Update - Numeric or Duration Formula (depending on what you need) to calculate cumulative time spent on task (Time + Hours Worked)

    I use this process on Timesheets when the client does not need to maintain individual Start/Stop records. Of course, this is the manual process, but you could adapt it fairly easily for your automated process.



    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------



  • 7.  RE: Calculating Sum of Duration From Multiple Time Stamps

    Posted 29 days ago

    Hi Blake, Thanks for the response. I appreciate it. However we do need the historic records as logs for other metrics as well. Thanks for your help. 



    ------------------------------
    Srinath MR
    ------------------------------