Users Guide
Working with DataWindows

When you generate a new DataWindow object, PowerBuilder presents the data according to the properties already defined for a column, such as a column's display format and edit style.

Display formats

Display formats embellish data values while still displaying them as letters, numbers, and special characters. Using display formats, for example, you can:

For information, see About display formats.

Edit styles

Edit styles usually take precedence over display formats and specify how column data is presented. For example, using edit styles, you can:

For more information, see About edit styles.

About display format masks and EditMask masks

The differences between display format masks and EditMask masks can be confusing. A display format mask determines the appearance of the column when the focus is off the column, or when the DataWindow object is in print preview mode. When you apply an EditMask edit style, the mask you use determines the appearance of the column when focus is on the column.

If you want data to display differently depending on whether the focus is on or off the column, specify an edit mask (on the Edit property page for the column) as well as a display format (on the Format property page for the column), then check the Use Format check box on the Format property page. The Use Format check box displays only when an edit mask has been specified.

If you want the data to display in the same way whether focus is on or off the column and you have defined an edit mask, you do not need to define a display format. The edit mask is used for display if the Use Format box is not checked (the default).

When data is entered in the Database painter or in a DataWindow object, PowerBuilder evaluates the data against validation rules defined for that column. If the data is valid, PowerBuilder accepts the entry; otherwise, PowerBuilder displays an error message and does not accept the entry.

For more information, see About validation rules.

Typically, you define display formats and associate them with columns in the Database painter, because display formats are properties of the data itself. Once you have associated a display format with a column in the Database painter, it is used by default each time the column is placed in a DataWindow object.

Display formats you assign to a column in the Database painter are used by default when you place the column in a DataWindow object. You can override the default format in the DataWindow painter by choosing another format from the extended attribute system tables or defining an ad hoc format for one specific column.

Customizing the toolbar

You can add buttons to the PainterBar that assign a specified display format to selected columns in reports.

For more information, see Customizing toolbars.

A number display format can have up to four sections. Only the first is required. The three other sections determine how the data displays if its value is negative, zero, or NULL. The sections are separated by semi-colons:

Positive-format;negative-format;zero-format;null-format

Special characters

The following table lists characters that have special meaning in number display formats.

Percent signs, decimal points, parentheses, and spaces display as entered in the mask.

Number keywords

You can use the following keywords as number display formats when you want PowerBuilder to determine an appropriate format to use:

Note that [Currency] and [Currency(n)] are legal edit masks, but they are not legal display formats.

Percentages

Use caution when defining an edit mask for a percentage. When you enter a number in a column with a percent edit mask and tab off the column, PowerBuilder divides the number by 100 and stores the result in the buffer. For example, if you enter 23, PowerBuilder passes .23 to the buffer. When you retrieve from the database, PowerBuilder multiplies the number by 100 and, if the mask is ##0%, displays 23%.

The datatype for the column must be numeric or decimal to handle the result of a division by 100. If the column has an integer datatype, a percentage entered as 333 is retrieved from the database as 300, and 33 is retrieved as 0.

If you use an edit mask with decimals, such as ##0.00%, the datatype must have enough decimal places to handle the division. For example, if you enter 33.33, the datatype for the column must have at least four decimal places because the result of the division is .3333. If the datatype has only three decimal places, the percentage is retrieved as 33.30.

Examples

The following table shows how the values 5, –5, and .5 display when different format masks are applied.

String display formats can have two sections. The first is required and contains the format for strings; the second is optional and specifies how to represent NULLs:

string-format;null-format

In a string format mask, each at-sign (@) represents a character in the string and all other characters represent themselves.

Example

This format mask:

[red](@@@) @@@-@@@@

displays the string 800YESCELT in red as:

YES-CELT

Date display formats can have two sections. The first is required and contains the format for dates; the second is optional and specifies how to represent NULLs:

date-format;null-format

Special characters

The following table shows characters that have special meaning in date display formats.

Colons, slashes, and spaces display as entered in the mask.

Date keywords

