dBASE 5.0 for Windows;
A Product Review
(Part 2)
Software Review by Mike Bianchi

TOC

Last month, part 1 of this review discussed some of the general features of dBASE for Windows, its user interface windows and the design and use of tables.

Part 2 covers some of the other important features of a Database Management System and how they have been implemented by dBASE for Windows.

QUERIES

A query is a question you ask about your data. The Query Designer is used to build a model of the information you want to see. The model specifies the table(s) that the data is to be drawn from, the records and fields to look at and how the data is configured.

When the query is run, a view of the data is created. A view is the specifications of the data you want to see including the tables, records, fields and format that you set up in the model. It appears in a Query Results Window that looks as if it is a table, even though the data may be drawn from several different tables. When the query is run, dBASE for Windows generates and executes the code to select and arrange the data the way you requested. If necessary, you can then return to the Design Mode to fine tune the results. The view can be used like any editing window to view the data, make selections, perform calculations or (with some restrictions) edit the data. Any editing done in the view is stored directly to the table(s) used to construct the query.

Saving the query creates a QBE file. This text file contains the dBASE commands that define the model. The QBE file does not contain the data (that is still in the tables) but rather includes instructions for:

  • the tables that are used and the relationship between them -- the USE and SET RELATION commands

  • the record order, set and filter conditions -- with such commands as ORDER TAG, INDEX, SORT, SET INDEX and SET FILTER

  • the fields that are displayed and/or calculated -- the SET FIELDS command.

The view that is created by running a query can be saved to a table. In most cases, this is not desirable because the resulting 'snapshot' will not reflect later updates in the data as you get by saving and rerunning the query. However, this can be an easy way to export the view to other applications.

Creating a Query: To create a new query enter the CREATE QUERY command in the Command Window, select File|New|Query from the menu or select the 'Untitled' Query icon from the Catalog Window or Navigator. These options open a dialog that requests you to select the table to query. Alternately, if you are already browsing the table you want, you can click the Design Query button on the speed bar or select the Table|Create Query menu option. The Query Designer Window opens presenting a skeleton of the table structure.

The Sessions setting affects what happens when working with queries. Usually these differences can be ignored but you may have specific reasons for wanting specific results. When creating a query with Sessions turned off, all open tables appear in the Query Designer. If Sessions are on when creating a query, you will always be prompted for a table to query. If Sessions are on when the query is run, the view is presented in its own Session and has no effect on the status of any other table or window; with Sessions off, all windows associated with any table are closed.

Query Design Window Figure 1: Query Design Window

Working with fields: The table skeleton (figure 1) lists the table name and each field name. Clicking on the Selection box of a field will select (check marked) or deselect the field for inclusion in the view. The Selection box under the table name selects/deselects all fields. If you want to view most of the fields, the quickest procedure is to select all fields with the record selection box then individually deselect the fields not wanted. If no fields are selected, all are included in the view by default. To rearrange the order of the fields in the view, simply drag-and-drop them to the desired position while in the Design mode.

Working with records: The simplest way to specify a record order is to use the order box in the table skeleton. This opens a box that allows the selection of ascending or descending order and, in the case of character fields, whether or not case sensitivity is to be used. More than one order box can be selected, however, two considerations must be taken into account when doing so. First, the record order is determined by the left-to-right order of the fields in the record skeleton. If the fields are not in the desired order, they can be moved as discussed above. Secondly, the view created by checking multiple order boxes (or selecting a case- insensitive option) is always read-only. If these considerations are not compatible with the task you are trying to perform, it would be better to use an index to specify the record order.

To index a view, select Query|Manage Indexes from the menu, then select Create. This opens the same dialog box as is used for indexing tables. For more details on indexing see part 1 of this review in last month's PC Alamode. Complex indexes based on more than one field create a new field in the table skeleton that is then used as the index key. To display or hide this field in the Query Designer, choose Properties|Query Window from the menu, then check or uncheck the Display Complex Index box.

The method used for record ordering in the view is determined by the following rules:

  1. If no fields are selected, records are displayed in natural order.

  2. If a single field or complex index is specified that matches an existing index, that index is used.

  3. If a single field is specified with the case sensitive option, a temporary index is created and used.

  4. In all other cases, a temporary sorted table is created and used. As with all sort orderings, this view is read-only.

Filtering: Filtering records is the process of setting a condition that determines which records will be included or excluded in the view, ususlly based on the contents of specified fields. To set a filter condition, select the space below the field name and type in the condition. Any of dBASE's relational operators can be used. For example, to select all records that are outside the state of Texas, select the state field and enter #"TX".

Some restrictions apply to the use of query conditions according to the data type of the field in question:

    CHARACTER:
    text must be delimited by quotes, single quotes or brackets. For example, to locate all records for the state of Texas, enter, "TX", 'TX', or [TX].

    NUMERIC
    enter the number exactly as it is stored, including decimal point and negative sign if appropriate. Do not include comma separators, currency symbols or other formatting characters.

    DATE
    must be delimited with braces {}. For example, to locate all records dated after January 1, 1994, enter >{1/1/94}.

    LOGICAL
    entry must be enclosed with period delimiters (.T., .t., .Y., .y., .F., .f., .N., .n.).

    MEMO
    search a memo field for a text string with the $ operator. For example, to locate entries that contain the name Jones, enter $"Jones".

    NOTE:
    The syntax for using the $ operator in a filter condition is different then when it is used as an expression. As a filter condition, $ means 'contains' and the syntax is as indicated above. When used as an expression, the syntax is reversed and the $ means 'is contained in'. For example, "Jones" $ LAST_NAME.

The LIKE operator allows searching for a character pattern with the use of wildcard characters. The '*' character can be used to replace any number (including none) of characters. For example, to locate all records in area code 210, enter Like "(210)*" as the filter condition in the phone field. The '?' character represents any one character. Entering Like "J??n" will match 'John' or 'Jean' but not 'Jan'.

More than one condition can be set to filter the records included in the view. An AND condition is when two or more conditions are set, all of which must be met (in the same record) for the match to be successful. AND conditions are specified by entering all conditions on the same line in the table skeleton; conditions applied to the same field are entered in the same box, separated with commas. For example, to locate dates in 1994, enter >{12/31/93},<{1/1/95} under the appropriate field.

An OR condition occurs when two or more conditions are specified such that only one need be true. OR conditions are specified on separate lines in the table skeleton. Use the down arrow key to open a new line. To specify records in Texas or Oklahoma, enter "TX", hit the down arrow then enter "OK".

Any number of AND and OR conditions can be combined in a single query as long as the combinations are meaningful to the table being viewed. To locate records in Athens, GA or Paris, IL, enter the following:

field: CITY STATE (comment)
"Athens" "GA" -excludes Athens, Greece
"Paris" "IL" -excludes Paris, France

The Exclusive OR condition (usually called XOR) is not available as a filter condition. This is the case where two or more conditions are specified such that any one (but ONLY one) can be true. This restriction can be worked around by using a condition specification similar to the example given above. If fields A and B are logical fields, for example, the following will set the filter to records in which only one of the fields is true:

field: A B
.T. .F.
.F. .T.

This example can be extended to any number of fields by including one line for each field and setting one field in each line to .T. and all others in that line to .F.

Filter conditions can also be based on calculated fields or comparisons of field values.

Condition Box Figure 2: Condition Box

More complex filter conditions can be specified by using the Condition Box (figure 2). This box is accessed through the Query|Add Condition menu selection. The Condition Box accepts any standard dBASE expression. Although you can still use the down arrow to access additional lines, this is usually not necessary because an entire complex condition may be written as a single expression. It is also possible, using the Condition Box, to set a condition based on more than one field, for example, SHIP_DATE - ORDER_DATE > 10.

Using multiple tables: The true power of a relational database system is in the ability to link multiple tables. To create a query on multiple tables, open the Query Designer as outlined above then use the Add Table button from the speedbar or select Query|Add Table to select additional tables. A table skeleton for each table appears in the Query Designer Window. Creating a relationship between the tables involves identification of the common field that links each pair of tables. The table that the link is created from is called the Parent Table, this should usually be the table that determines the record order. The table that the link is created to is called the Child Table.

Define Relation Window Figure 3: Define Relation Window

To create a link between two tables, drag the mouse pointer from the link icon of the parent table skeleton to the link icon of the child table. The Define Relation dialog box opens. dBASE for Windows will attempt to automatically identify the linking fields based on the fieldnames of the parent and the index keys of the child. The linking field in the child table must be indexed, if it is not, you can use the Add Index button. Three check boxes in the dialog allow specifying various link options that affect what records will appear in the view

    Every Parent
    When checked, all records in the parent table will be included in the view. When unchecked, only parent records that have one or more corresponding records in the child table will be included.

    One to Many
    If this option is checked, every child record is included in the view. If unchecked, only the first child record of each parent is included.

    Enforce Integrity
    This option specifies the result of actions that would interfere with the parent-child relationship. When this option is selected, deleting a parent record activates a dialog that allows the deletion to be cascaded, that is, all associated child records are deleted at the same time. When a child record is added, it is automatically given the key value of the current parent record and this value is read-only. This prevents the existence of an 'orphan' child record that is not linked to any parent record.

The relationship between two tables can also be defined using an expression. To do this, type the expression or use the Expression Builder tool associated with the Define Relation dialog. This can be helpful, for example, when a code is embedded in another code such as when the first 3 digits of an invoice number represents the store location code. This is also handy if the data types do not match between the two fields, for example, if the zip code is entered as a numeric field.

Occasionally, the view created by the query is the desired end result of creating the query, that is, the reason for creating the query is to see the view in the Query Results window. Most of the time, however, the query is used as the starting point for the creation of a Form or Report.

FORMS

Form Designer Figure 4: Form Designer

The Form is the primary component of the user interface. It is the screen that the user sees and interacts with. The form can be as simple as a screen to read a record or a complete user interface for a complex set of applications. dBASE for Windows' Form Designer is a graphical interface that allows you to create complex forms visually, by clicking and dragging objects on the screen, with little or no programming. To create a form, you start with an empty Form Window. You then select objects from the control palette and place them at the desired position on the form, then use the Object Inspector to set the properties of the object. It's that simple.

The form and each item (called controls) placed in it are all separate objects. Each object has its own unique set or properties that determine its appearance and behavior. These considerations will be discussed in detail in the final installment of this review next month. For now it is sufficient to be aware of the existence of these concepts.

The Form Designer itself consists of the window, grid and rulers. The window can be moved and sized like any window; as you do, however, the form properties that control the size and location of the window are automatically adjusted. The grid (a matrix of dots) and the horizontal and vertical rulers are used for aligning controls on the form. The size of the grid and ruler unit is determined by the characteristics of the current font. By default, the vertical unit is the same as the font height and the horizontal unit is the average character width of the selected font. The coarseness of the grid can be adjusted through the Form Designer Properties dialog box that is accessed through the Properties|Form Designer menu selection. You can also remove the display of the grid or rulers through this box, as well as turn off the snap-to-grid feature (on by default). The snap-to-grid is a feature that automatically aligns objects to the nearest grid position when it is placed on the form.

To open the Object Inspector select View|Object Properties from the menu. The Inspector window is divided into three tabbed pages: Properties, Events, and Methods. Methods are subroutines associated with an object that perform an action on the object, such as opening or closing a form. Events are methods that execute automatically in response to an occurrence. For example, the Pushbutton control has an event called 'OnClick' that defines what happens when the user clicks the mouse on the button. Clicking on an event in the Object Inspector opens a text box next to it. To add or change an event, simply type the name of the procedure or function into the box or type a codeblock. A codeblock is a command or a short sequence of commands that is associated directly with the event. Procedures, functions and codeblocks will be discussed further next month. Changing the value of properties is even easier as many of them offer a menu or dialog box for the selection of acceptable values. The combo box at the top of the Object Inspector can be used to select the properties of a different object.

Adding an object to the form is accomplished by clicking the object in the control palette. The mouse pointer changes to a crosshair. Clicking at the desired position will insert the control of the default size or dragging the pointer over the area will resize the control as it is inserted. The pointer then returns to its default shape. If you want to insert several controls of the same type, unselecting 'Revert to Pointer' in the Form Designer Properties dialog box will allow the mouse pointer to remain in the crosshair form until you select the pointer option from the control palette. While the pointer is in the crosshair form, the selected control will be inserted everywhere you click the mouse.

