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
------------------------------
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,
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
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
------------------------------
Thanks Don. It did work. I appreciate it.
If you do not need to keep the Start/Stop timestamps for historical purposes, you can set it up like this:
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/
------------------------------
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.
