Powerbuilder DataWindow Script Techniques

Powerbuilder DataWindow Script Techniques

How do I set a property for an entire column?

How many rows selected?

How do I check the name of the DataWindow?

Putting a Help button on a DataWindow dialog box

DataWindow dialog boxes, such as the ones which may come up for Sort( ) and Filter( ) after the Setxxx is passed a string variable with a Null value, can have a Help button appear. For details see 14-8 of the Mastering DataWindows 6.0 book.

How do I dynamically create and destroy bit maps in a datawindow?

You can dynamically create and destroy objects (such as text, bitmaps, and graphic objects) in a DataWindow object using CREATE and DESTROY requests as part of the Modify() function

This statement adds a bitmap named Logo to the header area for group level 1 in the DataWindow object:

The following statement destroys a bitmap object named logo:

Smart Filter (way cool)

Outside the datawindow, a sle exists. Whatever the user enters into this field will filter the datawindow . Any columns which do not include the entered string are excluded.

1. Create a computed column on the datawindow called c_key. This will contain a string of all the columns you wish to include in the filter. Any numbers or dates need to be converted to strings.

2. Do whatever you need to do to make this computed column not visible to the user

3.Add a single line edit with the following user event mapped to pbm_keyup

In a datawindow, how do I have the “Enter” key work like a tab?

You will need to declare a user event

ue_ReturnAsTab pbm_dwnprocessenter

In the ue_returnastab event code;

This is now part of the pfd_u_dw and the functionality is turned on by adding the following to the constructor event:

ib_enterastab = True

SetItem is too slow

When you have a need for speed:

Traditional method

Faster method

How do I make a datawindow read only?

Can you create an html form for a datawindow and datastore?

Yes, by using the GenerateHtmlForm function.

How do I coordinate the update of multiple DataWindows?

Update( ) will accept two parameters, AcceptText and ResetFlags. The default is True, True

Accept text True means that the edit control is sent to the DataWindow. DataWindow validation is performed.

ResetFlag = False will not reset the status flags. This must be done with the ResetUpdate( ) function.

Example:

When a data entry fills an entire field, how do I make the cursor skip to the next field?

<!–[if !supportLists]–>1. Auto Skip property of Edit Mask

When this box is checked on the Mask property sheet of the EditMask control, the user’s cursor will automatically skip to the next control in the tabbing order after entering all the characters allowed by the mask. If this box is not checked, the cursor will not skip automatically to the next control.

The AutoSkip property is a boolean value. This example enables automatic skipping to the next control.

em_1.AutoSkip = TRUE

2. The EditChanged Event

I get the following Run Time Error: A PowerBuilder message box “Datawindow Error”, “Value Required For This Item”

I got this from doing a describe (Evaluate (‘LookUpDisplay(pos_cd)’,1)). In my case the datawindow column name in the Column Specification panel shows that the actual column name is different than the DB Name.

I have a hh:mm editmask on a a datetime field, when I update the time, it sets the Date part to “01/01/2000”

You need to find the correct date value through some other means, parse the new time from the data argument and glue the two together.

What is the difference between SELECT and PBSELECT

PBSELECT is generic database independent SQL which is stored in the DataWindow.The actual outer join SQL for native Sybase connection will differ from an ODBC Sybase connection.

About DataWindow properties

All DataWindow object properties are stored as strings, but you can represent the values in more than one way when assigning.For example, the following statements are equivelent

As is

How do I make a column not modify in script.

How do I dynamically change a column’s tab order?

Modify

Dot notation

Evaluate( )

The evaluate( ) function is available for use with Describe( ).Evaluate( ) lets you evaluate DataWindow expressions within a script using data in the DataWindow.It has the following syntax:

Evaluate(‘Expression’, Rownumber)

The expression usually includes DataWindow painter functions that cannot be called in a script.The following example returns the display value for the dept_id column for the current row:

LookupDisplay() is a common use of Evaluate()

Find( )

The following snippet does a case insensitive Find( )

RowsDiscard()

Sort()

You can also sort by column number instead of column name.

To sort a DataWindow object with groups, call GroupCalc( ) after you call Sort( ). GroupCalc forces the DataWindow engine to recalculate the breaks in the grouping levels after you have added or modified rows in a DataWindow.

To have a “Specify Sort Columns” dialog box appear, set a string variable to NULL, and pass it as the argument to SetSort( ).A subsequent Sort( ) will open the dialog box.

How do I change a column’s tab order is script?

What if AcceptText() causes a validation error?

It will return a -1

Working with DDDWProperties

The following example returns the display value for the dept_id column for the current row:

Another Example:

One more example

In the ItemChanged event, I want to Reject the value of a column with a DDDW and allow the focus to change.But Return 2 does not seem to work.

Return 2 is changing the column item back to the original value, but the Display Item does not change.The DataWindow has rejected the value, but it is still displaying the changed value.Here is script which deals with this problem:

ItemChanged event:

Ue_Refresh Display event:

How do I dynamically change a dddw properties?

Peter Louis wrote an interesting user object for holding a simple datawindow with two colums, one for string, the other for a long.This function sets the dw and its associated dddw for either the String column or the long column.The column not being used is turned invisible.

Dot Notation

What are the DDDW properties?

• AllowEdit
• AutoHScroll
• Case
• DataColumn
• DisplayColumn
• HScrollBar
• HSplitScroll
• Limit
• Line
• Name
• NilIsNull
• PercentWidth
• Required
• ShowList
• UseAsBorder

RowsCopy

RowsCopy will copy the contents of one datawindow/datastore to another. The RowStatus flags for each row will be NewModified!

dwsource.RowsCopy(startrow, endrow, copybuffer, dwtarget, beforerow, targetbuffer )

Example:

RowsMove

The rows moved to the target DataWindow have the status NewModified!

How do I dynamically change a group property?

ll_color = RGB(200, 200, 500)

dw_1.Modify(“DataWindow.Header.2.Color=” + String(ll_color))

dw_1.Modify(“DataWindow.Trailer.2.Height=500”)

How do I expand hidden rows in a group?

In this example, a DataWindow hides the detail rows and shows only the group summaries and headers.If a checkbox “Explode Details” is clicked, the following script is fired:

How do I get the value of the items in a group trailer or header?

The clicked event gives the following arguments: x, y, row, and dwo.Because clicking on a trailer returns a row of 0, you can not directly do a GetItem().Instead, after you interrogate the dwo to determine that you are in the trailer, use the function GetBandAtPointer().Here are the results:

Returns a string that names the band in which the pointer is located, followed by a tab character and the number of the row associated with the band (see the table in Usage). Returns the empty string (“”) if an error occurs.

If you have the last row in a group, and you need to find the first row in the group, cycle through the rows with FindGroupChange().

What is FindGroupChange(long, integer)

long dwcontrol.FindGroupChange ( long row, integer level )

The first argument is the row you wish to start your search (use 0 to start at the very beginning).The second argument is the group number.The return value is the row where the group changes.

Here is an example from PB Help:

This statement searches for the first break in group 2 in dw_regions. The search begins in row 5:

dw_regions.FindGroupChange(5, 2)

Example of finding the range of rows for a group

If the user clicks on a group trailer band, the following script will call the function to calculate which rows that group begins and ends, and then expands them.Note: the detail band begins with a height of zero and a calculated column called EXPAND_LEVEL with the value of empty string.This column is hidden behind another column and has its autosize attribute set to TRUE.EXPAND_LEVEL begins with a height of zero.

This is called from the DataWindow clicked event if the user clicks on a group trailer:

Looping through a group

My Groups seem to be working fine, except they are not sorted.

This was a killer problem I faced in Dec ’99 at QG.I wanted to have my groups appear in YEAR, Month order, but they were sometimes jumbled.My SQL needed an order by clause.Note, when you create or edit a group (Rows>Create Group) there is a sort by tab.Beware: when you drag the columns you want to sort by, you may actually drop the SUM(<col> FOR GROUP 1).

GetSelectedRow(long)

Reports the number of the next highlighted row after a specified row in a DataWindow control or DataStore object.Argument is the row after which you want to search for the next selected row. Specify 0 to begin searching at the first row

Returns the number of the first row that is selected after row indwcontrol. Returns 0 if no row is selected after the specified row.

Print Events

• PrintStart
• PrintPage
• PrintEnd

In the PrintPage event, a RETURN 1 will skip the next page to be printed.

How do I get a report to print landscape?

How do I print only a range of pages?

How do I print several DataWindows in the same print job?

Open a print job and use PrintDataWindow( ) function for each DataWindow.

How do I cancel a print job?

Some of my text is in red (or some other color other than black) on the display, but this red text does not appear when I print the report.

Check the column background.If it is Transparent, change it to white.

Disabling a button on a datawindow

Datawindow buttons do not have a Disabled property. So showing them as “disabled” is not clear cut.Here is one way

In the datawindow painter, create a static text control of the same size as the button

• Set the border to outline
• Set its text to ”…”
• Text should be bold and colored dark gray
• Move it directly underneath the button

Now hide the button in script to disable it

How do I copy a DataWindow value to a variable?

Also GetItemString and GetItemDate( ) GetItemDateTime( ), GetItemDecimal( ), GetItemTime( ) for other datatypes.

OR

How do I update the datawindow with some value stored in a variable?

Note, the variable must be of the same data type as the column in the database.Returns 1 for success-1 for error.

Extracting result set from a datawindow

The ‘datawindow.data’ attribute of a DataWindow represents the result set from the retrieve. By using the Describe() function you will get a string value containing the rows and columns of the results. Within the string the newline character (~n) separates each row, and the tab character (~t) separates each column within the row.

Very long strings inserted into a column are being truncated.

I was inserting a comment string into a computed field.Very long comments were being truncated.Discovered that the length of the column (Datawindow painter, Column specification tab) was set to 1 and PB was truncating after 512 characters.Need to up the column length to 32767.

How do I get a value from the footer or summary band?

Just treat it as a column on every row.Note, there must be at least one row.

How do I set data?

How do I set a column for all the rows to the same value?

If I want to set all row’s STRM_ID column to the value 1234. lsa_default is array with the same size as the number of rows. Eche array element has a value of 1234.

How do I update the static text in the header of a datawindow?

How do I get data?

The Object property of the DataWindow control lets you specify expressions that refer directly to the data of the DataWindow object in the control. This direct data manipulation allows you to access small and large amounts of data in a single statement, without calling methods.

There are several variations of data expression syntax, divided into three groups. This section summarizes these syntaxes. The syntaxes are described in detail later in this chapter.

Data in columns or computed fields when you know the name

One or all items(if rownum is absent, include either buffer or datasource)

dwcontrol.Object.columnname {.buffer } {.datasource } { [ rownum ] }

Returns a single value (for a specific row number) or an array of values (whenrownum is omitted) from the column.

Selected items

dwcontrol.Object.columnname {.Primary }{.datasource }.Selected

Returns an array of values from the column with an array element for each selected row.

Populating an array witha column’s valuesfor all rows

From a script written by Dan M.

Range of items

dwcontrol.Object.columnname {.buffer } {.datasource } [ startrownum,endrownum ]

Returns an array of values from the column with an array element for each row in the range.

Data in numbered columns

Single items

dwcontrol.Object.Data {.buffer } {.datasource } [ rownum, colnum ]

Returns a single item whose data type is the data type of the column.

Blocks of datainvolving a range of rows and columns

dwcontrol.Object.Data {.buffer } {.datasource } [ startrownum, startcolnum, endrownum, endcolnum ]

Returns an array of structures or user objects. The structure elements match the columns in the range. There is one array element for each row in the range.

Whole rows

Single row or all rows

dwcontrol.Object.Data {.buffer } {.datasource } { [ rownum ] }

Returns one structure or user object (for a single row) or an array of them (for all rows). The structure elements match the columns in the DataWindow object.

Selected rows

dwcontrol.Object.Data {.Primary } {.datasource } .Selected

Returns an array of structures or user objects. The structure elements match the columns in the DataWindow object. There is one array element for each selected row.

How do I make a DataWindow Read Only?

To use a DataWindow for reporting purposes only, set its ReadOnly attribute so users can not place focus on its columns.

Modify and Describe

Allows for the dynamic modification and access to the attributes of a datawindow or datastore.Direct access can often accomplish the same thing in a much more readable manner.

Modify and Describe lend themselves more to generic coding techniques as dot notation requires the programmer to hard code the column name.Modify( )and Describe( ) can use the column number.Some things cannot be accessed using dot notation.

For more information, look at the “PowerScript >Techniques” and the “DataBase Painter > Datawindow Expressions “sections.

Format

Examples

Direct Access (Dot Notation)

Tips on Building Modify and Describe Expressions

• DwSyntax Utility
• Examine Datawindow Export code and look for how similar expressions are built.

ItemError Event Return codes

How do I find the data in the underlying data(not the edit control)

If I am outside the ItemError Event, how would I find the column data for a particular row?

How do I get rid of multiple error messages?

If you code a validation script in the ItemChanged event that displays a MessageBox for invalid values, the default ItemError message box also appears.To prevent he user from seeing two error messages, Return 1.

If you are using the PFC, set the variable ib_itemerror_msg = FALSE.This will not display the default error message.

ItemChanged Event Return codes

Sample ItemChanged Event

In the ItemChanged event, how come changes to the data argument have no impact on what is saved in the column

Because DATA is a copy of what exists in the edit control, changing it does not change the edit control.

Okay, in the ItemChanged event, how do I force a new value using SetItem() into the primary buffer?

If you force a new item into the primary buffer for the affected column, the edit control overwrites this at the conclusion of the ItemChanged event.You need to post to a method which does the SetItem.

Retrieve()

• RetrieveStart
  • Return 1 to stop retrieval
  • Return 2 to retrieve without reset