You can use the following keywords as date display formats when you want PowerBuilder to determine an appropriate format to use:

The format used is determined by the regional settings for date in the registry. Note that [Date] is not a valid display format.

Examples

The following table shows how the date Friday, January 30, 1998, displays when different format masks are applied.

Time display formats can have two sections. The first is required and contains the format for times; the second is optional and specifies how to represent NULLs:

time-format;null-format

Special characters

The following table shows characters that have special meaning in time display formats.

Colons, slashes, and spaces display as entered in the mask.

Time keyword

You can use the following keyword as a time display format to specify the format specified in the Windows control panel:

[Time]

Examples

The following table shows how the time 9:45:33:234567 PM displays when different format masks are applied.

Typically, you define edit styles in the Database painter, because edit styles are properties of the data itself. Once defined in the Database painter, the styles are used by default each time the column is placed in a DataWindow object.

An edit style you assign to a column in the Database painter is used by default when you place the column in a DataWindow object. You can override the edit style in the DataWindow painter by choosing another edit style from the extended attribute system tables or defining an ad hoc style for one specific column.

By default, columns use the Edit edit style, which displays data in an edit control. You can customize the appearance and behavior of the edit control by modifying a column's Edit edit style.

To do so, select Edit in the Style Type drop-down listand specify the properties for that style:

You can use the DropDownListBox edit style to have columns display as drop-down lists at runtime:

Typically, this edit style is used with code tables, where you can specify display values (which users see) and shorter data values (which are stored in the database).

In the DropDownListBox edit style, the display values of the code table display in the ListBox portion of the DropDownListBox. The data values are the values that are put in the DataWindow buffer (and sent to the database when an Update is issued) when the user selects an item in the ListBox portion of the drop-down list.

In the preceding example, when users see the value Business Services, the corresponding data value could be 200.

At runtime

You can define and modify a code table for a column in a script code by using the SetValue method at runtime. To obtain the value of a column at runtime, use the GetValue method. To clear the code table of values, use the ClearValues method.

For more about code tables, see Defining a code table.

If a column can take only one of two (or perhaps three) values, you might want to display the column as a check box; users can select or clear the check box to specify a value. In the following entry from a DataWindow object, users can simply check or clear a box to indicate whether an employee has health insurance:

What happens

The value you enter in the Text box becomes the display value, and values entered for On, Off, and Other become the data values.

When users check or clear the check box at runtime, PowerBuilder enters the appropriate data value in its buffer. When the Update method is called, PowerBuilder sends the corresponding data values to the database.

Centering check boxes and text

You may find it useful to center check boxes used for columns of information. First make the text control used for the column header and the column control the same size and left aligned. Then you can center the check boxes and the column header.

If a column can take one of a small number of values, you might want to display the column as radio buttons:

What happens

Users select values by clicking a radio button. When the Update method is issued, the data values are sent to the database.

Sometimes users need to enter data that has a fixed format. For example, in North America phone numbers have a 3-digit area code, followed by three digits, followed by four digits. You can define an edit mask that specifies the format to make it easier for users to enter values:

Edit masks consist of special characters that determine what can be entered in the column. They can also contain punctuation characters to aid users.

For example, to make it easier for users to enter phone numbers in the proper format, specify this mask:

