Getting Started-2-Tier

For this lesson, you are provided with an enhanced client screen based on the Order Item Detail screen that you created in Lesson 11. You continue to add to this screen and ultimately connect it with the other screens that you built in the tutorial.

In general, this lesson provides additional ideas and methods for enhancing screen wizard-generated JPL procedures and extending the application's database interaction capabilities.

In this lesson you learn how to:

• Add a widget (item_total) to the detail section (in the grid widget) of the screen that derives its data from database values.

• Implement JPL procedures that calculate and recalculate item totals and the grand total as order items are added, deleted, or changed.

• Specify the item_total and order_total widgets as members of the appropriate table views. Because these widgets are not defined in the source database table, they are known as virtual fields. Its inclusion in the table view allows a virtual field to participate in SQL generation and in transaction manager events.

• Define a new control string and procedure for the screen wizard-generated Delete push button, so a user can delete one detail record from order_items.

  ---

1. If necessary;

   • Invoke the editor and connect to the database.

   • Reopen tutorstd.lib (FileOpenLibrary).

2. Open lesson15.clt from tutorstd.lib. Use either the Library TOC or menu bar (FileOpenScreen).
   

   lesson15.clt includes a single-record master section, an Order Total label and corresponding data entry widget, a Delete Order push button, a grid display detail section, and wizard-generated push buttons.

3. Choose FileSave AsLibrary Member and save:

   • lesson15.clt as orditm.scr in library client.lib.

     This prompts you to overwrite the orditm.scr screen that you created in Lesson 11.

     Note: If you prefer not to overwrite the original orditm.scr client screen, open it and save it under a different name before saving the new version of orditm.scr.

Enhance the Order Item Detail screen so the grid widget shows a total for each order item. You do so by adding a column to the grid widget.

4. Give focus to the orditm.scr client screen.
5. Choose CreateSingle Line Text and click inside the grid widget.
   

   A new, default-sized grid member is added at the rightmost position of the grid widget (next to the Price grid member).

6. With the new grid member selected, set these properties:

   • Name property: item_total

   • Column Title property: Total

     This property displays a column title in the grid widget's first row.

   • Length property (under Geometry): 8

     This changes the onscreen size of the widget, so it is the same length as the Price grid member.

     Note: Use the grid's horizontal scroll bar to view offscreen columns. Or resize the grid to display all seven columns: select the grid widget and under Geometry, set the Onscreen Columns property to 7.

To display totals in currency format, set the Data Formatting property.

7. With the item_total grid member selected, under Format/Display, set its Data Formatting property to Numeric.

   Numeric format subproperties are displayed. The Format Type property specifies Local currency. This specifies to display the data in the form $0.00.

More About Data Formatting Options

A variety of formatting options let you control how widget data appears. You can choose from ten predefined date/time formats and ten numeric formats. You can also create custom formats for both data types. The format is automatically applied when data is entered into fields that have their format properties set accordingly.

Default formats are defined in the Panther message file. You can define your own set of format standards by editing the message file. For more information about the message file and custom formats, refer to Chapter 45, "Customizing the User Interface," in Application Development Guide.

You want the new item_total widget to display the total value of each order. This value can be calculated by multiplying values in two other widgets: qty*price. You can direct the transaction manager to perform this calculation via the SQL that it generates. To do this, you must set item_total's Use In Select property so it is included in the select list of the generated SQL SELECT statement, and provide the appropriate math expression.

8. Under Database, under FETCH DATA, set the Use In Select property to Yes.

   Related subproperties are displayed.

9. In the Expression subproperty, enter: qty*price
   

   The expression uses the values from both widgets belonging to the order_items table to yield a calculated result.

The transaction manager includes item_total in the SQL generation only if the widget is part of the appropriate table view—in this case, order_items. Widgets that are outside a table view are excluded from SQL generation.

The next few steps show how to identify widgets that are table view members and how to change table view membership. To do so, you must select the order_items table view widget via the DB Interactions window or the Widget List and access its properties.

10. Give focus to the client screen and choose ViewWidget List.

    The Widget List opens. It shows the widgets on the current screen: the widget's name, field number or contents is in the middle column and its widget type in the right column.

