AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS for Open Office database tutorials.
Delicious.Com Bookmark this on Delicious     StumbleUpon.Com Recommend toStumbleUpon

Open Office 2 Base (database) Tutorials
Data Validation. ListBox. ComboBox.

You may find that the database included in OpenOffice delights you as much as it has me. This page tries to help you use it.

Remember that Open Office, including ooBase, is free. Don't let that fool you, though. Big organizations, governmental and civilian, have adopted it as their standard office suite... and saving million$, but still Getting The Job Done. And making things easy for users on different platforms... Linux, Mac, Windows all spoken here!

There's more about ooBase in the main index to this material. Adabas? Star Office? Ancient history.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is fully explained, and there are more tips, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software, 3/06- 10/12.

Data validation: Overview

Many times there are only a limited number of valid entries for a given field in a table you have created. For example, if you have collected data on a group of people, and you have a reason to want to know each person's gender, then you'll have a field which should only hold "male" or "female", or some other pair of codes, e.g. "m"/"f".

Continuing with our example: It would not give a human any trouble if sometimes you recorded an individual as "male" and other times you recorded him as "man". Computers do not thrive on inconsistency. It would be best if you used one and only one code for each gender throughout the database. Table constraints are an important tool you can use to guard against invalid entries. They are discussed elsewhere. In this essay, we explore other tools.

"Data validation" is something for which you should strive. A given datum may be wrong, i.e. you enter "female" for Santa Claus, but as long as you've used the right code, the data is valid. To put it another way, a datum is valid if you have used one of the allowed values for the field.

The "male"/"female" example is a nice simple one. Data validation can be more subtle. Take, for example, US Social Security numbers. Every US taxpayer has an ID assigned by the government. A typical SSN is 044-44-8365. An individual's SSN always consists of three digits followed by a hyphen followed by two digits followed by a hyphen followed by four digits.

Whether you are dealing with simple cases (like "m"/"f") or more complex ones (SSNs), databases provide tools to validate data as it is entered, so that invalid data can be excluded from your tables.

This tutorial provides answers for the simple cases. I'm still trying to master validating things like SSNs!

In this tutorial we will pretend that we've been given the job of keeping track of the names of the children attending a certain prestigious boarding school where every child is a member of one of the following "houses", (i.e. internal units of the school): Ravenclaw, Gryffindor, Hufflepuff or Slytherin

We'll build a little table, and provide a data entry form which won't allow us to enter an invalid value for the child's house. We'll first do this by using a ListBox. We'll look at the shortcomings of a ListBox, and try an alternative, the ComboBox. We'll look at its shortcomings, and then go on to a more advanced solution to the problem: the setting up of constraints on the table. I'm afraid that the tutorial ends with an appeal to more competent "Base" (ooBase) programmers for some help with a few problems which I have not yet solved.


By the way.... Don't get carried away with data validation. It might be tempting to put a validation rule on the postal (Zip) code in a database of addresses. The rule could say that the code must consist of 5 digits. Fine... until you try to add someone to your database who doesn't live in the US! We've all been annoyed by dealings with firms whose computers "can't" deal with something in our lives because their database doesn't allow the data to be entered correctly for our circumstances. This is often due to badly designed data validation. Don't add to the problem!

Data validation: To provide it...

Create the database.

Create a table in it. Call the table HSL. (For "Hogwart's School List")

In it, provide the following fields:

ID integer, auto value, primary key
Name, text, length 30
House, text, 3

Create a form. You might use the wizard, or the alternative method. (But how appropriate to use the "wizard", for this one!)

You only need a very basic form. Include all fields, use "datasheet" arrangement. Call it HSLwLookup for "Hogwart's School List With Lookup". Exit the wizard after selecting "Modify table" selected as the next task.

Right-click on the "House" column heading.

Choose "replace with..." and choose ListBox. (It will not be offered if the field is already set up as a ListBox, by the way.)

Right-click on "House" column heading again.

Choose "Column...", and the ListBox's property editor should open up.

Click on the "General" tab, and enter the following in the List Entries field:

rav... then type shift/ enter to go to the next line in the box,
gry... then type shift/ enter to go to the next line in the box,
huf... then type shift/ enter to go to the next line in the box,
sly... and then (you don't need to close the ListEntries data entry box)
.......... but do save what you've done.

In the List Entries box, you should see...


(It seems that you can't simply enter the values in this format; you have to put them in the box that opens when you click on the down arrow of the "List entries" box. You shift/ enter to get new lines so that each value is on its own line.)

(A detail: You could, of course, in this case, simply use "r","g","h" and "s" to indicate the different houses. Notice I did not propose "R" or "Rav".... Do you want to fool with the shift key? The data will be buried inside the database most of the time. If you want "rav" presented differently in output from the database, you can tell the computer to do that work.)

Use the floating toolbar associated with the form to leave design mode. (If the toolbar is "missing", click on the form. The toolbar's title is "Form controls", and the ruler/ pencil/ set-square button toggles the design mode on and off.

You should now find that when you enter data in the school list, clicking in the "House" field gives you a ListBox, with rav, gry, huf and sly as your choices.

You should find that you can't enter anything else... but (at the moment!) you can leave the field blank.

Close the form. We're going to be working on the table which underlies the form, and you are asking for trouble if you change what is underlying the form while the form is open. We will close the form, change the table, and then re-open the form. At the time the form re-opens, it can check the table to see how things stand.

Open the table ("HSL") for editing. Change the Entry Required property of the House field to Yes.

Close the table.

Reopen the form, for editing, i.e. in design mode. (If you just open the form, you won't be able to switch to design mode.)

Use the toolbar to switch out of design mode.

Now if you attempt to leave a record without entering a value for the pupil's house, you'll get a message saying "Error writing to database. Input required in field 'House'..."

Who says error messages are always obscure! After you acknowledge the message you'll be positioned to correct the problem. A detail: when you create a new record, even if the ListBox is already showing the value you want, you still have to open it and click on that value to enter the value in your table.

A ListBox can also be opened (dropped down) with the Alt- and DownArrow key, and then navigated with the down and up arrow keys, of course. (My thanks to RGB for writing in with that useful tip.)

I am not a fan of making fields obligatory. Take the case of the house field for the school list. It may be that someone is trying to get the names of some new pupils entered into the system before their houses have been determined. (Certainly at Hogwart's this would be necessary, given the way houses are assigned there!) Even if you do know everyone's house in time, you may prefer to do your data entry by going down the table, filling in the names, and then go back through the data assigning houses in a second pass through the data. Neither approach is possible if you've made the house field a required entry.

By all means restrict the values which can go into a field, but often you need to allow users to leave it empty. It is easy enough to set up a query to list all records with no value in a given field. Continuing the school list example, the data entry people could enter things as they became available. Just before they printed any report which needed a value in the house field, they could run the query to be sure everyone had been assigned to a house.

In a related vein: Many times you will have something that can be true or false. It may pay you to add a "Don't know" option. (I code such fields "t"/"f"/"?" to save typing.) Again, consider the school list: Suppose you want to know if the child lives with his or her biological parents. (Not necessarily the school's business, and dangerous to gather on a list... but it makes a good example of the sort of data that goes in the sort of field I want to discuss.) If you restrict that field to "true" or "false", then what do you do when you don't know the child's circumstances? If you leave the field blank in such cases, how do you distinguish the records that are blank because you don't know from the records that are blank because you've forgotten to enter the data? Bottom line: "Don't know" can be a useful option. If you have a "Don't know", the blank fields are the ones you've forgotten to fill. If appropriate, you can set up the table to force you to enter something in every record.

In other circumstances, "Misc" is a valuable option. If, using "Misc", you find that your database has accumulated too many "Misc" records, you can look at them and define new categories to take some records out of the "Misc" category.

Data validation: An alternative: the ComboBox

UGLY EDIT begins....

Oh dear... some of the following is wrong, rubbish! I will try to get back to this page and clean up for you. Sorry. Let me explain what is wrong...

It is wrong that you need to use a ComboBox if you want to let your users enter data by typing. In the example as presented above, using a ListBox, it is perfectly okay for a user to type any of the valid values into the House field.

In fact, Base is very clever... if you had houses axy, bxy, caa, cab, and ccc, then if you typed "a", you would get house axy. Type b and you get house bxy, but type c, and you'll get one of the "c" houses. Type cc, and you'll get ccc (the only one starting cc). Type ca, and you'll get one of caa and cab. Type the final letter (if necessary) to get the one you want.

ListBoxes will not let you enter something that isn't provided for in the ListEntries property. The ListEntries list cannot be "contaminated", as would be possible if you use a ComboBox control (see below). You don't need to get clever, use a constraint. (See below).

Sorry for mistakes! Sorry for Ugly Edit!


Put the form back into design mode.

First I'll tell you what to do. In a moment I'll explain what you've done.

Change the House field to a ComboBox type field. All you need to do is right-click on the "House" field, choose "Replace with...", and click on "ComboBox".

Why? (See "Ugly Edit" above, if you haven't already read it.) I've asked you to do this to show you a close cousin of the ListBox. A ComboBox is like a ListBox... you get the pull-down as before. You may even find that the ListEntries are intact. (If not, re-enter them). However, with a ComboBox, you can also enter things by hand. Now, in the Hogwart's example this would never be necessary- all of the valid entries are provided for, Indeed, you could argue that a ListBox is better, because it prevents a user from entering an invalid value. (I've yet to find the right keys to control it from the keyboard. Answers on a postcard (or in an email) welcome! I have part of the answer: If you are in the House field, you can cycle through the different possible entries with ctrl/ down (or up) arrow.) How hard is it to type three letters?! The combo box allows "sly", say, to be entered either by typing it by hand (or, indeed, just the "s". If you have several "s" choices, you still only need to type whatever it takes to narrow things down to the one you want), or by picking it from the list.

You might say, "Well, yes, I can see that.. but I don't want to lose the data validation strengths of the ListBox." (Remember: The ComboBox lets you enter anything in the field.) (See "Ugly Edit" above, if you haven't already read it.) Happily, there's an answer....

Good news!

This tutorial used to be much longer! Now it is shorter. (That's the good news!)

I had a long section here about adding constraint to tables. I still think they are a good idea... more important that what you've read so far, in fact. But I have split the old version of the page you are reading and created a separate page all about using constraints to protect the data in a field from invalid entries.


ARGGH! I was in the final stages of testing the database this tutorial is based on and I discovered a major pain: If you enter an invalid value into the ComboBox, even if you have the constraint in place, that invalid value becomes an option in the pulldown list of the ComboBox. Again... help from others on how to prevent this nuisance would be welcomed. (To save you trying what I did in desperation: Making the ComboBox read-only does what you'd expect, rather than what I hoped it might: It makes it impossible to change the field's value in a record. Of course being able to do what it does is valuable.... but there must be something else you can do to "lock" the list of options presented by the ListBox part of the ComboBox?)

Here's the link to my eddress one more time, to make helping everyone easy for you!

Editorial Philosophy

I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....

I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.

Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE

If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.

Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgMostly passes. There were two "unknown attributes" in Google+ button code. Sigh.

One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .