![]() |
![]() |
|
![]() |
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:
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:
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. ![]() |
![]() |
|