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 the Browser 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 the select
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.