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
Use the following steps to perform a simple join:
The bulletin board should be about 3 inches high by 4 inches wide.
Add the fields from the Invoice table to the application. Later, you will add the account information.
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.
Place them in the upper left corner. Leave room to add the account information later.
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.
To display and set the necessary resources for this application:
- 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.
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.
where Resource Edited to Create the Join
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.
accounts_name
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.
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.
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:
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.
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.
You will build upon this newly created interface in Tutorial 3: Master-detail Display and Entry Form Application. Write out the .uil
file for the interface and save it so you can use it for Tutorial 3 at your leisure.
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.
Note: The default path displayed at the top of the dialog is the path of the directory from which you started Database Xcessory.
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.
uil
.uil
, which can be read back into Database Xcessory to reconstruct the collection.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 select
statement by:
select
statement.from
clause in theselect
statement.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.