In order to use the form to read or edit data you have to tell it what data to look at. The View property of the form is used to link the form to a table or query. Controls that are associated with fields, such as entry boxes, checkboxes etc. each have a property called DataLink that is used to link it to the appropriate field.

By default, a form window conforms to the Windows MDI standard that allows multiple windows to be open or the same document to be open more than once. MDI windows are movable and sizeable and have a window title, control menu box, Maximize and Minimize buttons. The default MDI settings can be overridden by setting the form's 'MDI' property to False. This results in a modal window that takes control of the user interface and the user cannot switch to another window without exiting the current window, like in the case of a dBASE IV window. Modal windows are most appropriate for use as dialog boxes where the user is required to take a specific action before being allowed to continue.

Form Navigation: An object receives focus when the user selects it by tabbing to it or clicking on it with the mouse. By default, the order in which controls receive focus when tabbed is the same order as they are placed in the form. Windows refers to this as the tabbing order, dBASE for Windows uses the term z-order. You can change this z-order, if desired, to a sequence that may be more efficient for data-entry.

Certain properties of controls can be used to determine if and when the object can receive focus. If the 'TabStop' property is set to False, using the Tab key will skip to the next control in the sequence but it can be selected with the mouse. This can be handy for a field that is seldom used. If 'Enabled' is False the object cannot receive focus and appears dimmed on the screen. When 'Visible' is set to False the object is not displayed on the screen and is not available to the user. The 'When' property specifies a condition that must be true before the object can receive focus, for example, the entry field for spouse's name can receive focus only if MARRIED is True. These properties can be set to default values through the Object Inspector or can be set dynamically in a program.

In DOS versions of dBASE, the Enter key is used to move to the next field in a data entry form. Windows applications (including dBASE for Windows) use the Enter key to activate the default pushbutton. You can override the Windows standard to enable the Enter key to simulate the behavior of older dBASE versions by unchecking the box labeled 'CUA Enter ' in the Data Entry page of the Desktop Properties dialog box. Similarly, when an entry field is filled with data, focus automatically moves to the next control in the tabbing order. To override this feature and require the user to press 'Enter' before moving to the next field (equivalent to the SET CONFIRM ON command of earlier dBASE versions), check the 'Confirm' box in the Data Entry page of the Desktop Properties dialog. This setting will be ignored, however, if the 'CUA Enter' setting is checked.

REPORTS

Unlike earlier DOS versions, dBASE for Windows does not include a Report Generator, but rather ships with a third party product called Crystal Reports from Crystal Computer Services, Inc. in Vancouver, Canada. This product requires separate registration.

Since Crystal Reports is a separate product from dBASE for Windows, I am not going to spend a lot of time on it. I will say that as easy to use and well documented that dBASE for Windows is, Crystal Reports is NOT.

For creation of simple reports, its easy enough to drag-and-drop fields from a table or query into the Report Design Window. You can insert text or formulae into your report by dragging them from the Text Editor or Expression Builder (discussed below). It is just as easy to group data based on the changing value of a field or expression or to add counts, subtotals and other summary data to each group.

Any more sophisticated formatting then that is not so easy. As a test case, I created (attempted) a report that would list our membership grouped according to the marketing campaign that they were recruited from. It took about 20 minutes to build, including building the query that the data was drawn from. However, the group name was listed on every line. To get the group heading to not repeat on each line took about 3 hours of trial and error. Then it was put ONLY at the beginning of each group; if the group was large enough to span more than one page, the heading was not put on the next page. This resulted in several (most, in this example) pages that were blank on one side of the page and having to flip back several pages to see what group I was looking at. After more fiddling, I decided it was not worth the effort trying to get the group headings on each page or trying to insert a page break between the groups. Nowhere in the printed or online documentation are these procedures explained and to call Tech Support involves the added expense of an international phone call. These tasks are common requirements and should be available and documented. In the past, most of my database report generation has been done through programming, and it looks like it still will be.

CONCLUSION

In the first two parts of this review, I have discussed some (not all) of the more important features of dBASE for Windows, what they do and how they are used. Next month the final installment will look at programming considerations and the adaptations of the xBase language to object oriented applications and the Windows environment.

Part 1
Part 2
Part 3