Getting Started-2-Tier

Lesson 12. Calculating Data from Database Values

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:

  1. If necessary;
  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:

Add a column to the grid widget

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.

  1. Give focus to the orditm.scr client screen.
  2. 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).

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

Define a currency format

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

  1. 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.

Define a math expression

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.

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

    Related subproperties are displayed.

  2. 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.

Add the widget to a table view

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.

  1. 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.

  1. 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.

  2. Choose EditGroupSelect Members.

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

  3. Add the item_total (Total) grid member to the selection set:
  4. 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.

  5. 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:

Calculate results

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:

  1. Select orditm.scr (deselect all widgets).
  2. 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.

  3. 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.

Update totals on transaction manager events

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.

  1. 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 == \
    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.

  2. Save the JPL. Choose Apply.
  3. Return to the orditm.scr client screen and select its root table view orders, via the DB Interactions window or Widget List.
  4. 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.

Delete a detail record

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.

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

  2. Change the widget's name to delete1_pb.
  3. 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.

  4. Return to the JPL edit window.
  5. 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:

  6. Save the JPL file. Choose Apply.

Validate client data

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:

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

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

  3. Return to the JPL edit window.
  4. 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:

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

Clearing data in a virtual field

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.

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

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

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

  4. 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.

  5. Save orditm.scr.

Update a detail record

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.

  1. 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.

  2. 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.

  3. 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.

  4. Choose .

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

  5. Choose .

    All fields including order_total are cleared of data.

  6. Type 1003 in the order_num field and choose .

    The order associated with distributor 6 displays.

  7. 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.

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

    The totals are immediately recalculated.

  9. Choose .
  10. Return to the editor to add some final touches.

Connect two screens

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.

  1. Open the dstord.scr client screen from client.lib.
  2. With the screen selected, under Focus, select the JPL Procedures property.
  3. 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.

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

What did you do?

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:

What did you learn?

You learned: