Sams Teach Yourself StarOffice® 5 for Linux in 24 Hours

ContentsIndex

Hour 18: Using Database Functions

Previous HourNext Hour

Sections in this Hour:

 

Using Data Validation


Data validation enables you to control what information is entered in each field of your database, and provide feedback messages as data is entered.

Note - You can use the data validation procedures outlined in this section for single cells within a spreadsheet, but using them for fields within a database-style table of information is described here.


Using data validation assumes two things:

Looking at the sample table, you can immediately see two fields that require data of a certain format:

Data validation can be used to make certain that anyone entering data in these fields follows these rules to maintain the integrity of your database table.

Setting up Data Validation Criteria

To set up criteria, or rules, for how your data must be structured, you first must select the cells to which the data validation rules are to apply.

For this example, the entire spreadsheet is dedicated to a names and addresses table, so you can select the entire column to define a field to which you want to apply a data validation rule.

1. Click on the column heading for column C to select the entire column of state information (see Figure 18.2).

Figure 18.2
With the State field selected, you can set validation criteria for that field in all records.

2. Select Validity from the Data menu. The Data Validation dialog box appears (see Figure 18.3).

3. Choose the Values tab.

4. Select Text Length from the Allow field drop-down list.

The Allow field defines what type of data can be contained in the selected cells (the States field in this case).

5. Select Equal from the Data field drop-down list.

The Data field defines how the cell entry is examined compared to a fixed value. (For example, all numbers must be greater than zero.)

6. In the Value field, enter 2.

The State field must have exactly two characters. This is shown by selecting Text Length Equal 2.

7. Before choosing OK, select the Input Help tab (see Figure 18.4).

Figure 18.3
Data validation rules and feedback are set up in the Data Validation dialog box.

Figure 18.4
The Input Help tab enables you to define a message to guide users in entering correct data.

Note - Normally, you might not use this feature for a full column of fields like this, but it is included in this example so that you can see how it works.


8. Select the checkbox for Show input help when cell is selected.

9. Enter State Code in the Title field

10. In the Input help text area, enter Use a valid two character state code.

Tip - You can use Input help in the data validation dialog box for a single cell to help someone enter a correct value to recalculate a spreadsheet or to try different scenarios.


11. Finally, select the Error Alert tab (shown in Figure 18.5). This tab defines what StarOffice is to do if the data entered in a cell doesn't match the rules you specified in the Values tab (in this case, if something besides two characters were entered).

Figure 18.5
The Error Alert tab defines how to respond to incorrect data entry.

12. Select the Show error messages when invalid values are entered checkbox. If this box is not checked, the options you define in this section of the dialog box are not active.

13. Select Stop from the Action drop-down list. Stop clears data from the cell if it doesn't match the rules set in the Values tab.

Note - You can choose Information or Warning to leave the bad data as the user entered it, after displaying a message. You have to decide how important it is to follow the data rules for your situation.


14. In the Title field, enter Invalid State Code.

15. In the Error Message field, enter The post office only accepts mail with a valid two letter State code.

16. Choose OK to close the Data Validation dialog box and apply your settings to the selected cells.

Now you can try entering some state codes and see what happens. Follow these steps:

1. Move the cell focus to the first empty cell below the list of states.

2. Notice that the yellow pop-up window describes what to enter in that cell (see Figure 18.6).

Figure 18.6
Input help can guide users as they enter data in cells.

Tip - As was mentioned earlier, Input help is better suited to a single cell. It can get annoying when you see it for every cell in the column, as it is applied here.


3. Type UT and press Enter.

This is a valid state code by the rules that were set, so it is accepted without comment and the cell focus moves down one line.

Caution - You can't use the data validation dialog box for rules such as "Only allow the following 50 state codes," but see the note at the end of this section regarding macros.


4. Now type 12345 and press Enter.

A message box appears, containing the error message that was entered (see Figure 18.7).

Figure 18.7
An error message explains to users why the data that was entered cannot be accepted.

5. Choose OK to close the message dialog box. The cell focus moves to the next cell down, but the cell data that was entered, 12345, is cleared from the cell.

You can see how useful this feature can be when you're using StarOffice for lists or tables of information. With data validation, all the information in the table is valid and useful. Typing or knowledge errors can be greatly reduced by using simple rules to control what is entered in each cell.

Data validation in StarOffice is a useful feature, but it isn't like the data validation that is available if you use a full-featured relational database.

Still, if you want to have more fine-grained control over the data validation process (for example, to check that the state code matched one of the actual codes in a list), you can create a macro program and check the data each time it is entered.

Macro is one of the options in the Error Alert tab of the Data Validation dialog box--but you're not going to learn how to write StarOffice macros during this 24 Hours.

Sams Teach Yourself StarOffice® 5 for Linux in 24 Hours

ContentsIndex

Hour 18: Using Database Functions

Previous HourNext Hour

Sections in this Hour: