@database
@node main Chapter07
 "SBase Personal -  7. DATA ENTRY VALIDATION"

@{" 7 DATA ENTRY VALIDATION " link 0}
 @{" Types of Validation " link 1}
 @{" Ranges " link 2}
 @{" Lists " link 3}
 @{" Patterns " link 4}
 @{" Cross-File LOOKUP " link 5}
 @{" Constructing a Validation Formula " link 6}
 @{" Validation Help Message " link 7}
 @{" Further Information " link 8}

@endnode
@node 0
7 DATA ENTRY VALIDATION

SBase 4 allows you to attach a validation formula to a field. The formula
may be thought of as a rule that regulates what can be entered into the
field. This allows the application designer to simplify the data entry
process for the user by automatic checking of field values against range
limits, patterns, and lists of alternatives held within the formula.
Cross-file lookup may also be used to check entered data against data
already on file. These basic rules are useful in a single file situation,
and crucial in a multi-file situation such as data entry into a multi-file
form.

Validation formulas may also be added to fields on forms. In this
situation, the validation acts as a secondary rule governing data entry
for the particular form. As you can design many different forms for every
file, you may complement the file level validations which comprise the
fundamental rules for data entry with extra rules at the form level for
different application areas or users.

Validation formulas can be created either during the file definition
process or at a later stage when you are modifying a file. See Chapter 3
Defining a New File and Chapter 5 Modifying Existing Files. The procedure
for constructing a validation formula is described further on in this
chapter.

@endnode
@node 1
Types of Validation

There are four main ways of validating data as it is entered in a record:
using a range, a list, a pattern, or a cross-file LOOKUP.

@endnode
@node 2
Ranges

This type of validation is usually applied to numeric or date fields. By
imposing an upper or lower limit, or both, it restricts data to a range of
values.

Examples are:

amount.Clients >>= 100 AND amount.Clients <<= 200

This requires the entry (into the amount field) to be between 100 and 200
inclusive.

date.Clients >> "6/1/89"

The date entered must be after June 1, 1989. Note that the interpretation
of the Day/Month/Year order of the date will depend on the system.

@endnode
@node 3
Lists

By using the logical operator OR in a validation formula, you can specify
a list of acceptable values. SBase 4 will only validate data as it is
entered if it matches one of the items in the list.

For example:

firstname.Clients LIKE "Tom" OR firstname.Clients LIKE "Jerry"

would restrict the firstname field to the values `Tom' or `Jerry'. Note
the use of LIKE instead of the `=' operator. LIKE is case insensitive and
allows you to enter text in either capital letters or lower case, or a
mixture of both.

Country.Clients = "USA" OR Country.Clients = "France" OR Country.Clients =
"England" OR Country.Clients = "Italy"

Here the formula restricts data in the Country field to four items. Unlike
the previous example, it also requires the user to enter the country in
the correct format - with the first letter capitalized.

@endnode
@node 4
Patterns

By using LIKE, you may define a formula which limits text entry to a
specified pattern. Here is a typical example:

Lastname LIKE "[A-F]*"

This formula accepts an entry of any length, provided it begins with a
letter from `A' to `F' (either case). More details on pattern matching
with LIKE are in Chapter 10 Retrieving Data Interactively and Chapter 32
Learning to Use SBase 4.

@endnode
@node 5
Cross-File LOOKUP

The LOOKUP function can be used in a validation formula to check data
entry against the contents of a field in another file.

The syntax for LOOKUP is:

LOOKUP(field1.file1,field2.file2)

field1.file1 represents the name of the field to which the validation
formula is attached, i.e., the current field. field2.file2 represents the
name of the field in another file.

When you enter data in a field which has a LOOKUP validation, SBase 4
checks whether it matches the data in any of the records in the other
file. If it doesn't, SBase 4 rejects your entry.

See Chapter 18 Linking Files for more information on the LOOKUP function.

@endnode
@node 6
Constructing a Validation Formula

You can create a validation formula for a field when you first define the
file or, at a later stage, when you are modifying the file definition.
Either way, you construct a validation formula using the File Definition
requester.

A validation formula can only be attached to a field after it has been
Added to the Field Name box (using the Add button). The reason for this is
that a validation formula contains the name of the field it validates. If
the field has not yet been defined, SBase will not recognize the field
name when you include it in a formula.

The correct procedure, therefore, is to Add the field first and then
create the formula. In effect, this means you have to define the field
first, and then edit its definition.

For example, if you wanted to create a date field with a formula attached
to it, you would need to carry out these steps first:

1.      Define the field as a date field and set the date format.
2.      Click Add - the field name will then be added to the File
        Definition panel.
3.      Click the field in the File Definition panel to select it for
        editing.

Once you have completed this preliminary task, you can proceed to the next
stage where you construct the formula and enter it in the Validation
Formula requester.

1.      Click the Validation check box. This action will present you with
        the Validation Formula requester:
2.      Build up the validation formula using the field list box, the
        operator buttons and value box. You click an operator or a field
        name to place it in the Text box; and you can add other elements
        in the formula - numbers, text, dates, etc. by typing them into
        the Value box and pressing ENTER.
3.      Alternatively, you can type the formula directly into the Text
        box. You will need to do this if the formula contains functions
        such as LOOKUP.
4.      When you've finished the formula, click OK. If you want to clear
        the formula from the requester and start again, click Clear.

@endnode
@node 7
Validation Help Messages

A validation formula sets limits to the data that can be entered in a
field. If you attempt to enter invalid data, SBase 4 will reject your data
entry. At the same time, it displays a validation error message:

 *** @{" See Fig 7-1 " system "ps sb2:Fig7-1"} ***

For this message, we've taken the validation formula which is assigned to
an Interest Rate field:

Interest Rate >>= 0 AND Interest Rate <<= 15

This formula restricts the user to values between 0 and 15.

The Validation Help feature allows you to make the message even more
explicit. Instead of displaying the field name in the first line of the
message, you can include a specific error message which tells the user
exactly why the data is invalid.

Validation Help messages are defined as part of the formula. The procedure
is as follows:

1.      Construct the validation formula, as explained earlier.
2.      Click in the text box and type ELSE at the end of the formula.
3.      Then type the help message, enclosing it in quotation marks.
4.      Click OK.

If you wanted to modify the formula for the Interest Rate field in the
example above, you would enter:

Interest Rate = 0 AND Interest Rate << 15 ELSE "Interest
Rate too high or less than zero"

Now, the validation help message will look like this:

 *** @{" See Fig 7-2 " system "ps sb2:Fig7-2"} ***

@endnode
@node 8
Further Information

Here are some useful facts about validation formulas:

  -  The maximum length of a validation formula is 255 characters.
  -  You can place field names on either side of an operator:field 1 >>
     field 2
  -  SBase 4 stops you making simple mistakes. It does this by flashing
     the screen if a validation fails.
  -  You can use parentheses to set priority levels for your formula:

amount.Clients = 10 OR ( amount.Clients >> 20 AND amount.Clients << 30 )

  -  To allow the user to create a blank Numeric or Date field, you must
     include a `zero option' in the formula:

amount.Clients >> 10 OR amount.Clients = 0

  -  The same applies to text fields, except you type "" into the Value
     Box instead of 0.
  -  If you click in the Text box, you can edit the formula directly. This
     is essential for making changes to an existing formula or for adding
     functions which are not available from the selections in the 
     requester.

See Chapter 9 Using Functions.

@ENDNODE