Note: We recommend that you print this tutorial for easy reference. To print, click on the right-hand frame before accessing your HTML Browser's File/Print command.

In this tutorial you will develop a form that has fields from two tables. A join is often necessary when there are fields from more than one table that will be displayed on the same form. In this case, you will be joining the Invoice table and the Accounts table.

Accounts are organized by a unique integer key, Account_Number. The Invoice table could store all the account information (such as the name, address, city, state, and zip code), but there is no reason to duplicate this data since it is already in the Accounts table. The Invoice table has eight fields. Only one of these shares information with the Accounts table. This field is the Account_Number field.

The Application You Will Create using a Simple Join

Creating the Application

Oracle users

• Because Oracle is case-insensitive, the Schema Browser renders all names uniformly in uppercase.

Performing a simple join

Use the following steps to perform a simple join:

• Create a bulletin board widget to serve as the parent widget for window.

The bulletin board should be about 3 inches high by 4 inches wide.

• Select the OrderEntry database from the Database list (Sybase) or DBPak from the Owner list (Oracle) on the Schema Browser.
• Select the Invoice table from the Tables list on the Schema Browser.

Add the fields from the Invoice table to the application. Later, you will add the account information.

• Select the Invoice_Number and the Account_Number field in the Columns list on the Schema Browser.

Since the two fields are next to each other in the list, click on the Invoice_Number field and drag down to the Account_Number field to select both fields at the same time.

• UsingMB2, drag the two selected fields from the Schema Browser to the upper left corner of bulletin board you have already created.

Place them in the upper left corner. Leave room to add the account information later.

• Select the Accounts table from the Tables list on the Schema Browser.
• Select the Name, Address, City, State, and Zip fields from the Accounts table.
• Drag all five fields from the Schema Browser to the bulletin board.
• Place them to the right of the invoice number.

If the Accounts information is not aligned with the Invoice, you can move them by selecting all the Accounts widgets and dragging them to a location of your choice. Hold down the Shift key and drag a rectangle with MB1 around all the Account fields to select all the widgets. Move them together by holding down the Ctrl key, positioning the pointer over one of the selected widgets, and dragging MB1 until the group is properly aligned.

When selecting a group of fields, if you inadvertently select the background container widget, you cannot move the group of widgets. Deselect the container by holding down Ctrl and click MB1on the container (the easiest way is to deselect the container object on theBrowser hierarchy). The other objects in the group remain selected, and you can now move the group.

Setting Resources

To display and set the necessary resources for this application:

• In the Browser, select q_Invoice (the query widget for the invoice fields) by clicking MB1 on the q_Invoice widget in the Browser tree.
• To display its resources, update the Resource Editor window using one of the following methods:

  • Click on the Update icon located in the tool bar below the Resource Editor menu bar.
  • Double-click on the widget to list the widget's resources and their settings in a scrollable window at the bottom of the Resource Editor.

Note: All database-related resources are grouped at the end of the list to help differentiate them from the appearance-setting resources.

• Find the tables resource, and enter "from" in the Find Resource field at the bottom of the Resource Editor.

Note: This resource was set automatically to both the Invoice and Accounts table when you dragged fields from both tables into the application. Although it is not necessary to supply the value, you must use qualified names in the data fields XmNexpression resources to allow DBPak to determine from which tables to obtain data.

• Find the where resource:

where Resource Edited to Create the Join

• Modify the where resource on the query (q_Invoice) to create the join. The correct value is: "Invoice.Account_Number = Accounts.Account_Number".

After typing the value into the text input field, be sure to either press Enter or click on the OK button that is displayed to the right of the input field.

The text entered into the where resource is copied into the where clause that is part of the SQL statement that is constructed and sent to the database server. By specifying the join conditions, the server will return data from both the Invoice table and the Accounts table, where the Invoice table's account number matches the Accounts table account number.

• In the Browser, select:

accounts_name

• Update the XmNqbeOperator resource to XiDB_QBE_USER_DEFINED. Refer to "XmNqbeOperator" in DX Hyperhelp for more information.
• To complete the interface, add a control panel at the bottom of the form.

Drag the Control Panel from the DBPak Controls group on the Palette. If you have any questions about how to do this, see Adding a Control Panel to the Application.

Testing the Interface

Refer to Testing the Application in Play Mode for information about testing your interface.

When you enter Play Mode, you can use the control panel widgets to navigate through the data. Start by pressing Search, and notice that the Account information updates together with the Invoice information. The where clause keeps them synchronized.

Using Query by Example (QBE)

You can restrict the set of values shown when browsing through displayed data by using a technique called Query by Example.

Suppose you are looking for an invoice from a customer, but you have forgotten the customer's name. You know the customer's name begins with the letter "R".

To find the customer's name using QBE, use the following steps:

• Enter Play Mode.
• If necessary, click the Stop button on the control panel to enter QBE Mode.
• Enter "R%" into the Name field.

By default, the interface is in QBE Mode. If you enter data into any of the data fields and then initiate a search, that data will be interpreted as search strings to restrict the rows that will be returned by the server.

The "R%" represents all names that have an R as the first character and any number of other characters following. The percent character (%) is an SQL wild-card character that matches any string.

DBPak contains many capabilities for controlling database queries. For example, if you want this field to always search for values that begin with what was entered (without the user being required to enter the% operator), then you can set the Data Presentation widgets's XmNqbeOperator resource to XiDB_QBE_BEGINS_WITH. Refer to "XmNqbeOperator"in DX Hyperhelp.

• Click on the Search button on the control panel.

You can now browse through all the invoices restricted to those having a customer name starting with the letter "R".

You can change the search parameters by clicking on the Stop button in the control panel. This places the interface back in QBE mode and you can then enter a new search string.

Writing Out and Saving the UIL File

You will build upon this newly created interface in Tutorial 3: Master-detail Display and Entry Form Application. Write out the .uilfile for the interface and save it so you can use it for Tutorial 3 at your leisure.

Writing and saving the .uil file

First, make a directory in which to save tutorial .uil files. The following steps describe how to write and save the .uil file for an interface.

• Return to Build Mode.
• Select Save As... from the File menu on the Browser to display the File Selection dialog.

Note: The default path displayed at the top of the dialog is the path of the directory from which you started Database Xcessory.

• Change the directory path in the Filter input field at the top of the File Selection dialog.

Either enter the full path name of the directory in which you want to save the .uil file or use the list of current directory contents to navigate to the desired directory.

• Click on the Filter button at the bottom of the File Selection dialog.
• Enter the name of the .uil file you are writing out in the File Name input field at the bottom of the File Selection dialog.
• Click on the OK button to write out the file uil.uil, which can be read back into Database Xcessory to reconstruct the collection.

Summary of Tutorial 2

You created an interface with two tables joined together by setting a single resource on the query widget. Database Xcessory did the rest automatically. When you press the Search button, the Query widget (to which the Search button points) builds an SQL selectstatement by:

• Finding all Data Presentation widgets (in this case, data fields) and using their expression values to create the expression list of the select statement.
• Using the XmNfrom resource and any table qualifications in the Data Presentation widgets to build the from clause in theselect statement.
• Taking the where resource (and other query resources) to build the remainder of the select statement.

The query widget then sends the select statement to the server.