Calculating Sum of Duration From Multiple Time Stamps

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
------------------------------