BI513 Advanced Data Techniques: When a DataWindow is Not Enough

1
BI513Advanced Data Techniques When a
DataWindow is Not Enough
Lynn Clark Emerald City Software emcitysoft_at_aol.c
om
2
Datawindows Why We Love Them

• What datawindows do for us
• Combine data access with data presentation
• Lets us build complex SQL statements graphically
• Lets us sort and filter data on the client side
• Lets us easily control presentation style

3
Target AudienceBeginning to intermediate
powerbuilder developersSome tips and tricks you
can use right now, not in
POWERBUILDER 8 or 9 or 10!!
4
What This Presentation Is (And Is Not)

• IS Real world examples of
• advanced techniques
• (with code that tells you how to do them!)
• IS NOT Web, HTML, Java

5
Datawindows Limitations

• Do they really have any?

6
Datawindows Limitations

• Do they really have any?
• Too many joins

7
Datawindows Limitations

• Do they really have any?
• Too many joins
• Data from many unrelated
• sources

8
Datawindows Limitations

• Other Situations
• Parameters unknown until runtime
• Parameters that change on the fly
• Complex presentation requirements

9
Datawindows Limitations

• SO..what do we do?

10
Solutions

• Stored Procedures?
• Separate skill set from PB
• Sometimes in the domain of DBA
• Cant paint them graphically

11
Other Solutions

• Some creative approaches
• to solving
• complex business problems.

12
Backwater Bay Boating

• Operates 25 boats
• Every boat is unique
• New schedule every
• season
• Subject to regulations
• 5 routes

13
Project Requirements

• Must be flexible for
• changing needs
• Users are very low
• tech (and hostile)
• Must be VERY user
• friendly

14
Backwater Bay

• First task The sailing schedule
• Problem Each route can have any number
• of stops, and these can change each
• season. Project manager wants a
• spreadsheet like feel.

15
Proposed Interface

• This shows one route
• with three stops.
• What about other
• variations?

16
Proposed Interface

• This shows a route
• with 5 stops

17
How??? Openuserobjectwithparm

• Standard Visual User
• Object of Type
• datawindow

18
Schedule Entry Window

• It is completely blank
• at development time
• User supplied parameters
• determine number of
• userobjects opened at
• runtime

19
Get The Parameters
dw_1.accepttext() //clear out prior
sailings of_clear_sailings( ) //get the
parameters il_season dw_1.object.season1 il_ro
ute dw_1.object.route1 il_sail_posn
dw_1.object.sail_posn1 il_terminal
dw_1.object.depart_term1 //construct the
terminals of_construct_terminals( )
20
Construct The Terminals

• //declare all the user objects
• uo_term_name luo_term
• uo_sailings luo_sailings
• ucb_addrow lucb_addrow
• uo_cb_remove lucb_delete
• //declare other variables
• long ll_result, ll_y, ll_x, ll_add_y, ll_del_x
• str_schedule_entry lstr_schedule_entry
• //establish spatial parameters (so it looks
  right)
• ll_y a_y 100
• ll_x a_x
• ll_del_x ll_x 303
• //populate the structure that will tell the
  userobject
• //about itself
• lstr_schedule_entry.route_id il_route
• lstr_schedule_entry.terminal_id a_term_id
• lstr_schedule_entry.schedule_plan_id il_season

• Declare the Userobjects
• Must determine the
• geometric layout
• Tell the userobject
• about itself

21
Construct The Terminals Cont.
//open the terminal name userobject ll_result
openuserobjectwithparm(luo_term,lstr_schedule_entr
y, a_x, a_y) if ll_result lt 0 then Messagebox('Co
uld not create terminal', 'Could not create
terminal') return -1 end if //open the sailing
schedule userobject ll_result
openuserobjectwithparm(luo_sailings,
lstr_schedule_entry, a_x, ll_y) if ll_result lt 0
then Messagebox('Could not create sailings',
'Could not create sailings') return -1 end
if luo_term.iuo_sailings luo_sailings luo_saili
ngs.iw_parent this ll_add_y a_y
luo_sailings.height 125
22
The Finished Product

• Powerbuilder demo of sailing schedule

23
Next Task Vessel Route Assignments

• A vessel can be on any route at any time
• Must be easy to move a vessel
• Both vessels and routes can be
• deactivated (only active should show)

24
Proposed Interface

• Project Manager likes
• Drag and Drop
• Must be able to add
• comments to each
• vessel assignment

25
How??? Openuserobjectwithparm

• Once again much of
• the window is blank
• at development time

26
Custom Userobject With Static TextControl
Constructor script str_sail_posn_info
lstr_sail_posn lstr_sail_posn
message.powerobjectparm il_sail_posn_work_loc_id
lstr_sail_posn.work_loc_id is_sail_posn_name
lstr_sail_posn.name st_1.text is_sail_posn_name
27
Openuserobjectwithparm

• Steps
• Retrieve the data
• Populate a structure with required elements
• Open a userobject for each row of data
• In the constructor of userobject, tell the
• object what to do with the data elements

28
The Finished Product

• Powerbuilder demo of vessel route
• assignments

29
Task 3 Sailing Qualifications

• The Problem Each vessel and/or route
• can require sailors to be qualified in
• different ways. This must be editable from
• the interface, but cannot require code
• changes to maintain.

30
How??? Openuserobjectwithparm

• Similar Approach
• Standard visual userobject of type
• checkbox
• Constructor Script
• this.text message.stringparm

31
Change The Behavior Of The Userobject

• Script in the clicked event
• iw_parent.ib_saved false
• iw_parent.iu_qual_cbxil_count.ib_changed true
• if this.checked true then
• iw_parent.iu_qual_cbxil_count.ib_checked
  true
• else
• iw_parent.iu_qual_cbxil_count.ib_checked
  false
• end if
• What does this do?

32
Make The Userobject Communicate With The Window

• Window keeps an array of the checkboxes and
• establishes a pointer to each one as it is
  created.
• As each checkbox is turned off or on,
• the window is informed.
• u_qual_cbx lu_qual_cbx
• long ll_success
• ll_success this.openuserobjectwithparm(lu_qual_c
  bx, a_name, a_xpos, a_ypos)
• if ll_success gt 0 then
• lu_qual_cbx.iw_parent this
• lu_qual_cbx.il_qual_id a_qual_type
• lu_qual_cbx.il_count a_count
• iu_qual_cbxa_count lu_qual_cbx
• lu_qual_cbx.show()
• end if
• return ll_success

33
Evaluate The Data

• Array of user objects
• Evaluate each user object
• and update datawindow
• based on value of variable
• within user object.

34
But Why Is This Significant?

• Because the number of requirements for
• a given vessel or route can change on the
• fly and the userobject will be created
• automatically. Does not require a code
• change to change the interface!

35
The Finished Product

• Powerbuilder demo of employee
• qualifications screen.

36
Openuserobject Function

• OpenUserObjectWithParm is a very
• effective, versatile method of displaying
• data in situations where a standard data
• window approach is not sufficient. Allows
• complex data presentations that you
• cannot achieve with
• just a datawindow.

37
Next Task Vessel Assignments for a season

• Vessel can move from route to route
• on a daily basis
• Initial planning done one week at
• a time
• Season can have varying number
• of weeks

38
Interface Requirements

• Build only required number of weeks
• (No blank columns at the end)
• Support drag and drop

39
HOW???? Add Columns To A Datawindow On The
Fly

• External datawindow
• Grid presentation style
• Only two columns initially (Vessel Id
• and Vessel Name)

40
Interface Design

• Only the columns we
• know to be present
• are in the original
• datawindow

41
Initialize The Datawindow

• Easy Way Export to script and
• extract the select statement
• Place select statement in instance
• variable is_select

42
Retrieve The data

• Retrieve the weeks in the season
• Retrieve the vessel data
• Build the routes
• (using openuserobjectwithparm!)

43
Add Columns To The Datawindow

• For each week, add a column to the
• datawindow
• First, add a column to the table section
• of the select statement

44
How To Add The Column

• string ls_select, ls_added_column, ls_new_select
• long ll_len
• ll_len len(is_select)
• is_select left(is_select, ll_len - 1)
• ls_added_column "column(typechar(20)
• updatewhereclauseno
• name " as_column_name
• " dbname'"
  as_column_name"')"
• is_select is_select ' ' ls_added_column
  ')'
• return is_select

45
Modify Function

• Use the modify function to change the
• select statement to the new select
• statement with the added column

46
Add Column And Header To Visual Display Of
Datawindow

• Powerbuilder demo of scripts to
• add column and header.

47
The Finished Product

• Powerbuilder demo of season
• assignment screen

48
Conclusion

• Openuserobjectwithparm, drag drop,
• and adding columns to a datawindow
• at run time can all be used to provide
• complex presentation styles that meet
• complex business needs and provide
• flexibility.
• Think creatively!