(###) ###-####

At runtime, the punctuation characters display in the box and the cursor jumps over them as the user types:

Special characters and keywords

Most edit masks use the same special characters as display formats, and there are special considerations for using numeric, string, date, and time masks. For information, see Defining display formats.

The special characters you can use in string edit masks are different from those you can use in string display formats.

If you use the "#" or "a" special characters in a mask, Unicode characters, spaces, and other characters that are not alphanumeric do not display.

Keyboard behavior

Note the following about how certain keystrokes behave in edit masks:

Also, note certain behavior in Date edit masks:

Entering zero for the day or month causes the next valid date to be entered. For example, if the edit mask is DD/MM/YY, typing 00/11/01 results in 01/11/01. You can override this behavior in the development environment by adding the following lines to your PB.INI file:

[Edit Mask Behaviors]AutocompleteDates=no

For deployed applications, the date is completed automatically unless you provide a file called PB.INI in the same directory as the executable file that contains these lines. Note that this section must be in a file called PB.INI. Adding the section to a different INI file shipped with the application will have no effect.

You cannot use a partial mask, such as dd or mmm, in a date edit mask. Any mask that does not include any characters representing the year will be replaced by a mask that does.

The strings 00/00/00 or 00/00/0000 are interpreted as the NULL value for the column.

Using the Mask pop-up menu

Click the button to the right of the Mask box on the Mask property page to display a list that contains complete masks that you can click to add to the mask box, as well as special characters that you can use to construct your own mask. For example, the menu for a Date edit mask contains complete masks such as mm/dd/yy and dd/mmm/yyyy. It also has components such as dd and jjj (for a Julian day). You might use these to construct a mask like dd-mm-yy, typing in the hyphens as separators.

Using masks with "as is" characters

You can define a mask that contains "as is" characters that always appear in the control or column. For example, you might define a numeric mask such as Rs0000.00 to represent Indian rupees in a currency column.

However, you cannot enter a minus sign to represent negative numbers in a mask that contains "as is" characters, and the # special character is treated as a 0 character. As a result, if you specify a mask such as ###,##0.00EUR, a value such as 45,000 Euros would display with a leading zero: 045,000.00EUR. Note that you must always specify a mask that has enough characters to display all possible data values. If the mask does not have enough characters, for example if the mask is #,##0.00 and the value is 45000, the result is unpredictable.

The preferred method of creating a currency editmask is to use the predefined [currency] - International mask. You can change the number in parentheses, which is the number of characters in the mask including two decimal places. When you use this mask, PowerBuilder uses the currency symbol and format defined in the regional settings section of the Windows control panel. You can enter negative values in a column that uses a currency mask.

Using spin controls

You can define an edit mask as a spin control, a box that contains up and down arrows that users can click to cycle through fixed values. For example, you can set up a code table that provides the valid entries in a column; users simply click an arrow to select an entry. Used this way, a spin control works like a drop-down list that displays one value at a time:

For more about code tables, see Defining a code table.

Using a drop-down calendar

You can use a drop-down calendar option on any DataWindow column with an EditMask edit style and a Date, DateTime, or TimeStamp datatype. The DDCalendar EditMask property allows for separate selections of the calendar month, year, and date. This option can be set in a check box on the Edit page of the DataWindow painter Properties view when a column with the EditMask edit style is selected. It can also be set in code, as in this example for the birth_date column:

dw1.Modify("birth_date.EditMask.DDCalendar='Yes'")

Sometimes another data source determines which data is valid for a column.

Consider this situation: the Department table includes two columns, Dept_id and Dept_name, to record your company's departments. The Employee table records your employees. The Department column in the Employee table can have any of the values in the Dept_id column in the Department table.

As new departments are added to your company, you want the DataWindow object containing the Employee table to automatically provide the new departments as choices when users enter values in the Department column.

In situations such as these, you can specify the DropDownDataWindow edit style for a column: it is populated from another DataWindow object. When users go to the column, the contents of the DropDownDataWindow display, showing the latest data:

What happens

At runtime, when data is retrieved into the DataWindow object, the column whose edit style is DropDownDataWindow will itself be populated as data is retrieved into the DataWindow object serving as the drop-down DataWindow object.

When the user goes to the column and drops it down, the contents of the drop-down DataWindow object display. When the user selects a display value, the corresponding data value is stored in the DataWindow buffer and is stored in the database when an Update is issued.

You can use the RichText edit style to display column data in a rich text format, and to use different fonts and colors in the same data field.

Columns that you format with the RichText edit style require considerably more storage space than columns with plain text edit styles. Therefore you should set a minimum of 1 KB for the column width. Otherwise, you can use the RichText edit style with columns that have large text datatypes.

By default, whenever a column with the RichText edit style is edited in the Preview view or at runtime, a font toolbar displays. The font toolbar disappears when the column loses focus. The following picture shows the default font toolbar available for columns with the RichText edit style:

You can modify the RichTextToolbarActivation constant on a DataWindow control to display the font toolbar whenever a DataWindow object containing columns with the RichText edit style has focus—whether or not this type of column is selected. You can also modify the constant so that the font toolbar never appears.

For more information, see the section called “RichTextToolbarActivation” in DataWindow Reference.

The RichText edit style is not available for columns in a DataWindow object with the Graph, OLE, or RichText presentation styles.

The InkEdit edit style is designed for use on a Tablet PC and provides the ability to capture ink input from users of Tablet PCs.

You can specify InkEdit as a style type on the Edit page in the Properties view for columns. When the column gets focus, an InkEdit control displays so that the user can enter text with the stylus or mouse. The text is recognized and displayed, then sent back to the database when the column loses focus.

The InkEdit edit style is fully functional on Tablet PCs. On other computers, it behaves like the Edit edit style.

For more information about ink controls and the Tablet PC, and to download the Tablet PC SDK, go to the Microsoft Tablet PC website at http://msdn.microsoft.com/en-us/library/ms950406.aspx.

You can define a code table as a property of the following column edit styles:

The steps to specify the code table property for each edit style are similar: you begin by defining a new edit style in the Database painter. Once you select an edit style, use the specific procedure that follows to define the code table property.

For how to create an edit style, see About edit styles.

When data is retrieved into a DataWindow object column with a code table, processing begins at the top of the data value column. If the data matches a data value, the corresponding display value displays. If there is no match, the actual value displays.

Consider the example in the following table.

If the data is MA or ma, the corresponding display value (Massachusetts) displays. If the data is Ma, there is no match, so Ma displays.

If the code table is in a DropDownListBox edit style, and if the column has a code table that contains duplicate display values, then each value displays only once. Therefore, if this code table is defined for a column in a DataWindow object that has a DropDownListBox edit style, Massachusetts and Rhode Island display in the ListBox portion of the DropDownListBox.

When users enter data into a column in a DataWindow object, processing begins at the top of the display value column of the associated code table.

If the data matches a display value, the corresponding data value is put in the internal buffer. For each display value, the first data value is used. Using the sample code table, if the user enters Massachusetts, ma, or Mass, the data value is MA.

You can specify that only the values in the code table are acceptable:

Validation rules are criteria that a DataWindow object uses to validate data entered into a column by users. They are PowerBuilder-specific and therefore not enforced by the DBMS.

Validation rules assigned in the Database painter are used by default when you place columns in a DataWindow object. You can override the default rules in the DataWindow painter.

A validation rule is an expression that evaluates to either "true" or "false". If the expression evaluates to "true" for an entry into a column, PowerBuilder accepts the entry. If the expression evaluates to "false", the entry is not accepted and the ItemError event is triggered. By default, PowerBuilder displays a message box to the user. You can customize the message displayed when a value is rejected.

You can also code the ItemError event to cause different processing to happen.

For more information, see the section called “Using DataWindow Objects” in DataWindow Programmers Guide.

This section describes the ways you can manipulate validation rules in the Database painter.

@col > 0

'Item ~'' + @Col + '~' does not pass validation test.'

'Item ~'' + ColumnName + '~' does not pass validation test.'

Please enter a salary greater than $10,000.

Validation rules you assign to a column in the Database painter are used by default when you place the column in a DataWindow object. You can override the validation rule in the DataWindow painter by defining an ad hoc rule for one specific column.

Integer(GetText( )) > 0

Match(GetText( ),"^[0-9]+$") AND
Real(GetText( )) < Full_Price

(Number(GetText( )) >= If(price >= 1000, .10, .04))
AND
(Number(GetText( )) <= If(price >= 1000, .20, .09))

"Price is " + if(price >= 1000,
"greater than or equal to","less than") +
" 1000. Commission must be between " +\
If(price >= 1000,".10", ".04") + " and " +
If(price >= 1000, ".20.", ".09.")