More About the Widget List

You can use the Widget List as another way to select widgets. All widgets on the current screen are listed in the Widget List, including invisible widgets, such as selection groups, synchronization groups and table views.

When you select an item from the list, the widget on the screen is also selected. The Properties window displays the properties common to the widgets that are currently selected, or of the screen if no widgets are selected.

You can select multiple contiguous widgets in the list with a click+drag or Shift+click; Ctrl+click to toggle membership in the selection set or to select non-contiguous items.

11. Select the order_items table view from the list of names.
    

    If the Properties window displays the table view properties, it confirms the table view is selected.

12. Choose EditGroupSelect Members.
    

    All members of the order_items table view are selected: ID (title_id), Qty (qty), and Price (price).

13. Add the item_total (Total) grid member to the selection set:

    • Windows Shift+click on the column's title.

    • Motif Shift+click within a cell in the column.
      

14. With all four members selected, choose EditGroupUpdate Group Members.

    The Update Group Members dialog opens.

    

    The Update Group Members dialog lists all groups to which the selected widgets belong.

15. Select order_items as the group to update and choose OK.

    All members are deselected.

    Note: To confirm the new membership, repeat Step 11 and Step 12. The Total grid member should be selected along with title_id, qty, and price. If it is not, repeat Step 13 - Step 15.

More About Groups and Group Membership

Widgets can belong to several types of groups. Each group type has its own set of properties that control group behavior:

• Synchronization—Controls how widgets scroll together. By default, all members of a grid widget belong to a synchronization group and therefore scroll together. Synchronized group properties can specify, for example, scroll increment and scroll behavior when the last item has focus.

• Table view—Contains one or more related widgets, usually associated with and named for a single database table. Table view members can also include widgets that are not part of the database table in order to display derived data. Table view properties give the transaction manager the information it needs to generate SQL statements-for example, sort order, or whether the table view is updatable.

• Selection—Comprises specific widgets types (multiple radio buttons, toggle buttons, or check boxes, and single list boxes) that enhance the user interface by providing users with visual choices. You can define selection group properties such as the number of selections that a user can make, the tabbing order, and the group's entry, exit and validation functions.

Order entry screens often include a grand total as well as item totals. In order to display grand totals, the orditm.scr client screen has a single line text widget order_total and a corresponding Order Total label. The value in order_total is calculated from the sum of all values in the item_total column. The procedure that performs this calculation must be called on three occasions:

• When data is selected from or saved to the database.

• A row of order item data is deleted from the grid widget.

• A value in the qty or price columns changes.

16. Select orditm.scr (deselect all widgets).
17. Under Focus, select the JPL Procedures property.

    The JPL edit window opens. It currently contains the screen entry procedure enter_screen, which behaves like the screen entry procedure that you implemented on the dstord.scr client screen. It receives the order identification number (order_num) from the calling screen (dstord.scr) and executes a sm_tm_command("SELECT") to fetch the specified order.

18. Scroll to the bottom of the JPL edit window and type in the upd_order_totals procedure defined below.

    proc upd_order_total()
    {
    	order_total = @sum(item_total)
    	return 0
    }

    This procedure calculates the order's total with the aggregate function @sum.

The grand total in order_total needs to be updated whenever the transaction manager performs a SELECT or SAVE command. To do this, attach a transaction manager hook function to the client screen's root table view.

19. Scroll to the bottom of the JPL edit window and insert evnt_ord_clt.jpl from tutorstd.lib. The tm_events_clt function is read into the JPL edit window:

    proc tm_events_clt(event_id)
    {
    	if (((event_id == TM_POST_SELECT) || (event_id == \
    			TM_POST_SAVE)))
    	{
    		call upd_order_total()
    	}
    	return TM_PROCEED
    }

    The tm_events_clt procedure determines whether a SELECT or SAVE transaction manager command has executed. If either condition is true, it calls the upd_order_total procedure. The TM_PROCEED return value tells the transaction manager to resume processing.

20. Save the JPL. Choose Apply.
21. Return to the orditm.scr client screen and select its root table view orders, via the DB Interactions window or Widget List.
22. Under Transaction, enter tm_events_clt in the Function property.

    The root table view now has tm_events_clt set as its hook function. The transaction manager executes this function when it starts traversing the screen's table views.

The screen wizard-generated Delete button was copied and renamed delete_order_pb on the lesson15.clt screen. Its label was changed to Delete Order and its Pixmap properties were removed. However, its behavior remains the same: it calls a wizard-generated procedure that deletes the master and related details.

To allow a user to delete a single order item instead of the entire order, modify the Delete button at the bottom of the orditm.scr client screen: rename the button and assign a new control string to invoke the appropriate procedure.

23. Select the Delete button at the bottom of the orditm.scr client screen.
    

24. Change the widget's name to delete1_pb.
25. Under Validation, set the Control String property to ^do_de lete1("title_id").

    When a user chooses the Delete push button, ^do_delete1 is called and is passed the argument title_id, the name of a widget to use in the procedure.

26. Return to the JPL edit window.
27. Scroll to the bottom and insert delete1.jpl from the tutorstd.lib library.
    

    The delete1.jpl library member is read into the JPL edit window. It contains two procedures: do_delete1, which calls delete_selected_row:

    proc do_delete1(fld)
    {
    	call delete_selected_row(fld)
    	call upd_order_total()
    	return 0
    }

    proc delete_selected_row(fld)
    vars grid_name occ
    {
    	grid_name = @widget(fld)->grid
    	occ = @widget(grid_name)->grid_current_occ
    	call sm_i_gofield (fld, occ)
    	call sm_i_doccur(fld, occ, 1)
    	return 0
    }

    do_delete1 first calls delete_selected_row. delete_selected_row deletes the selected row from the detail grid as follows:

    • Gets title_id's grid property, which returns the name of the grid in which title_id is member.

    • Gets the grid's grid_current_occ property, which returns the occurrence number of the current grid row selection.

    • Calls sm_i_doccur to delete the grid row selection.

      After the grid row is deleted, delete_selected_row returns to do_delete1, which next calls upd_order_total. This procedure recalculates the value in order_total.

      When the user saves changes to the database by choosing Save, the record in order_items that corresponds to the deleted grid row is deleted.

28. Save the JPL file. Choose Apply.

Item totals and the grand total must be recalculated whenever a value in quantity or price changes. To detect changes in either column, you need to set their Validation Func property. The function that this property specifies executes whenever an occurrence in either column loses focus—for example, the user presses TAB.

More About Widget Validation

When a widget loses focus at runtime (the user presses TAB for example), Panther calls the widget's validation function, then its exit function, and finally the automatic field function.

Validation functions are also called under the following conditions:

• As part of screen validation. Screen validation occurs when the XMIT key (for example, an OK button) is pressed or when the screen closes. At that time, all fields on the screen are validated via the function sm_s_val.

• When the application code calls library functions for field validation or screen validation.

Refer to Chapter 17, "Understanding Application Events," in Application Development Guide for more information about application events.

29. Select the Qty (qty) and Price (price) grid members.
30. Under Validation, enter valid_item_total in the Validation Func property.
    

31. Return to the JPL edit window.
32. Scroll to the bottom of the window and insert order_valid.jpl from the tutorstd.lib library.

    The order_valid.jpl library member is read into the JPL edit window and includes the procedure valid_item_total:

    proc valid_item_total(field_no, data, occ, context) 
    { 
    	item_total[occ]=price[occ] * qty[occ] 
    	if (!(context & K_SVAL) || \ 
    		(occ == @widget("Detail")->num_occurrences)) 
    	{ 
    		call upd_order_total() 
    	} 
    	return 0 
    }

More About the valid_item_total Procedure

The valid_item_total procedure updates item_total for the selected item using the expression price[occ] * qty[occ]. The if command checks the context in which the procedure is invoked. It also specifies two conditions, one of which must be satisfied to execute the if statement block, which calls upd_order_total:

• The first condition (!(context & K_SVAL—the negation of the screen validation bit K_SVAL) states that if the procedure is called on widget validation, update the order total by calling upd_order_total.

• On the other hand, if the widget is being validated as part of the screen's validation, the order total will only be updated after validating a grid member in the last row of the grid.

33. Choose Apply to save all changes in the JPL edit window.

The order_total widget is not derived from a database table so it is not included in transaction manager transactions. Therefore, when you add a new order, delete an existing one, or choose the Reset button, the content of the order_total widget doesn't clear. To clear order_total when these transaction manager events occur, you must add this widget to the screen's root table view orders.

34. Select the orders table view with the Widget List or the DB Interactions window.
35. Choose EditGroupSelect Members.
    

    All members on the screen that belong to the orders table view are selected.

36. Add the order_total single line text widget to the selection group (Shift+click).
    

37. Choose EditGroupUpdate Group Members.

    The order_total widget is now controlled by the same display styles and transaction behavior as other widgets that belong to the orders table view.

38. Save orditm.scr.

Test it out! When you go into test mode, the orditm.scr screen entry procedure executes a SELECT command and displays the first order record in the database.

39. Choose FileTest Mode (press F2) or .

    The first order record is displayed.

    

    Item totals and the order total are calculated on screen. Rows that lack quantity or price data also omit total data.

40. Click in the price field for Cinema Paradiso. Enter 20.00 and press TAB.

    The totals are immediately updated when you tab out of the field.

41. Click in the row with the ID 70 and choose .

    The row data clears and the order's total is adjusted. The total is recalculated from the client screen's current values and so does not require any database transaction.

42. Choose .

    The database is updated with the changed data in order 1001.

43. Choose .

    All fields including order_total are cleared of data.

44. Type 1003 in the order_num field and choose .

    The order associated with distributor 6 displays.

45. Click into the ID field of the first empty row. Enter 9 and press TAB.

    The video displays and the cursor advances to the qty field.

    

46. Enter 2 for the quantity, press TAB, and enter 25.00 in the price field. Press TAB again.

    The totals are immediately recalculated.

47. Choose .
48. Return to the editor to add some final touches.

To connect the orditm.scr (Order Item Detail) screen with the dstord.scr (Distributor Orders) screen created in Module 3, you must include the send_order_data procedure on the dstord.scr client screen. The send_order_data procedure calls the orditm.scr screen.

49. Open the dstord.scr client screen from client.lib.
50. With the screen selected, under Focus, select the JPL Procedures property.
51. Scroll to the bottom of the JPL edit window and insert send_order.jpl from tutorstd.lib.

    proc send_order_data() 
    { 
    	vars occ 
    	occ = Detail->grid_current_occ 
    	if (order_num[occ] == "") 
    	{ 
    		msg emsg "First select an order." 
    		return 1 
    	} 
    	send DATA order_num[occ] 
    	call sm_jwindow("(+5,+5)orditm.scr") 
    	return 0 
    }

More About the send_order_data Procedure

The send_order_data procedure is called when you double-click on a specific order on the Distributor Order screen (you implemented this behavior in Lesson 10). The data required to execute the appropriate SQL is sent and the orditm.scr screen opens.

52. Choose Apply to save the JPL procedure.
53. Save all open screens and proceed to the tutorial finale.

    ---

You enhanced the order entry screen to display totals for each item in the order and a grand total for the entire order. You did this by performing these tasks:

• Added a virtual database column to the order_items table view so the transaction manager can build a SQL SELECT statement using the appropriate math expression. As a result, when the Order Item Detail screen first opens, the total for each item in the order is tallied and displayed in the grid widget.

• Implemented field validation and screen validation to ensure that totals, for both individual order items and for the order as whole, are recalculated when an order_items record is updated, inserted, or deleted.

• Added a virtual database column to the orders table view so order_total clears when the screen is cleared of data.

What did you learn?

You learned:

• Adding a widget to a table view group allows a "virtual" widget to behave as though it belongs to a database table. It can be included in transaction manager events and behave as other database-derived widgets from the same table view behave.

• The screen-wizard Delete procedure deletes the master and its detail items. You can replace this with a delete procedure that deletes only one detail item.