Building a Master/Detail Page

How to develop a master/detail application that uses data views on a page.

About this technique	Details
Category	HTML Client Techniques> HTML-based views
Description	You'll learn about setting up the user interface: Specifying the master Orders data view Specifying the Order Details data view Specifying the total fields and programming the events: Navigating to the first record Navigating to the previous record Navigating to the next record Navigating to the last record Initiating find mode Completing the find request Performing the Click event to bring up details
	You can run this technique code from: Database: Examples3_HTML Page: pgCompaniesOrders.html NOTE   First make sure that database is running on your localhost SilverStream Server
Related reading	See the chapter on using HTML-based views in the Programmer's Guide

In the sample application, you can browse through a database of companies and see the orders for each one. When you click on a particular order, the order details appear on the screen. The application page is bound to a database table called companies (the primary table for the page). It contains the company names and addresses.

The techniques in this page show how to set up the user interface portion of the application to make the master/detail feature work, as well as how to code the buttons and event link.

Specifying the master Orders data view   

This section describes how the Orders data view is set up to serve as the master table. The important properties to note in the Property Inspector are:

• Table name. The data view is bound to the "orders" database table, which is specified in the Table Name property. The table for the master data view has a column that matches a column in the primary database table.

• Link clause. A link clause for the data view relates these two columns. For example, the following clause limits the data view to include only the orders whose customerid matches the companyid of the current company:

    companies.companyid = orders.customerid  
  

• Order by. The order-by clause for the data view sorts the displayed data. The following clause sorts the entries by descending order dates:

    orders.orderdate Desc  
  

• Expression. The first label control inside the data view allows the user to click to bring up more details about the order. The Expression property for the label creates the text that will appear in this column. The following expression displays the orderid:

    orders.orderid  
  

• Type. In order to trigger an event when the user clicks the orderid, the type for this label is set to Event Link. The corresponding event is the eventLinkPerformed event, which is explained in a following section.

Specifying the Order Details data view   

This section describes how the Order Details data view is set up to serve as the details table. When the user clicks an item in the master Orders data view, details about the order appear in this data view. The important features to note about the Details data view are:

• The data view is bound to the "orderdetails" database table, which is specified in the Table Name property.

• This data view has no Link clause that would automatically connect it to the primary database table. Instead, this data view is populated when an event is triggered by the user clicking on a label in the master data view.

• The data in this data view is cleared out whenever the user navigates to another record in the database, since the order details would no longer apply to the new record.

Specifying the total fields   

This section describes the two fields that display the shipping costs and grand total for the Order Details data view.

• The two fields are label controls. They are not part of either data view. They are simply label controls that have been added to the page, placed inside an HTML table to align them properly.

• The type of both labels is set to Label in the Property Inspector. The text to display in the controls is calculated and set programmatically at runtime.

Navigating to the first record   

The following code illustrates how to navigate to the first record in the primary database table. Extra lines are added to clear the Order Details data view and the calculated total fields, since the order details would no longer apply to the newly displayed record.

  // pageActionPerformed event for First button 
  agData.gotoFirst(); 
  dvOrderDetails.query("orderdetails.orderid=-1"); 
  shippingcosts.setText(""); 
  grandtotal.setText(""); 

Notes about the code

• The instance variable agData handles the primary database table.

• The code calls gotoFirst() to move to the first record.

• The Order Details data view is cleared of data by calling query() on it, setting the orderid key to -1.

• The two calculated label control fields are cleared by calling setText() with an empty string.

Navigating to the previous record   

The following code illustrates how to navigate to the previous record in the primary database table. Extra lines are added to clear the Order Details data view and the calculated total fields, since the order details would no longer apply to the newly displayed record.

  // pageActionPerformed event for Previous button 
  agData.gotoPrevious(); 
  dvOrderDetails.query("orderdetails.orderid=-1"); 
  shippingcosts.setText(""); 
  grandtotal.setText(""); 

Notes about the code

• The instance variable agData handles the primary database table.

• The code calls gotoPrevious() to move to the previous record.

• The Order Details data view is cleared of data by calling query() on it, setting the orderid key to -1.

• The two calculated label control fields are cleared by calling setText() with an empty string.

Navigating to the next record   

The following code illustrates how to navigate to the next record in the primary database table. Extra lines are added to clear the Order Details data view and the calculated total fields, since the order details would no longer apply to the newly displayed record.

  // pageActionPerformed event for Next button 
  agData.gotoNext(); 
  dvOrderDetails.query("orderdetails.orderid=-1"); 
  shippingcosts.setText(""); 
  grandtotal.setText(""); 

Notes about the code

• The instance variable agData handles the primary database table.

• The code calls gotoNext() to move to the next record.

• The Order Details data view is cleared of data by calling query() on it, setting the orderid key to -1.

• The two calculated label control fields are cleared by calling setText() with an empty string.

Navigating to the last record   

The following code illustrates how to navigate to the last record in the primary database table. Extra lines are added to clear the Order Details data view and the calculated total fields, since the order details would no longer apply to the newly displayed record.

  // pageActionPerformed event for Last button 
  agData.gotoLast(); 
  dvOrderDetails.query("orderdetails.orderid=-1"); 
  shippingcosts.setText(""); 
  grandtotal.setText(""); 

Notes about the code

• The instance variable agData handles the primary database table.

• The code calls gotoLast() to move to the last record.

• The Order Details data view is cleared of data by calling query() on it, setting the orderid key to -1.

