Null time formatting as midnight in dw datetime column when time format set

[Former Member]

I'm seeking advice regarding the default formatting of a null time to 12:00:00am in a dw datetime column when time format and/or edit mask set.

I am using PB12.5.2.

The application has a parsing routine that will extract times and dates from a string, ie 'Between 3pm and 4pm on 3 June 2015 Mr John Smith ....'  On other occasions there will only be one time, ie 'At 3pm on 3 June 2015 Mr John smith ....' The 'from' time will be valid, the 'to time' null but the 'to time' will default to 12:00:00am.  This is not a problem if the formatting/mask is turned of - the column will be empty.

I tried using the following 'modify' code if a null time was encountered, and use editchanged to turn the formatting/mask back on if the user decided to enter a time.  My modify code, below, must be wrong but is there a neater solution?

ldt_test = dw_event.GetitemDateTime(dw_event.GetRow(),'event_to_time')

If IsNull(ldt_test) Then

     dw_event.modify("event_to_time.editmask=''")

     dw_event.modify("event_to_time.format=[general]")

End if

[nayf · Accepted solution]

Hi William,

I only have access to PB 10.2 at the moment so this may or may not apply to 12.5.2, but in 10.2 I can get null times to display as 'empty' instead of 12:00:00am when focus is not in the field by setting the format property. This only works when focus is not in the field, as soon as focus is placed in the field it will again display as 12:00:00am, but will change back to 'empty' when focus moves away from the field.

Format allows a definition for how the data will display when null values are encountered, so looking at your example you'd want to select the field in the DW painter, select the Format tab, set the format to HH:MM:SS;'' (the HH:MM:SS is how the data should display when there is a valid value, the '' after the ; is how the data should display for null values, ie '' - empty string), and make sure to check the Use Format checkbox.

If this doesn't work and you have to rely on your modify solution, I think you need to change

dw_event.modify("event_to_time.editmask=''")

to

dw_event.modify("event_to_time.editmask.Mask=''")

Hope this is of some use.

Nathan

[Former Member]

Many thanks Nathan, John,

The HH:MM:SS;''  worked fine.  Simple and easy

[nayf]

Glad it worked William.

Woo hoo!! My first correct answer ...

[Former Member]

Hi, William -

Many years ago I was part of a team working on an application that made extensive use of date, time-of-day and time-duration values. The business users found the usability of PB's edit mask style confusing and restrictive and complained about the keystrokes it took to specify a value. Showing a null time value as 00:00:00 or 12:00:00 AM is one example of how limiting the functionality of an edit mask can be. And don't get me started on what some users think about the usability of spin buttons in an edit mask.

On this particular project, we chose to give the user a simple edit field instead of using edit masks. In each case, we had two column objects in the DataWindow: One for the "real" updating date, date/time, time column (that was not included in the visual presentation) and a string "psuedo-column" (not stored in the table/database) that WAS included in the DW's visual presentation. We populated the string value in the SQL SELECT to values/standards the business users were familiar with ("45" for 45 seconds, or "90" or "1:30" for 1 minute 30 seconds, 3pm for 15:00:00, 1h for 1:00:00 [one hour], for example). For input, we wrote some PB data validation functions that could interpret user's input expressed in a multitude of standard ways that made sense to them, then converted the user's value to the appropriate date, date/time, time-of-day or time-duration value in the ItemChanged event and populated the actual, not-visible but update-capable column object with the converted value.

It took a little effort to develop and refine the technique, but we easily reused it throughout the app once it was working,,,and the users LOVED that we listened to them and found a solution for their needs.

I'm not suggesting you need to or must do something similar in your situation; I only share this example to illustrate that there can be alternatives to using the often-inflexible PB edit mask edit style.

John