• If there are already rows in the datawindow
  • RowFocusChanging (newrow = 0)
  • Resize (if a scrollbar I showing
• SQL Preview (Select statement)
• RetrieveRow (For each row retrieved)
  • Return 1 stops retrieval
• Resize (after a page if there is a vertical scroll bar)
• RowFocusChanging( if row # is not 1)
• RetrieveEnd

Update()

• UpdateStart
  • Return 1 to cancel the update
• SQLPreview for each changed row
  • Deletes first
  • Update & Inserts by row number
  • Return 1: Cancel current action
  • Return 2: Skip this SQL Statement

Mouse Scroll Wheel (other event)

The user can hold down control and then use the mouse scroll wheel to zoom in or out.This can be disabled with the code below.If you want to disable the scroll wheel all together, you would just remove “AND KeyDown(KeyControl!) ” from the code.

How do I append data to a datawindow

In the RetrieveStart event the return code must be 2

Return 2

Return Codes

0 – (Default) Continue.

1 – Do not perform the retrieval.

2 – Do not reset the rows and buffers before retrieving the data from the database.

When does the Error event fire?

TheErrorEvent occurs at execution time as the result of syntax errors in DataWindow property expressions and DataWindow data expressions.If you refer to a nonexistent object, misspell a property name or reference nonexistent data theErrorevent fires.

In the above example, the compiler examines only up to “dw_1.Object.”Column names and property names are not verified until execution. So if the column “emp_id” does not exist on the datawindow, the Error event is triggered.

Event arguments

• ErrorNumber – unsigned integer
• ErrorText – string
• ErrorWindowMenu – string
• ErrorObject – string
• ErrorScript – string
• ErrorLine – unsigned integer
• Action – ExceptionAction

Valid Action values are ExceptionFail!, ExceptionIgnore!, and ExceptionSubstituteValue!.

If the Action argument is ExceptionFail!, the SystemErrorevent fires.

Example script for changing the return value

Suppose the following code triggers theErrorevent:

The following code is in the Error event:

The DBError event fires in a DataWindow when a database error occurs as a result of an Update( ).

How do I stop the default error window from showing

By default, a modal message window displays the database error code and error message.Change the Return value to 1.

Return 1

Event Arguments

The DBError event contains useful information about the database error

• SQLDBCode DBMS- specific error code
• SQLErrText DBMS – specific error message
• SQLSyntax – SQL statement sent to the DBMS
• Buffer – DataWindow buffer where the error row is located
• Row – Row number of the error row within the buffer

How come the ButtonClicked event doesn’t even fire when I click on a datawindow button?

Here is the situation I found. My datawindow was very wide and I had a button at the end of the row with a hscroll bar.After this button is pressed , the datawindow’s Clicked event first fired and the following script ran:

This adjusts the horizontal position of the datawindow.And, the ButtonClicked event never fires.To solve this problem, move the buttonclicked script to the Clicked event.

Listing of Datawindow ControlEvents

• ButtonClicked When the user clicks a button.
• ButtonClicking When the user clicks a button. This event occurs before the ButtonClicked event.
• Clicked When the user clicks a noneditable field or between fields in the DataWindow control.Return codes:0 – (Default) Continue processing.1 – Stop processing.
• Constructor Immediately before the Open event occurs in the window.
• DBError When a database error occurs in the DataWindow control.Return codes:0 – (Default) Display the error message.1 – Do not display the error message.
• Destructor Immediately after the Close event occurs in the window.
• DoubleClicked When the user double-clicks a noneditable field or between fields in the DataWindow control.For a RichText presentation style DataWindow, when the user double-clicks in the text.
• DragDrop When a dragged control is dropped on the DataWindow control.
• DragEnter When a dragged control enters the DataWindow control.
• DragLeave When a dragged control leaves the DataWindow control.
• DragWithin When a dragged control is within the DataWindow control.
• EditChanged When a user types in an edit control in the DataWindow control.
• Error When an error is found in a data or property expression for a DataWindow object.
• GetFocus Just before the DataWindow control receives focus (before it is selected and becomes active).
• Help When the user presses the F1 key or drags the context help button (question mark) from the title bar to a menu item or control.
• ItemChanged When a field in the DataWindow has been modified and loses focus (for example, the user presses enter, the tab key, or an arrow key or clicks the mouse on another field within the DataWindow).Return codes:0 – (Default) Accept the data value.1 – Reject the data value and don’t allow focus to change.2 – Reject the data value but allow focus to change.
• ItemError When a field has been modified, the field loses focus (for example, the user presses enter, tab, or an arrow key or clicks the mouse on another field), and the field does not pass the validation rules for its column.Return codes:0 – (Default) Reject the data value and show an error message box.1 – Reject the data value with no message box.2 – Accept the data value.3 – Reject the data value but allow focus to change.If the Return code is 0 or 1 (rejects the data), the field with the incorrect data regains the focus.
• ItemFocusChanged When the current item in the control changes.
• LoseFocus When the DataWindow control loses focus (becomes inactive).
• Other When a Windows message occurs that is not a PowerBuilder event.
• PrintEnd When the printing of the DataWindow ends.
• PrintPage Before each page of the DataWindow is formatted for printing.Return codes:0 – Do not skip a page.1 – Skip a page.
• PrintStart When the printing of the DataWindow starts.
• RButtonDown When the right mouse button is pressed on the control.For a RichText presentation style DataWindow, if PopUp Menu has been turned on, this event will not be triggered when the right mouse button is pressed.
• Resize When the user or a script resizes a DataWindow control.
• RetrieveEnd When the retrieval for the DataWindow is complete.
• RetrieveRow After a row has been retrieved.Return codes:0 – (Default) Continue.1 – Stop the retrieval.
• RetrieveStart When the retrieval for the DataWindow is about to begin.Return codes:0 – (Default) Continue.1 – Do not perform the retrieval.2 – Do not reset the rows and buffers before retrieving the data from the database.
• RowFocusChanged After the current row changes in the DataWindow.
• RowFocusChanging When the current row is about the change in the DataWindow. This event occurs before the RowFocusChanged event.
• ScrollHorizontal When the user scrolls right or left in the DataWindow control with the tab or arrow keys or the scrollbar.
• ScrollVertical When the user scrolls up or down in the DataWindow control with the tab or arrow keys or the scrollbar.
• SQLPreview After a Retrieve, Update, or ReselectRow function call and immediately before the SQL statement is submitted to the DBMS.The following return codes specify the action that takes place when the event occurs after an Update function call only:0 – (Default) Continue.1 – Stop.2 – Skip this request and execute the next request.
• UpdateEnd When all the updates from the DataWindow to the database are complete.
• UpdateStart After an Update function call and just before changes in the DataWindow are sent to the database.Return codes:0 – (Default) Continue.1 – Do not perform the update.

How do I put a DataWindow into query mode?

How do I clear the query criteria?

Criteria entered by users are retained in the DataWindow so they can refine the query.If you want to clear user-entered query criteria:

What does the QuerySort property do?

Turning query sort mode forces the DataWindow into query mode at the same time.The first row of the DataWindow is dedicated to entering sort criteria, all subsequent rows are dedicated to entering WHERE criteria.

What does a column’s Criteria.Override_edit do?

If this property is set to ‘yes, the edit style overrides anyu other style that was specified such as radio button or dropdown listbox.The user is free to type in any value without restriction.

This property can be set in the DataWindow property or in powerscript:

What does a column’s Criteria.Required do?

If this property is set, a user in query mode may only enter equality criteria in this column.

This property can be set in the DataWindow property or in powerscript:

How do I retrieve the data?

Setting the DataWindow’s query mode criteria to ‘no’ will disable query mode and store the user’s specification for the next Retrieve( )

Prompt for Criteria

When a retrieve is performed, a dialog box appears and prompts the user to supply Retrieval Criteria.

This is set for each column you wish to prompt for criteria.

Query mode should be off as this could cause conflicting WHERE clause criteria.

If you wan users to specify sort criteria as part of the select, use QuerySort mode

If you want to limit the columns a user can specify criteria, use Prompt for Criteria

Prompt for criteria forces you to use any edit style set in the datawindow painter, query mode allows you to override this.

Levels of Validation

1.Data type correct (failure triggers ItemError event)

2.Pass validation rule (failure triggers ItemError event)

3.Value changed

4.ItemChanged Event

Validation property tab

Triggered by ENTER, TAB, UP ARROW, or DOWN ARROW key or clicks another Datawindow column.An Update( ) or AcceptText( ) function will also cause current input to be validated.

User constructs a Validation Expression and message text.External global functions may be called.

Edit Control data is fetched with GetText( )

Script may read and modify validation rules using GetValidate(“<column name>”) and SetValidate(“<column name>”).

Coding a validation script in the ItemChanged event that displays a message box for invalid values, the ItemError message box also appears.To prevent the user from seeing two error messages, consider handling the message in one place, such as the ItemError event.

To change the message title from “DataWindow Error” to something else, use:

To change the message title for a specific DataWindow, use:

Get From The Internet

Good Luck!