Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Interactive Paradox Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


Building Tables
Part 2: Validity Checks and Table Lookup
By: Liz Woodhouse
ParadoxCommunity.com

Introduction

In the previous section, we discussed the importance of tables as the foundation of your database, and also the files which make up a Paradox® table and the field types available. We also discussed field naming and some recommended uses for various field types.

In this article, we'll continue exploring the table structure dialog, by discussing Validity Checks and Table Lookup. Both of these features help to ensure data integrity, can simplify the development process, and cannot be replaced by code or user interface features at the table level. And while I recommend you not allow tables to be accessed directly, but instead design forms for this purpose, new users frequently edit tables directly until they're comfortable with moving to the next level, so these features can be especially important for beginners.


Validity Checks

Validity checks are an easy way to ensure that data entered into your table conforms to some basic rules. Paradox's validity checks are: required, minimum value, maximum value, default value, and picture. All of these validity checks apply to the following field types: Alpha, BCD, Currency, Date, LongInteger, Number, Short (integer), Time and Timestamp. Other field types are mentioned under the specific validity checks which apply to them.

The Required validity check is the only validity check which applies to all field types. When a field is marked as required, the user will not be allowed to depart a modified record without entering a value in the required field.

Minimum value and Maximum value define the range of data allowed in a field. For most field types, the limits are obvious: the impact of setting a minimum value of 1 in an integer, number or currency field is obvious - no value less than 1 would be allowed; a minimum value of 1:00PM in a time field would indicate no time before 1:00PM could be entered. However, for Alpha fields, it's not always so obvious: is A greater than, less than or the same as a? The answer depends on your table language (to be discussed later). For example, with the table language Paradox 'ascii', A is less than a, but with the table language Pdox ANSI Intl, A and a are equal. Whenever there is any doubt, you should thoroughly test your table with sample data to see how the validity checks behave.

NOTES: Both of these checks are enabled for logical fields, but trying to save the table with one or both of these validity checks specified will produce an error. Minimum value also applies to autoincrement fields and specifies the value for the first record inserted into the table.

Default value specifies the value which will automatically be placed in the field when a new record is created. I recommend that logical fields always have a default value, because Paradox often treats a blank value in logical fields the same as False, thus making it difficult to distinguish between "not yet assigned" and False. If you need to distinguish between records where a field is not assigned or has been assigned one of two possible values, use a single-character Alpha field with a picture (more later) instead of a logical field.

The Picture validity check allows you to assign a format for the data to be entered into a field. For example, you could specify that the first character entered into a field be capitalized, or that the data entered into a field must match the typical US telephone number format of (999) 999-9999. New users often have difficulty learning how to create their own pictures, but study of the help file topics and the standard pictures included with Paradox, and requesting assistance on the newsgroups will help. I won't go into further detail here as a discussion of pictures deserves a separate article.


Table Lookup

Table lookup allows you to limit the data in a field to values found in another table. There are generally two purposes for this:
  1. To specify a specific list of valid values for a field
  2. To ensure that a value cannot be used in a field until it has been used in another table
At first, it may seem that these two uses are the same, and from a technical standpoint, they are, however, logically, they are different. The following examples should help distinguish between the two.

The first purpose, specifying a list of valid values, might apply to a country field in an address table. You don't need the country to have been used elsewhere in your database for it to be valid, but you do want to ensure that only valid country names are entered and that they're entered in the same way each time. So, you create a country table (the lookup table), enter all valid country names into the table. Then, in your address table, you specify that the country field must use a value from the country table.

The second purpose, ensuring a value cannot be used unless it has first been used elsewhere, might apply to a lineitem table in an order entry database. You don't want to allow the user to enter a product which isn't listed in your products table since presumably, the products table lists all of the products available for purchase.

When table lookup is specified, and the user presses Ctrl+Spacebar while in edit mode, a dialog is opened showing the content of the lookup table so that the user can select a valid value. The user may also key in a valid value, but it must match a value in the lookup table exactly. You can also create your own custom lookup dialog (click here for a sample by Steve Caple), or you can simulate table lookup in code rather than using the table structure (click here for details).

Table lookup is extremely flexible in that you can edit the list of valid values without the need to either restructure a table or redeliver a form. For that reason, you may wish to consider using table lookup in place of the following alternatives:
Pictures
Pictures are better for when a format is what's important rather than for specifying valid values, though both are possible. If you use a picture to limit the valid values for a field, and then need to change those values or add to them, you must restructure the table.

Hard-coding the valid values into a user interface
This should be avoided at all cost. Hard-coded values are difficult to locate and keep track of, when changes are needed, code must be changed and source redelivered.

Combo and list field objects
These are good for short simple lists, but not for situations where the user needs to see more than one field of data (such as selecting from a company list where they might want to double check the address or phone number before selecting the company) or for long lists where the user might prefer to see more than a few options at a time.

Combo and list fields can also be used in combination with table lookup to ensure the user doesn't type in an invalid value (with combo fields), and the lookup table can be used to populate the list, rather than entering all the values in the field's properties dialog (which would also require the form to be redelivered if the list needed to be changed).
It's important to note that when defining table lookup, aliases are not stored in the table structure. Therefore, one of two things will be stored: the lookup table's filename or the lookup tables path and filename. Because storing the path of a file makes it difficult and inconvenient to move tables (to a new computer, onto a network, installed at a client site), I strongly recommend you use the following steps to establish table lookup and ensure that only the filename is stored.
  1. Ensure both tables involved in the relationship are in the same directory
  2. Set that directory as the "Working Directory" (:WORK:)
  3. Restructure the data table and pick the lookup table from the list of tables in :WORK: without selecting any other alias or directory first
Following these steps will ensure that only the lookup table's filename is stored, thus allowing complete flexibility when moving tables (just be sure to move all linked tables at the same time).


Summary

Proper use of validity checks and table lookup can help you ensure data integrity and will simplify and speed up development by eliminating the need for complex code.


Part 3: Table Language, Table Level and Passwords


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.