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.
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 ------------------------------
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?
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";
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 ------------------------------