Powerbuilder examples

These are actual examples from practical field cases. Each case has a problem description and a suggested solution.
Using environment variables
How to set screen sizes using environment variables
Suggested Solution
// Center a window based on the screen dimensions.

environment le_env
int screenheight, screenwidth
 
/* get screen measurements */
 
GetEnvironment(le_env)
screenheight = PixelsToUnits(le_env.screenheight,YPixelsToUnits!)
screenwidth = PixelsToUnits(le_env.screenwidth,XPixelsToUnits!)
 
/* now position window */
 
this.Move( (ScreenWidth - this.Width) / 2, (ScreenHeight - this.Height) / 2 )
Using DropDown Datawindows
Problem description

I have three tables with the following fields:
Venue
      Venueid
      Venuename

League
      Venueid
      League_id
      League_name

Competitions
      Venueid
      League_id
      Competion_id
      CompetitionsName
      Year
What I would like to do is create a Datawindow with 4 Drop Down boxes, one each for Year, Venue, League, and Competitions. The year would be a drop down containing all the distinct years from the Competition Table.
The Venue would contain all the distinct venues from the Venues table.
Based on the Venue selected, I want the DropDown for League to only have those leagues that are related to a particular Venue.
Suggested Solution
DropDown datawindows should only be used when the values can easily be obtained from an existing datawindow. An example would be rep_code on a customer table. Given that a rep_code datawindow exists, this datawindow can be used to populate a DropDown datawindow on rep_code.
In the example above we suggest limiting the use of the dropdown datawindows and using a different approach as shown below.
1) Create a datawindow for venues (eg d_venues)
2) Create a datwindow for leagues (eg d_leagues)>br> Specify a retrieval argument for d_leagues on the column venueid.
3) Create datawindow controls for each of the above and place them on the same window - link the relevant datawindow objects to the appropriate control
(dw_1 -> d_venue and dw_2 ->d_leagues)
4) Simply retrieve all the venue records - dw_1.retrieve()
5) In the doubleclicked event of dw_1 specify a retrieval for the league records by passing the venueid as a retrieval argument. eg: dw_2.retrieve(ls_venueid)
The correct leagues will be displayed for each selected venue.

Crosstab matrix-like report
Problem description
How to generate a matrix-like report as in the example below from the following dataset :
CREATE TABLE "dba"."salessum" 
("season" smallint NOT NULL, 
 "customer" char(6) NOT NULL, 
 "product_grp" char(3) NOT NULL,
 "sales_date" date NOT NULL,
 "sales_rep" char(6) NOT NULL,
 "sales_amt" decimal(10,2) NOT NULL,
 "cost_amt" decimal(10,2) NOT NULL);
Lets assume the table holds the following data:
2005|1001|HW|20050801|A|11000.00|8000.00|
2005|1002|SW|20050801|A|20000.00|4000.00|
2005|1003|IN|20050802|B|4000.00|1000.00|
The following report is required:

Season Salesperson HW SW IN Total
2005 A 11000 20000 0 31000
2005 B 0 0 4000 4000
Totals 11000 20000 4000 35000

This effect can be achieved by using a crossttab datawindow

File > New > Datawindow
Select Crosstab
In the crosstab definition, specify season and sales_rep for the rows,
product_grp for the columns and sales_amt as the value.
This should generate a basic but adequate crosstab report for this purpose.

Retrieval not working against wild card retrieve on character field

Problem description We specified following:
	retrieval argument: arg_code
	where condition:	cusmas.cuscode like :arg_code

The full sql statement was as follows:

  	 SELECT cusmas.cuscode,   
      	    cusmas.cusname,   
       	    cusmas.tel_num   
   	FROM cusmas 
  	 WHERE cuscode like :arg_code;

However the retrieval returned no rows.
Suggested Solution
We got it to work by adding the convert(varchar(100) ) statement like so:
where convert(varchar(100), cusmas.cuscode) like :arg_code

Quotations around item names
Problem description
Some systems generate " " around the table and field names. This may cause syntax problems on some servers.
Example 'syntax error near . '
SELECT   "floads"."season",   
         "floads"."f_code",   
         "farmers"."f_name",   
         "floads"."pool_grp",   
         "floads"."pool_code",   
         "poolmas"."pool_name",   
         "floads"."wbload",   
         "floads"."wbdate",   
         "floads"."weekno",   
         "floads"."nettmass",   
         "floads"."num_crates"  
    FROM "farmers",   
         "floads",   
         "poolmas"  
   WHERE ( "floads"."f_code" = "farmers"."f_code" ) and  
         ( "floads"."season" = "poolmas"."season" ) and  
         ( "floads"."pool_code" = "poolmas"."pool_code" ) and  
         ( ( "floads"."season" between :arg_s_seas and :arg_e_seas ) )    
Suggested Solution
Remove the " " using the editor

Multiple prompts in a composite datawindow
Problem description
When doing a retrieve against composite datawindow, you enter retreieval arguments. Each of the nested datawindows prompts you again for the same retrieval argusments.
Example:
You have a composite data object, d_co_packout. This composite data object contains two nested data objects, namely d_receipts and d_despatches, each having the same two retrieval arguments : arg_season and and arg_code.
The window w_packout contains a datawindow object dw_1. Dw_1 is assigned the data object d_co_packout. You use two text fields on w_packout where the user can enter the retrieval arguments. You have a command button cb_retrieve with the following script:
integer li_season
string ls_code

li_season=integer(sle_season.text)
ls_code=(sle_pool.text)

parent.dw_1.retrieve(li_season,ls_code)
Suggested Solution
In the data object painter, for the composite data object, d_co_packout, specify the same two retrieval arguments. Then for each of the nested objects, d_receipts and d_despatches set the properties for the arguments in the properties pane as follows:
Properties > General > Arguments > Expression
For argument season the expression=arg_season and for the argument code the expression=arg_code.
When you run the application, the nested windows will now use the retrieval arguments of the composite data object.

Sorting colums in a list by double clicking the column name (header)
Problem description
How to sort the rows in a list data column when the user clicks on the column name.
Suggested Solution
Code for the datawindow double clicked event

string ls_colname

if row=0 then
  ls_colname=dwo.name
  ls_colname = Left(ls_colname, Len(ls_colname) - 2)  // Get rid of the '_t'

  if is_sort_sequence="a" then
	 is_sort_sequence="d"
  else
	 is_sort_sequence="a"
  end if
  wf_sort_dw(ls_colname,is_sort_sequence) //call a defined sort function

end if

Powerbuilder Examples - OOP method

Using environment variables

Using DropDown Datawindows

Crosstab matrix-like report

This effect can be achieved by using a crossttab datawindow

Retrieval not working against wild card retrieve on character field

Quotations around item names

Multiple prompts in a composite datawindow

Sorting colums in a list by double clicking the column name (header)