---------------------------------------------------------- back to tips menu
DW Tip 1: Change a Regular List DW into a Grid and Vice-Versa
1. Export the Datawindow. 2. Edit the exported datawindow. 3. Search for "Processing= " a) To make List DW become grid, change to Processing=1 b) To make List DW become "normal" non-grid, change to Processing=0
DW Tip 2: Getting Two Columns from the Same Row onto a PB Graph
On a PB Graph DW, PowerBuilder wants different values on the graph (e.g. different pieces of the pie) to come from the SAME column but from different rows in the table. This can be frustrating when you are trying to graph two different columns in the SAME row of a table.
The solution (where two columns are graph are 'A' and 'B') is to select the two columns separately and union themm as follows:
SELECT A Value_Col, 'Desc of A' Category_Col FROM
TAB_X WHERE criteria
UNION
SELECT B Value_Col, 'Desc of B' Category_Col FROM TAB_X WHERE criteria;
This will let you graph A and B with their own description.
DW Tip 3: Making "GroupBoxes" inside a DataWindow (pre-PB6)
Prior to PowerBuilder 6, DataWindows did not have groupboxes as windows did. Here's how to make a "pretend" groupbox on your DW that works pretty well:
1. Paint your columns that you're going to group onto the DW.
2. Choose the recentangle (or box) on the DW Painter. Give the box a TRANSPARENT fill to reduce problems associated with determining whether the rectangle or the fields are "in front".
3. Size the Box over the fields you're grouping
4. Choose Right-mouse on the rectangle and choose the "Layer-Band" attribute. This avoids the PB "feature" that causes the box to move around when you resize the band on the DataWindow.
DW Tip 4: Making Grids DW's Look "Nice"
When using a grid on the non-editable list datawindow, there are a few ugly-looking side-effects that you might want to avoid.
1. If you put the mouse on a "cell" on a grid, PB defaults to
turning the colour of that field black and changing the row focus (and selecting the row
if you are using a row selection facility). If you don't want this cell blackening (i.e.
you don't want to cut/paste columns of data to other tools), then do the following:
a) Go into the DataWindow (not the DW control on the window)
b) Go to the white area (i.e. not a field on the window), hit right mouse, look at the
"General" tab – on this tab, look at the "grid" GroupBox.
c) In the grid groupbox, turn "Mouse Selection" OFF.
2. You may also not get the row selection behaviour that you are looking for, if one or more columns in your grid DataWindow is editable. Giving all columns a tab order of 0 to get a smooth row selection line.
3. If you have functionality where the user can resort rows, by clicking the column header, you will find that the whole column turns black when the header is clicked on (a visually displeasing side effect). If you don’t want the column blackening, you essentially have to turn the grid capabilities off. This means column resizing and column movement will no longer be supported. To turn of the grid capabilities on the grid DW, follow procedures under point 1 a) - 1c) but choose grid "Off" in the drop down.
DW Tip 5: Copying data from a Grid DW to the Windows Clipboard
Grid DataWindows have the rather unique ability to let you select a number of "cells" in the DataWindow (or a whole rows or whole columns of data). This example illustrates how to provide copy support for selected data from a Grid Datawindow. It assumes that a new menu item called m_SpecialCopy has been added under the m_Edit menu.
Extend the m_Edit script and add the following code to determine if Special Copy item should be enabled:
GraphicObject lg_Object DataWindow lg_DW string ls_SelectedText
lg_Object = GetFocus ()
CHOOSE CASE TypeOf (lg_Object)
CASE DataWindow!
lg_DW = lg_Object
This.m_SpecialCopy.Enabled = &
(lg_DW.dwDescribe ("datawindow.selected.data") <> '')
END CHOOSE
==============================
Code the following in m_SpecialCopy (or in an event triggered by m_SpecialCopy):
graphicobject lg_Object datawindow lg_DW
lg_Object = GetFocus ()
CHOOSE CASE TypeOf (lg_Object)
CASE DataWindow!
lg_DW = lg_Object
ClipBoard (lg_DW.dwDescribe ("datawindow.selected.data"))
END CHOOSE
DW Tip 6: Dealing with Proportional Font Data
While a proportional font (e.g. Arial, Times New Roman) is more pleasant to look at and requires less space on average than a fixed-size font (e.g. Courier), it can be tricky to work with when you have limited room to fit data. The trouble is that the physical space required to show proportional data is unpredictable since one "W" or "m" is as wide as three i’s or t’s. Two proportional font situations are discussed:
DW Tip 6a. Handling Proportional Font for One Column
Sizing a Numeric Column: Fortunately, major fonts design numbers (0 through 9) to be the same width so determining the required column width to show numbers requires straight mulitplication. Since dollar signs, commas and decimal points occur in predictable places (e.g. $99,999.99), it is still possible to find the required column width of a formatted number with a bit of trial-and-error.
Sizing an AlphaNumeric Column: Alphanumeric data will have unpredictable sizes -- you can size the field to fit the widest possible data (e.g. all W’s or m’s) but actual data will usually fill less than half of a column sized in this manner. This approach will both waste real estate and look odd to the users. A more practical approach is to try to size the column so that all characters will be visible at least 95% of the time -- for fields that are at least 15 characters long, making the field just big enough to show all "A" is a good guideline. Shorter fields will have a higher percentage variability in size and often cannot be truncated (e.g. codes). In this case, a "brute force" review of data in a DataWindow list is often required to determine an appropriate width.
Giving Access to Non-Visible Data: If you set the column’s auto horizontal scroll on, the user who enters/updates the data can always scroll over to see non-visible characters. If the data is being shown on a list, the grid style can be used to allow the user to widen the column at runtime to see wider data. DW Tip 1 tells you how to change a DataWindow from regular style to grid style.
DW Tip 6b. Handling Proportional Font for Several Pieces of Data Appended Together
The method below describes how several pieces of data can be appended together for display on one line until visible space runs out. The advantage of the method described is that it does not depend on calculating the actual width of each character for a particular font type and point size. Simpler methods were attempted but failed.
Warning: a bug is PB 6.00 causes the vertical scrollbar to ALWAYS appear when live scrolling is set (even when there is insufficient data to warrant a scrollbar). Proper scrollbar behaviour is central for technique to function. For this reason, this technique should be only used on PB 5.x or PB 6.01 or later.
The method described requires a DataWindow and a function -- DataWindow setup and function code are included below.
Step 1: What this Method Does
The method below describes how to take a series of parameters (stored in an array) and formats them so they appear as follows:
Parm1Value, Parm2Value, Parm3Value
If there is not enough space to display all the parameters, a "..." will be displayed after the last parameter that fits. For example, if overflow (past the visible width) occurred after the third parm, the resulting string would be:
Parm1Value, Parm2Value, ...
Step 2: Set up a Supporting DataWindow
A purpose-made DataWindow has to be created to support the function. This DataWindow is used to test whether the parameters will "fit" onto a line. The DataWindow setup is described below.
a) the DataWindow can be the actual DW where formatted string will be
displayed or a "representative" DW that is set up with the required attributes.
b) the DataWindow should contain a column that has its font/size and width set to the
target value (i.e. set to the font and width values will be used when the string is
actually displayed to the user)
c) the DataWindow column should have its auto-resize-height =True, horizontal-scroll =
False and auto-horizontal-scroll = False.
d) the band where the DataWindow is found (typically the Detail band) should have
auto-resize-height = True
e) the DataWindow should have LiveScrolling = True
f) both the DataWindow band and the DataWindow control should be just tall enough to show
the one row. Inclusion of a second row (or a resized first row) should cause the
DataWindow scroll bar to appear.
g) in summary, the setup will ensure that when the characters become too wide to display
on one line, the row auto-resize kicks in (so the detail row now shows over two lines) and
then a DW scroll bar appears. The existence of the DW scroll bar can be programatically
checked for.
Step 3: The Function
This function takes these parms:
- as_ValueArray[] -- string array of values -- the parameters you want to show
- adw_DisplayDW - DataWindow to allow testing of "fit" (its setup was described
above)
- as_DisplayCol - Column Name in above DW (column setup was described above)
- as_Return - formatted string that will fit onto line
The code:
// this function creates a string to display parameters, showing as many
as will fit on a single line
//
// Parms are formatted as follows: Parm1Value, Parm2Value, Parm3Value
// If overflow occurs on the third parm, it displays: Parm1Value, Parm2Value, ...
integer li_Return = 1 string ls_ParmSep = ", " // Separator between different parms string ls_TestSuffix = " .." // test suffix which includes a space to cause word wrap string ls_Suffix = "..." // standard suffix when criteria won't fit string ls_StringWithNextParm // result string if next parm is included integer li_ArraySize // array size of input array integer li_Index = 1 boolean lb_LastParm = False
li_ArraySize = UpperBound(as_ValueArray[]) as_Return = ""
IF li_ArraySize < 1 THEN lb_LastParm = True END IF
// Loop to add parameters onto string
DO WHILE lb_LastParm = False
IF li_index = li_ArraySize THEN
lb_LastParm = True
END IF
// add next parameter onto end of existing string ls_StringWithNextParm = as_Return + as_ValueArray[li_index]
// add parm separator to end, if this is not the last parm
IF NOT lb_LastParm THEN
ls_StringWithNextParm = ls_StringWithNextParm + ls_ParmSep
END IF
// Put the new longer value into the DW (the test suffix has a blank followed by // chars so that word wrap and auto-size height kick in) adw_DisplayDW.SetItem(1,as_displaycol,ls_StringWithNextParm + ls_TestSuffix)
// If it fits, we're fine -- we will show this parm
IF Long(adw_DisplayDW.Object.DataWindow.VerticalScrollMaximum) = 1 THEN
as_Return = ls_StringWithNextParm
ELSE
// If it causes DW to scroll (i.e. spills over line), then don't include it and
// just append continuation suffix indicator
as_Return = as_Return + ls_Suffix
lb_LastParm = True // stop adding parms -- we're full
END IF
li_index++
LOOP
Return li_Return
Simpler Techniques (for DW Tip 6b): Tried but Failed
The following simpler methods were attempted in PB5 but did not allow for programatic detection of characters being too wide to fit into a DW column:
1. Tried to test DW column alone, using SetText/AcceptText:
- set DW Column with horizontal scroll/auto horizontal scroll off
- did a SetText() with a value that was too wide to fit into column
- tested return codes of both SetText() and AcceptText() but neither gave bad return code
to indicate value was "visually" too wide.
- this method seemed plausible since turning horizontal scroll off causes PB to
"ding" and reject characters once a column is visibly full -- the desired PB
logic seems to be in its ancestor EditChanged and not available.
2. Tried to test for a change of height of a data column or text column
in a DataWindow:
- set DW Column with horizontal-scroll/auto-horizontal-scroll off and auto-resize-height
on
- after setting column with a value that caused hieght resize to occur, tried to test DW
column height to see if height value was larger. PB only returns the design time DW column
height -- presumably it does this since columns in different rows could have different
heights. It didn’t matter that I only actually had one row.
- tried same with a text box in DW and had same problem of only getting the design height
returned.
DW Tip 7: General Routine to Validate DDDW Input
When working with enterable Drop down DataWindows (DDDWs), a common requirement is to
validate the data entered into the DDDW. In addition, it is frequently necessary to get
another
value from the DDDW (e.g. user enters a code which has to be validated and if the code is
valid
you need to get the corresponding description from the DDDW). The function below serves
both
those purposes and it is straightforward to call from ItemChanged.
Function: f_GetDddwValue
Purpose: First it locates the DDDW row that corresponds to the value entered. If
the DDDW entry was valid, it gets the value from another column on that DDDW row.
Parameters:
in - as_DDDWColumnName: Column name in parent DW (i.e. the DW with the DDDW).
This is the DW column name using the DDDW edit style.
in - as_ColumnValue - The DDDW value supplied by the user. It is used to
locate the appropriate DDDW row.
in - as_OtherDDDWColumnName - Name of the DDDW column whose value is desired.
This is the column name in the actual DDDW datawindow (e.g. the description).
out- as_OtherDDDWColumnValue - Value of DDDW Column (e.g. description) on the
selected DDDW row.
Returns: Long
>0 Row number where value was found
0 No input value provided
-1 User provided a bad value -- value entered was not found in DDDW.
-2 Internal Error, bad input column name (not a DDDW?)
string ls_ChildDDDWColumn
string ls_FindString
long ll_FoundRow
string ls_ErrorArgs[]
DatawindowChild ldwc_Source
IF Len(as_ColumnValue) > 0 THEN
// If this was a valid DW, continue to look for entry
IF This.GetChild(as_DDDWColumnName,ldwc_Source) = 1 THEN
// Determine the name of the column in the child datawindow to search
ls_ChildDDDWColumn = This.Describe(as_DDDWColumnName + ".DDDW.DataColumn")
CHOOSE CASE Lower(Mid(This.Describe(as_DDDWColumnName+'.ColType'), 1, 5))
CASE "decim", "long", "ulong", "numbe", "real"
ls_FindString = ls_ChildDDDWColumn + "=" + as_ColumnValue
CASE ELSE
ls_FindString = ls_ChildDDDWColumn + "='" + as_ColumnValue + "'"
END CHOOSE
// Search the datawindow for the value
ll_FoundRow = ldwc_source.Find( ls_FindString, 1, ldwc_Source.RowCount() )
// If the value is not found set user error return value: -1
IF ll_FoundRow <= 0 THEN
ll_FoundRow = -1
ELSE
// If the value is found, get the other DDDW column value requested
as_OtherDDDWColumnValue = String(f_GetItemAnyDWC(ll_FoundRow, &
as_OtherDDDWColumnName,ldwc_source))
END IF
ELSE
MessageBox("Error","Serious Error in f_GetDddwValue: invalid column name specified")
ll_FoundRow = -2
END IF
ELSE
// Return 0 to indicate no value was passed
ll_FoundRow = 0
as_OtherDDDWColumnValue = '' // reset description to EmptyString to correspond to input
END IF
Return (ll_FoundRow)
Note:
Sample invocation of this function can be found in the ItemChanged code sample.
f_GetItemAnyDWC can be modelled on of_GetItemAny in PFC's Base Services (with
the handle to the DW child passed as an argument).
DW Tip 8: Capturing Special Keys on your DataWindow (e.g. Enter)
A common requirement for applications is to capture certain keystrokes and perform
special
actions when the user presses these keys. To capture keystrokes on your DataWindow, do the
following:
In this sample event, the following keystrokes can be captured:
UE_KEY EVENT:
// if descendent DW wants tab ... capture it
IF ib_CaptureTab AND KeyDown(KeyTab!) THEN
// determine whether it is a tab or back-tab
IF NOT KeyDown(KeyShift!) THEN
This.POST ue_Tab()
ELSE
This.POST ue_BackTab()
END IF
Return 1 // have key not otherwise be ignored by PB
// if descendent DW wants enter keys ... capture it
ELSEIF ib_CaptureEnter AND KeyDown(KeyEnter!) THEN
This.POST ue_Enter()
Return 1 // have key not otherwise be ignored by PB
// otherwise send the key to the window-level for standard processing
ELSE
IF IsValid(iw_ParentWin) THEN
iw_ParentWin.TriggerEvent(Key!)
ELSE
// how did we get a key event for invalid parent?
f_SignalError(0/gi_zero,"Invalid Parent Window in Key Event")
END IF
END IF
Note: a handle to the parent window (iw_ParentWin) is obtained in the Constructor event. PFC's
of_GetParentWindow funciton is a good method to generically get a handle to the parent
window.
DW Tip 9: Avoiding "Double" Error Messages in DW Validation
A common problem when setting up validation logic in ItemChanged is that two message
get
displayed. First, the intended message in ItemChanged and then a default PowerBuilder
message:
"Item '<value>' does not pass validation test". One simple solution to
avoid this problem is to
have descendents set a flag (ib_SuppressMsg) in the ItemChanged event, when they've
already
displayed an error message.
In ItemChanged event: IF ... error situation ... THEN ... display message ... li_Return = 1 // reject value END IF // Bottom of ItemChanged script ... // set flag so ItemError know to suppress its default message IF li_Return = 1 THEN ib_SuppressMsg = True END IF
In ItemError, check this flag and, if set, return 1 so the default ItemError message is suppressed: IF ib_SuppressMsg THEN li_Return = 1 ib_SuppressMsg = False // reset flag for next time ELSE ... error not caused by ItemChanged END IF
Full ItemChanged and ItemError code samples are available.
---------------------------------------------------------- back to tips menu