PowerBuilder – Formatting and Validating Date/Time entries in a Datawindow
Using an editmask on a datawindow column to format a user’s input is pretty common practice for most applications. Done right it leads to a better user experience (no extra formatting characters to type) and can be visually appealing (all phone numbers formatted the same way for example). One really annoying behavior of an editmask appears when they are put on datetime fields and you need a way to validate that the user has entered a time and/or a date.
Since ’00:00′, using hh:mm format, is a valid time, how do you ensure the user put in a value on a newly inserted row? Now if the time is the only column on the datawindow you can check the itemstatus of the new row to see if it is NewModified! but this would rarely be practical. Other issues with formats and EditMasks on datetime columns is they can sometimes show data you don’t want to be shown or invalid data can cause unexpected results.
This solution involves using computed columns in the SQL statement making up the datawindow.
SQL statement for Datawindow Object
SELECT cht_cls_by , CONVERT(nvarchar(10), cht_cls_dt, 101) as cht_cls_date -- computed column , CONVERT(nvarchar(5), cht_cls_dt, 108) as cht_cls_time -- computed column , cas_int_id , cht_cls_dt FROM CASE_HEADER
Results
cht_cls_by cht_cls_date cht_cls_time cas_int_id cht_cls_dt mbalent 11/01/2010 13:00 4504 2010-11-01 13:00:00.000 nkennard 10/19/2010 10:30 4505 2010-10-19 10:30:57.600 hcorey 10/20/2010 13:02 4506 2010-10-20 13:02:31.790 NULL NULL NULL 4507 NULL NULL NULL NULL 4508 NULL NULL NULL NULL 4509 NULL
Now on the datawindow object itself you place the cht_cls_by, cht_cls_date, and cht_cls_time columns. In the update properties of the datawindow you select the cht_cls_by and the cht_cls_dt columns (and the id column too if you allow the inserting of rows).
In the ItemChanged event put the following:
date ldt_date
CHOOSE CASE dwo.name
case 'cht_cls_time'
IF IsTime(data) THEN
post event ue_post_itemchanged(dwo.Name, row, data)
ELSE
Messagebox('Error', "Please enter a valid time in 'hh:mm' format.")
RETURN 1
END IF
case 'cht_cls_date'
If IsDate(data) THEN
post event ue_post_itemchanged(dwo.Name, row, data)
ELSE
Messagebox('Error', "Please enter a valid date.")
RETURN 1
END IF
END CHOOSE
Create a user event on the datawindow called ue_post_itemchanged.
It takes three parameters: as_name (string), al_row (long), as_data (string)
// posted from itemchanged to format time value correctly // and to update the datetime column with the time portion date ld_date datetime ldt_db_date string ls_data_date time lt_time CHOOSE CASE al_name CASE 'cht_cls_date' ldt_db_date = this.getitemdatetime(al_row,'cht_cls_dt') IF IsNull(ldt_db_date) THEN ldt_db_date = datetime(Today()) // no date, set to today (time is 00:00) END IF lt_time = Time(ldt_db_date) // get current datetime value ls_data_date = string(Date(al_data), 'mm/dd/yyyy') // set to standard format this.setitem(al_row,'cht_cls_date', ls_data_date) // put back to datawindow this.setitem(al_row,'cht_cls_dt', DateTime(Date(al_data),lt_time)) // put the time value on the datetime CASE 'cht_cls_time' ldt_db_date = this.getitemdatetime(al_row,'cht_cls_dt') IF IsNull(ldt_db_date) THEN ldt_db_date = datetime(Today()) // no date, set to today (time is 00:00) END IF IF POS(al_data,':') > 0 THEN al_data = Replace(al_data,Pos(al_data,':'),1,'') // strip off any colon END IF // ensure value has four positions DO WHILE Len(al_data) < 4 IF Len(al_data) = 1 THEN al_data = '0' + al_data ELSE al_data +='0' END IF LOOP al_data = Left(al_data,2) + ':' + Right(al_data, 2) // insert colon this.setitem(al_row,'cht_cls_time', al_data) // set the time value so it looks consistant this.setitem(al_row,'cht_cls_dt', DateTime(Date(ldt_db_date),Time(al_data))) // put the time value on the datetime END CHOOSE
Finally in ItemError event put the following:
CHOOSE CASE dwo.name CASE 'cht_cls_time', 'cht_cls_date' RETURN 2 END CHOOSE
You will also have to code a validation routine which will make sure data has been entered in each cht_cls_date and cht_cls_time columns for rows which have been modified. This would be called after accepttext but prior to updating the database.
Example:
