Adding controls to a DataWindow object

This section describes adding controls to enhance your DataWindow object.

Adding columns to a DataWindow object

You can add columns that are included in the data source to
a DataWindow object. When you first create a DataWindow object, each of the columns
in the data source is automatically placed in the DataWindow object. Typically,
you would add a column to restore one that you had deleted from
the DataWindow object, or to display the column more than once in the DataWindow object.

Adding columns not previously retrieved to the data
source To specify that you want to retrieve a column not previously
retrieved (that is, add a column to the data source), you must modify
the data source.

See “Modifying the data source
of a DataWindow object”.

To add a column from the data source to a DataWindow object:

1. Select Insert>Control>Column
   from the menu bar.

2. Click where you want to place the column.

   The Select Column dialog box displays listing all columns
   included in the data source of the DataWindow object.

3. Select the column and click OK.

Adding text to a DataWindow object

When PowerBuilder generates a basic DataWindow object from a presentation style
and data source, it places columns and their headings in the workspace. You
can add text anywhere you want to make the DataWindow object easier to understand.

To add text to a DataWindow object:

1. Select Insert>Control>Text
   from the menu bar.

2. Click where you want the text.

   PowerBuilder places the text control in the Design view and
   displays the word text
   .

3. Type the text you want.

4. (Optional) Change the font, size, style, and alignment
   for the text using the StyleBar.

About the default font and style

When you place text in a DataWindow object, PowerBuilder uses the font
and style (such as boldface) defined for the application’s
text in the Application painter. You can override the text properties
for any text in a DataWindow object.

For more about changing the application’s
default text font and style, see Chapter 2, “Working with Applications”.

Adding drawing controlsto a DataWindow object

You can add the following drawing controls to a DataWindow object to enhance
its appearance:

• Rectangle
• RoundRectangle
• Line
• Oval

To place a drawing control in a DataWindow object:

1. Select the drawing control from the Insert>Control
   menu.

2. Click where you want the control to display.

3. Resize or move the drawing control as needed.

4. Use the drawing control’s Properties
   view to change its properties as needed.

   For example, you might want to specify a fill color for a
   rectangle or thickness for a line.

Adding a groupbox to a DataWindow object

To visually enhance the layout of a DataWindow object, you can add
a groupbox. The groupbox is a static frame used to group and label
a set of controls in a DataWindow object. The following
example shows two groupboxes in a report (nonupdatable DataWindow
object). The Address groupbox groups address information and the
Phone/Fax groupbox groups telephone numbers:

To add a groupbox to a DataWindow object:

1. Select Insert>Control>Group
   Box from the menu bar and click in the Design view.

2. With the groupbox selected, type the text to display
   in the frame.

3. Move and resize the groupbox as appropriate.

Adding pictures to a DataWindow object

You can place pictures, such as your company logo, in a DataWindow object to
enhance its appearance. If you place a picture in the header, summary,
or footer band of the DataWindow object, the picture displays each time
the contents of that band displays. If you place the picture in
the detail band of the DataWindow object, it displays in each row.

To place a picture in a DataWindow object:

1. Select Insert>Control>Picture
   from the menu bar.

2. Click where you want the picture to display.

   The Select Picture dialog box displays.

3. Use the Browse button to find the file or enter
   a filename in the File Name box. Then click Open.

   The picture must be a bitmap (BMP), runlength-encoded (RLE),
   Windows metafile (WMF), CompuServe Graphics Interchange (GIF), or
   JPEG file.

4. Display the popup menu and select Original Size
   to display the bitmap in its original size.

   You can use the mouse to change the size of the bitmap in
   the DataWindow painter.

5. Click the Invert Image checkbox on the General
   page of the Properties view to display the picture with its colors
   inverted.

Tips for using pictures

To display a different picture for each row of data, retrieve
a column containing picture filenames from the database.

For more information, see “Specifying additional properties
for character columns”.

To compute a picture name during execution, use the Bitmap
function in the expression defining a computed field. If you change
the bitmap in the Picture control in a DataWindow object, you need to reset
the original size property. The property automatically reverts to
the default setting when you change the bitmap.

To use a picture to indicate that a row has focus during execution,
use the SetRowFocusIndicator function.

For information about these functions, see
the PowerScript Reference
.

Adding computed fields toa DataWindow object

You can use computed fields in any
band of the DataWindow object. Typical uses include:

• Calculations
  based on column data that change for each retrieved row For example, if you are retrieving yearly salary, you can
  define a computed field in the detail band that displays monthly
  salary (defined as Salary / 12).
• Summary statistics of the data For example, if you have a grouped DataWindow object, you can use
  a computed field to calculate the totals of a column for each group.
• Concatenated fields For example, if you are retrieving first name and last name,
  you can define a computed field that concatenates the values so they
  appear with only one space between them (defined as Fname + ”
  ” + Lname).
• System information For example, you can place the current date and time in a DataWindow object’s
  header by using computed fields (defined as Today() and Now()).

Computed columns versus computed fields

When creating a DataWindow object, you can define computed columns
and computed fields as follows:

• In
  the Select painter, you can define computed columns when
  you are defining the SELECT statement that will be used to retrieve
  data into the DataWindow object
• In the DataWindow painter, you can define computed
  fields after you have defined the SELECT statement (or
  other data source)

The difference between the two ways

When you define the computed column in the Select painter,
the value is calculated by the DBMS when the data is retrieved.
The computed column’s value does not change until data
has been updated and retrieved again.

When you define the computed field in the DataWindow painter, the
value of the column is calculated in the DataWindow object after the data
has been retrieved. The value changes dynamically as the data in
the DataWindow object changes.

Example

Consider these columns in a DataWindow object. Cost is computed as
Quantity * Price:

Part #	Quantity	Price	Cost
101	100	1.25	125.00

If Cost is defined as a computed column in the Select painter,
the SELECT statement is as follows:

If the user changes the price of part 101 in the DataWindow object in
this scenario, the cost does not change in the DataWindow object until
the database is updated and the data is retrieved again. So the
user gets this display, with the incorrect cost:

Part #	Quantity	Price	Cost
101	100	2.50	125.00

On the other hand, if Cost is defined as a computed field
in the DataWindow object, the SELECT statement looks like the following
statement. It does not have a computed column:

The computed field is defined in the DataWindow object as Quantity * Price.

In this scenario, if the user changes the price of part 101
in the DataWindow object, the cost changes immediately:

Part #	Quantity	Price	Cost
101	100	2.50	250.00

Recommendation

If you want your DBMS to do the calculations on the server
before bringing data down and you don’t care about dynamically
updating the computed values, define the computed column as part
of the SELECT statement.

If you want computed values to change dynamically, define
your computed fields in the DataWindow painter Design view, as described
next.

Defining a computed field in the DataWindow painter Design
view

To define a computed field in the DataWindow painter Design
view:

1. Select Insert>Control>Computed
   Field from the menu bar.

2. Click where you want the computed field.

   If the calculation is to be based on column data that changes
   for each row, make sure you place the computed field in the detail
   band.

3. The Modify Expression dialog box displays listing:
   • DataWindow expression functions you can use in
     the computed field
   • The columns in the DataWindow object
   • Operators and parentheses
     
4. Enter the expression that defines the computed
   field (see below).

5. (Optional) Click Verify to test the expression.

   PowerBuilder analyzes the expression.

6. Click OK.

Entering the expression

You can enter any valid DataWindow expression when defining a computed field.
You can paste operators, columns, and DataWindow expression functions into the expression
from information in the Modify Expression dialog box.

DataWindow expressions The expression you are entering is a DataWindow expression; it is not
a SQL expression processed by the DBMS. So the expression follows
the rules for DataWindow expressions.

For complete information about DataWindow expressions,
see the DataWindow Reference
.

You can use any non-object-level function (built-in or user-defined)
in an expression.

You can use the + operator to concatenate strings.

Referring to next and previous rows

You can refer to other rows in a computed field. This is particularly
useful in n-up DataWindow objects when you want to refer to another row
in the detail band.

Use this syntax:

where x
is an integer. 0 refers to the
current row (or first row in the detail band), 1 refers to the next
row, -1 refers to the previous row, and so on.

Examples

Here are some examples of computed fields and columns:

To display	Enter this expression	In this band
Current date at top of each page	Today() (a built-in DataWindow expression function)	Header
Current time at top of each page	Now()	Header
Current page at bottom of each page	Page()	Footer
Total page count at bottom of each page	PageCount()	Footer
Concatenation of Fname and Lname columns for each row	Fname + ” ” + Lname	Detail
Monthly salary if Salary column contains annual salary	Salary / 12	Detail
Four asterisks if the value of the Salary column is greater than $50,000	IF(Salary> 50000, “****”, “”)	Detail
Average salary of all retrieved rows	Avg(Salary)	Summary
Count of retrieved rows, assuming each row contains a value for EmpID	Count(EmpID)	Summary

For more information

For complete information about the functions you can use in
computed fields in the DataWindow painter, see the DataWindow
Reference
.

A shortcut for doing summary statistics

PowerBuilder provides a quick way to create computed fields
that summarize values in the detail band.

To summarize values:

1. Select one or more columns in the DataWindow object’s
   detail band.

2. Do one of the following:

   To place this computed field	Do this
   Average	Select Insert>Control>Average from the menu bar or click the button in the Controls dropdown toolbar
   Count	Select Insert>Control>Count from the menu bar or click the button in the Controls dropdown toolbar
   Sum	Select Insert>Control>Sum from the menu bar or click the button in the Controls dropdown toolbar

   PowerBuilder places a computed field in the summary band or
   in the group trailer band if the DataWindow object is grouped. The band
   is resized automatically to hold the computed field. If there is
   already a computed field that matches the one being generated, it
   is skipped.

Adding custom buttons that place computed fields You can add buttons to the PainterBar in the DataWindow painter that
place computed fields using any of the aggregate functions, such
as Max, Min, and Median.

To customize the PainterBar with custom buttons
for placing computed fields:

1. Place the mouse pointer over the PainterBar and
   select Customize from the popup menu.

   The Customize dialog box displays.

2. Click Custom in the Select palette group to display
   the set of custom buttons.

3. Drag a custom button into the Current toolbar group
   and release it.

   The Toolbar Item Command dialog box displays.

4. Click the Function button.

   The Function For Toolbar dialog box displays.

5. Select a function and click OK.

   You return to the Toolbar Item Command dialog box.

6. Specify text that displays for the button and click
   OK.

   PowerBuilder places the new button in the PainterBar. You can
   click it to add a computed field to your DataWindow object the same way
   you use the built-in Sum button.

A shortcut for placing page numbers and date

You can click buttons in the PainterBar to place a computed
field for the current page number and date anywhere in the DataWindow object:

To place this computed field	Do this
‘Page ‘ + page() + ‘ of ‘ + pageCount()	Click the Page button in the Controls dropdown toolbar or select Insert>Control>Page n of n from the menu bar
today()	Click the Today button in the Controls dropdown toolbar or select Insert>Control>Today() from the menu bar

Adding buttons to a DataWindow object

Buttons make it easy to provide command button actions in
a DataWindow object. No coding is required. Furthermore, the use of buttons
(Button controls) in the DataWindow object (rather than CommandButton
controls in a window) ensures that the actions appropriate to the
DataWindow object are included in the object.

The Button control is a command (or Picture) style button
that can be placed in a DataWindow object. When clicked at execution time,
the button activates either a built-in or user-supplied action.

The following example shows a button placed in a report (a
nonupdatable DataWindow object). Clicking the button brings up the
Filter dialog, where users can specify a filter to be applied
to the currently retrieved data:

To add a button to a DataWindow object:

1. Select Insert>Control>Button
   from the menu bar.

2. Click where you want the button to display.

   You may find it useful to put a Delete button or an Insert
   button in the detail band. Clicking a Delete button in the detail
   band will delete the row next to the button clicked. Clicking an
   Insert button in the detail band will insert a row following the
   row containing the button clicked.

   Be careful when putting buttons in the detail band Buttons in the detail band repeat for every row of data (often
   not desirable). During retrieval the buttons in the detail band
   are not visible. So, for example, a Cancel button in the detail
   band would be unavailable when needed.

3. With the button still selected, type the text to display
   on the button.

4. Display the button’s Properties view (General
   page).

5. Select the action you want to assign to the button from
   the Action dropdown listbox.

   For information about actions, see “Actions assignable to buttons
   in DataWindow objects”.

6. If you want to add a picture to the button, select the
   Action Default Picture checkbox or enter the name of the Picture
   file to display on the button.

7. If you want to suppress event processing when the button
   is clicked at execution time, select the Suppress Event checkbox.

   When this option has been selected for the button and the
   button is clicked at execution time, only the action assigned to
   the button and the Clicked event are executed. The ButtonClicking
   and the ButtonClicked events are not triggered.

8. Click OK.

What happens if Suppress Event is off

