Forum Discussion

SrinathM_R2's avatar
SrinathM_R2
Qrew Member
5 years ago

Calculating Sum of Duration From Multiple Time Stamps

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
------------------------------
Relationships
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    5 years 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
    ------------------------------

    • SrinathM_R2's avatar
      SrinathM_R2
      Qrew Member
      5 years 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
      ------------------------------
      • DonLarson's avatar
        DonLarson
        Qrew Elite
        5 years 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
        ------------------------------