Powerbuilder Datawindow Column Line Feeds Removed When Clicked

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:

You might also be interested in

huffwourry.blogspot.com

Source: https://anvil-of-time.com/powerbuilder/powerbuilder-formatting-and-validating-datetime-entries-in-a-datawindow/

Share this post