Title: BI513 Advanced Data Techniques: When a DataWindow is Not Enough
1BI513Advanced Data Techniques When a
DataWindow is Not Enough
Lynn Clark Emerald City Software emcitysoft_at_aol.c
om
2Datawindows 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
3Target AudienceBeginning to intermediate
powerbuilder developersSome tips and tricks you
can use right now, not in
POWERBUILDER 8 or 9 or 10!!
4What 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
5Datawindows Limitations
6Datawindows Limitations
- Do they really have any?
- Too many joins
7Datawindows Limitations
- Do they really have any?
- Too many joins
- Data from many unrelated
- sources
8Datawindows Limitations
- Other Situations
- Parameters unknown until runtime
- Parameters that change on the fly
- Complex presentation requirements
9Datawindows Limitations
10Solutions
- Stored Procedures?
- Separate skill set from PB
- Sometimes in the domain of DBA
- Cant paint them graphically
11Other Solutions
- Some creative approaches
- to solving
- complex business problems.
12Backwater Bay Boating
- Operates 25 boats
- Every boat is unique
- New schedule every
- season
- Subject to regulations
- 5 routes
13Project Requirements
- Must be flexible for
- changing needs
- Users are very low
- tech (and hostile)
- Must be VERY user
- friendly
14Backwater 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.
15Proposed Interface
- This shows one route
- with three stops.
- What about other
- variations?
16Proposed Interface
- This shows a route
- with 5 stops
17How??? Openuserobjectwithparm
- Standard Visual User
- Object of Type
- datawindow
18Schedule Entry Window
- It is completely blank
- at development time
- User supplied parameters
- determine number of
- userobjects opened at
- runtime
19Get 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( )
20Construct 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
21Construct 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
22The Finished Product
- Powerbuilder demo of sailing schedule
23Next 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)
24Proposed Interface
- Project Manager likes
- Drag and Drop
- Must be able to add
- comments to each
- vessel assignment
25How??? Openuserobjectwithparm
- Once again much of
- the window is blank
- at development time
26Custom 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
27Openuserobjectwithparm
- 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
28The Finished Product
- Powerbuilder demo of vessel route
- assignments
29Task 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.
30How??? Openuserobjectwithparm
- Similar Approach
- Standard visual userobject of type
- checkbox
- Constructor Script
- this.text message.stringparm
31Change 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?
32Make 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
33Evaluate The Data
- Array of user objects
- Evaluate each user object
- and update datawindow
- based on value of variable
- within user object.
34But 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!
35The Finished Product
- Powerbuilder demo of employee
- qualifications screen.
36Openuserobject 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.
37Next 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
38Interface Requirements
- Build only required number of weeks
- (No blank columns at the end)
- Support drag and drop
39HOW???? Add Columns To A Datawindow On The
Fly
- External datawindow
- Grid presentation style
- Only two columns initially (Vessel Id
- and Vessel Name)
40Interface Design
- Only the columns we
- know to be present
- are in the original
- datawindow
41Initialize The Datawindow
- Easy Way Export to script and
- extract the select statement
- Place select statement in instance
- variable is_select
42Retrieve The data
- Retrieve the weeks in the season
- Retrieve the vessel data
- Build the routes
- (using openuserobjectwithparm!)
43Add 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
44How 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
45Modify Function
- Use the modify function to change the
- select statement to the new select
- statement with the added column
46Add Column And Header To Visual Display Of
Datawindow
- Powerbuilder demo of scripts to
- add column and header.
47The Finished Product
- Powerbuilder demo of season
- assignment screen
48Conclusion
- 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!