Time Field Calculations and Conditionals

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

Time Field Calculations and Conditionals

Postby aa6vhDave » Wed Oct 15, 2014 3:25 pm

There is a issue I encountered in time field calculations and conditionals. Fixing this problem could cause issues with existing databases, so I am not recommending changing anything. But this should be documented somewhere, so users are made aware of this issue.

I have a date and a time column that records the date and time that a check box is checked. I also wanted to display an offset to that time as it would be on the east coast (three hours difference). That offset time did not work.

Here is a test database to demonstrate the offset issue. The format:

Name Text
Time Time
IfTime Conditional: IF Name <> "xxx" THEN Time ELSE Time
iTime Calculated (display as integer): Time+0
iIf Calculated (display as integer): IfTime+0

Create a record. After record save, record shows:

Name: Test
Time: 8:52 am
IfTime: 8:52 am
iTime: 31961
iIf: 8

I expected the iIf column to have the same value as iTime. However the IfTime field has been stored as the text string "8:52 am" instead of the integer time second value, and the subsequent iIf calculation truncates the ":52 am" off as the colon and following stuff is not numeric (leaving only the 8).

Workaround (if you need to adjust the iIf time):

Create a parallel time column as an integer:

iTime Calculated (display as integer): Time+0

Do all of the calculations/conditionals on the iTime integer column.

When you need to display the iTime time back in time format, use the following column:

NewTime Calculated (display as time): iTime+0

For those wondering how I record the date/time when a check box is checked (note that order of columns is important):

TodaysDate Date (Set to current date) (hidden)
TodaysTime Time (Set to record modified time) (hidden)
// best that can be done right now since there is no "set to current time" yet. Modified time is not always
// set like expected, so be aware of that fact (may require a double save to get it set).
Complete CheckBox
LDate Conditional IF Complete <> CompSave THEN TodaysDate ELSE LDate (hidden)
LTime Conditional IF Complete <> CompSave THEN TodaysTime ELSE LTime (hidden)
CompSave Conditional IF Complete = 1 THEN 1 ELSE 0 (hidden)
LastDate Conditional IF ConpSave = 1 THEN LDate ELSE LastDate
LastTime Conditional IF CompSave = 1 THEN LTime ELSE LastTime

The above takes advantage that HanDBase evaluates columns in column order.

LastDate and LastTime will contain the date and time that the "Complete" check box was checked off.
Posts: 56
Joined: Mon Aug 19, 2013 1:42 pm

Return to HanDBase General Discussion

Who is online

Users browsing this forum: Exabot [Bot] and 2 guests