If Suppress Event is off and the button is clicked, the Clicked
and ButtonClicking events are fired. Code in the ButtonClicking
event (if any) is executed. Note that the Clicked event is fired
before the ButtonClicking event.

• If the return
  code from the ButtonClicking event is 0, the action assigned to
  the button is executed and then the ButtonClicked event is fired.
• If the return code from the ButtonClicking event
  is 1, neither the action assigned to the button nor the ButtonClicked
  event are executed.

Controlling the display of buttons in print preview
and on printed output

You can choose whether to display buttons in print preview
or in printed output. You control this in the Properties view for
the DataWindow object (not the one for the button).

To control the display of buttons in a DataWindow object in
print preview and on printed output:

1. Display the DataWindow object’s Properties
   view with the Print Specification page on top.

2. Select the Display Buttons – Print checkbox to
   have buttons included in the printed output when the DataWindow object is
   printed.

3. Select the Display Buttons – Print Preview checkbox
   to have the buttons display on the screen when viewing the DataWindow object in
   print preview.

Actions assignable to buttonsin DataWindow objects

These are the actions you can assign to a button in a DataWindow object:

Action	What it does	Value	Meaning of action return code from ButtonClicked event
User Defined(default)	Allows the developer to program the ButtonClicked event with no intervening action occurring	0	The return code from the user’s coded event script
Retrieve (Yield)	Retrieves rows from the database. Before retrieval occurs, the option to yield is turned on; this will allow the Cancel action to take effect during a long retrieve	1	Number of rows retrieved-1 if retrieve fails
Retrieve	Retrieves rows from the database. The option to yield is not automatically turned on	2	Number of rows retrieved-1 if retrieve fails
Cancel	Cancels a retrieval that has been started with the option to yield	3	0
Page Next	Scrolls to the next page	4	The row displayed at the top of the DataWindow control when the scrolling is complete or attempts to go past the first row-1 if an error occurs
Page Prior	Scrolls to the prior page	5	The row displayed at the top of the DataWindow control when the scrolling is complete or attempts to go past the first row-1 if an error occurs
Page First	Scrolls to the first page	6	1 if successful-1 if an error occurs
Page Last	Scrolls to the last page	7	The row displayed at the top of the DataWindow control when the scrolling is complete or attempts to go past the first row-1 if an error occurs
Sort	Displays Sort dialog and sorts as specified	8	1 if successful-1 if an error occurs
Filter	Displays Filter dialog and filters as specified	9	Number of rows filteredNumber < 0 if an error occurs
Delete Row	If button is in detail band, deletes row associated with button; otherwise, deletes the current row	10	1 if successful-1 if an error occurs
Append Row	Inserts row at the end	11	Row number of newly inserted row
Insert Row	If button is in detail band, inserts row using row number associated with the button; otherwise, inserts row using the current row	12	Row number of newly inserted row
Update	Saves changes to the database. If the update is successful, a Commit will be issued; if the update fails, a Rollback will be issued	13	1 if successful-1 if an error occurs
Save Rows As	Displays Save As dialog and saves rows in the format specified	14	Number of rows filteredNumber < 0 if an error occurs
Print	Prints one copy of the DataWindow object	15	0
Preview	Toggles between preview and print preview	16	0
Preview With Rulers	Toggles between rulers on and off	17	0
Query Mode	Toggles between query mode on and off	18	0
Query Sort	Allows user to specify sorting criteria (forces query mode on)	19	0
Query Clear	Removes the WHERE clause from a query (if one was defined)	20	0

Adding graphs to a DataWindow object

Graphs are one of the best ways to present information. For
example, if your application displays sales information over the
course of a year, you can easily build a graph in a DataWindow object to
display the information visually.

PowerBuilder offers many types of graphs and provides you with
the ability to control the appearance of a graph to best meet your
application’s needs.

For information on using graphs, see Chapter 20, “Working with Graphs “.

Adding OLE controls toa DataWindow object

On Windows, you can add the following to a DataWindow object:

• A column that contains a database binary large object
  (a blob object) using OLE 2.0
• OLE 2.0 objects

For information on using OLE in a DataWindow object,
see Chapter 23, “Using OLE in a DataWindow Object “.

Adding reports to a DataWindow object

You can nest reports (nonupdatable DataWindow objects) in
a DataWindow object.

For information on nesting reports, see Chapter 19, “Using Nested Reports “.

---