• The two calculated label control fields are cleared by calling setText() with an empty string.

Initiating find mode   

The following code illustrates how to prepare the page for the user to find a particular record in the database table. The code clears the main database fields to allow the user to enter search parameters. The user completes the operation by clicking the Find! button.

  // pageActionPerformed event for Find Mode button 
  agDataMgr.beginFind(); 
  AgiRowCursor rc = dvOrders.getRootRowCursor(); 
  if (rc != null) 
  { 
     while (rc.gotoFirst()) 
     { 
        rc.delete(); 
     } 
  } 
  dvOrders.scrollToFirstRow(); 
   
  dvOrderDetails.query("orderdetails.orderid=-1"); 
  shippingcosts.setText(""); 
  grandtotal.setText(""); 

Notes about the code

• The agDataMgr instance variable handles find operations on a database.

• When the Find mode button is clicked, the code calls the beginFind() method, which clears the main database fields and accepts subsequent user entries as search parameters.

• The master Orders data view is cleared out, since the user will be navigating to another company record. First, a row cursor variable of type AgiRowCursor is created and assigned to the root of the data, which is obtained by calling getRootRowCursor().

• The new row cursor variable traverses through all the rows in the data view and deletes them from the data view display. They are not removed from the database table itself.

• The Orders data view scrolling is reset to the first row with the scrollToFirstRow() method.

• Next, the Order Details data view is cleared of data by calling query() on it, setting the orderid key to -1.

• Finally, the two calculated label control fields are cleared by calling setText() with an empty string.

Completing the find request   

The following code illustrates how to complete the process of finding a particular record in the database table. The user will have already clicked the Find Mode button and entered any search parameters.

  // pageActionPerformed event for Find button 
  agDataMgr.doFind(); 

Notes about the code

• The agDataMgr instance variable handles find operations on a database.

• When the button is clicked, the code calls the doFind() method, which brings up a record that matches the search parameters.

Performing the Click event to bring up details   

The following code illustrates how to get details on the order whose row the user clicks. It uses both the Orders and OrderDetails views as well as the orders, orderdetails, and products database tables to obtain and compute the required data.

  // eventLinkPerformed event for orderid label in Orders data view 
  Integer ordID = new Integer(0); 
  BigDecimal total = new BigDecimal(0);               // running total 
  BigDecimal one = new BigDecimal(1);               // the constant one 
  BigDecimal unitprice;               // column unitprice 
  Integer qty;               // column quantity 
  BigDecimal bqty;            // qty converted to BigDecimal 
  BigDecimal discount;               // column discount 
  BigDecimal price;               // calculated item total 
  BigDecimal freight;               // shipping costs 
   
  AgiRowCursor ord = dvOrders.getCurrentRowCursor(); 
  if (ord != null) 
  { 
     ordID = (Integer) ord.getProperty("orders.orderid"); 
     dvOrderDetails.query("orderdetails.orderid=" 
        + ordID.toString()); 
   
     // Loop thru obtaining all the data 
     AgiRowCursor det = dvOrderDetails.getRootRowCursor(); 
     for (boolean gotNextRow = det.gotoFirst(); gotNextRow; 
        gotNextRow = det.gotoNext())                
     { 
        // Obtain the values for this row. 
        unitprice = (BigDecimal) det.getProperty( 
           "products.unitprice"); 
        qty = (Integer) det.getProperty("orderdetails.quantity"); 
        bqty = new BigDecimal(qty.intValue()); 
        discount = (BigDecimal) det.getProperty( 
           "orderdetails.discount"); 
        // Formula is unitprice * quantity * (1 - discount) 
        price = unitprice.multiply(bqty).multiply( 
           one.subtract(discount)); 
        total = total.add(price);             
     } 
   
     // Add in freight if not zero 
     freight = (BigDecimal) ord.getProperty("orders.freight"); 
     if (freight != null) 
        total = total.add(freight); 
     try 
     { 
        // Set the value to 2 digits of precision 
        freight = freight.setScale(2, BigDecimal.ROUND_HALF_DOWN); 
        total = total.setScale(2,BigDecimal.ROUND_HALF_DOWN); 
     } 
     catch (Exception e) {} 
   
     shippingcosts.setText(AgFormat.format(freight,"#,##0.00")); 
     grandtotal.setText(AgFormat.format(total,"#,##0.00")); 
  } 

Notes about the code

• Get the row cursor for the selected row in the Orders view by calling dvOrders.getCurrentRowCursor().

• Obtain the order identifier from the orderid column in the orders table.

• Using the OrderDetails view panel, send a query for records in the orderdetails table by calling dvOrderDetails.query() and supplying the obtained id as the parameter. The response to the query is contained in dvOrderDetails.

• Each order may consist of multiple products. Each row in dvOrderDetails represents a product (sub)order. Use getRootRowCursor() to get the data object representing the data in the view. This method returns a row cursor.

• Call gotoChild() on the row cursor to get to the first row. The for loop goes through each row (represented by a product) to get the unit price, quantity, and discount information for that product.

• To get unit price information, the getProperty() method obtains its value from the products database table.

• To get quantity and discount information, the getProperty() method obtains its data from the orderdetails database table.

• Update the subtotal each time after getting the information on a row.

• After computing the subtotal of all rows, get the freight charges from the orders database table. Note that the getProperty() method is called on the Orders (dvOrders)view. ord is the row cursor for that view.

• Display the freight charges and total cost by calling setText() on the appropriate labels. Both are AgpLabel objects in the Order Details data view.