Current time in a calculated field

For HanDBase related topics that are not specific to one platform.

Current time in a calculated field

Postby ParamitePie » Wed Oct 01, 2014 7:01 pm

I'm writing a daily schedule database.
Once the tasks and their expected durations have been entered, it works in list mode.
Given a starting time of day, it projects the start and end times of the remaining tasks.

Is there a way to find the current time of day in a calculated field formula?
My record has a sequence number that I use to order the rows. The first row is always sequence 0.
I'm thinking something like
Start Time = (if seqnum=0 then NOW() else previous row's end time)

I haven't been able to find a NOW() function, or a way to emulate it.

Thanks
ParamitePie
 
Posts: 5
Joined: Wed Oct 01, 2014 6:35 pm

Re: Current time in a calculated field

Postby Brian_Houghton » Thu Oct 02, 2014 5:42 am

Hi,

My suggestion would be to configure a second time field to use the parameter "time record last modified" and then use that field within your calculation. This should be the closest to a NOW value.

Give it a try and let us know if you have any questions.
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: Current time in a calculated field

Postby aa6vhDave » Thu Oct 02, 2014 10:18 am

What I do is create a column (that I call TodaysDate) that is hidden, and is set to the current date. Then I just use that column in my formula's.
aa6vhDave
 
Posts: 53
Joined: Mon Aug 19, 2013 1:42 pm

Re: Current time in a calculated field

Postby ParamitePie » Thu Oct 02, 2014 11:53 pm

Brian_Houghton wrote:Hi,

My suggestion would be to configure a second time field to use the parameter "time record last modified" and then use that field within your calculation. This should be the closest to a NOW value.

Give it a try and let us know if you have any questions.



Thanks for your reply.
I created a new time field and set it to 'time record last modified'.
Now, to update my times, I just have to remember to open and save the one record that will use that field.
Easy enough.

The approach works, but when I try to use the time in a conditional field and then use the result of that conditional field, it doesn't retain its value.
I hope this simplified example illustrates it well enough.

I have 4 fields
Seq: [Integer]
Timeval: [Time] Ask user
ConditionalTime: [Time] if Seq = 0 then Timeval else Timeval /*for this example, just to get a value into ConditionalTime*/
CalculatedTime: [Time] ConditionalTime+0

Seq: 0
Timeval: 4:03:02 AM /*entered manually*/
ConditionalTime: 4:03 am
CalculatedTime: 0:00:04

There seems to be something going on between the ConditionalTime and wherever I use the result - my input HH:MM:SS gets converted to HH seconds.

I built the database separately on Android and Windows Desktop.
The results are the same on both platforms.
Here's hoping I've set something up incorrectly - that would be an easy fix.
ParamitePie
 
Posts: 5
Joined: Wed Oct 01, 2014 6:35 pm

Re: Current time in a calculated field

Postby ParamitePie » Thu Oct 02, 2014 11:55 pm

aa6vhDave wrote:What I do is create a column (that I call TodaysDate) that is hidden, and is set to the current date. Then I just use that column in my formula's.



Thanks aa6vhDave.
Do you use your field to obtain the current time, or only the current date?

I couldn't find a 'currenttime' equivalent on a time field.
I created a date field with the current date.
I couldn't see how to get the current time from it (hoping a CurrentDate field would actually be a CurrentDateTime field).
ParamitePie
 
Posts: 5
Joined: Wed Oct 01, 2014 6:35 pm

Re: Current time in a calculated field

Postby Brian_Houghton » Tue Oct 07, 2014 5:48 am

Based on the additional information, it looks like the problem is that HanDBase is continually recalculating the current time value, so while the initial difference was 2 seconds in the example data by the time the program processes the time within the calculation another 2 seconds must have elapsed, hense the 4 second difference.

The option to use the time record modified is not perfect and will include the second differential issue you are experiencing.
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: Current time in a calculated field

Postby ParamitePie » Thu Oct 09, 2014 6:52 am

Thanks for your reply.
It looks like a bug in conditional fields.
They go wrong when the result of the conditional is sourced from a time field.
When you use the result of such a conditional, you get the hours value as seconds.
For example take a time field with value 01:02:03 (ie, just after 1AM).
If you assign that field a the result of a conditional field, you get 01:02:03.
Then it takes a turn for the worse.
If you then use that conditional value, you'll find you're working with a value of 1 second (ie, the hours part of the original time, but labelled 'seconds').
This behaviour is reproducible.
Time hh:mm:ss -> Conditional hh:mm:ss -> Time 00:00:hh

Luckily there's a workaround, because time values appear to be stored as integers (the number of seconds since midnight).
That means time fields can be assigned to integer fields without loss of value.
Before applying the conditional, copy the time to an integer value. Use the integer in the conditional, then convert the result back to a time by assigning it to a time field.
Works like a charm.
Time hh:mm:ss -> Integer -> Conditional (Integer) -> Time hh:mm:ss

As an aside, the integer value of 01:02:03 is 1x3600+2x60+3 = 3723.

It would be good if I could use time field directly in a conditional field, but the workaround is OK for those who are aware of it.

This applies to the windows desktop version and the android version.
ParamitePie
 
Posts: 5
Joined: Wed Oct 01, 2014 6:35 pm

Re: Current time in a calculated field

Postby ParamitePie » Wed Oct 15, 2014 5:29 pm

For more information regarding time fields in conditionals, see the topic 'Time Field Calculations and Conditionals'.
ParamitePie
 
Posts: 5
Joined: Wed Oct 01, 2014 6:35 pm


Return to HanDBase General Discussion

Who is online

Users browsing this forum: Google [Bot] and 1 guest