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.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.
LibreOffice? Open Office? This link will take you to a sidebar about the similarities and differences, the issues.
Whether you are using OpenOffice or LibreOffice, I think you are wise, and hope that my tutorials will be equally useful to you.
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- 12/18.
This essay first appeared in 2006. (That explains why some illustrations are dated.) I have tried to be responsive to comments over the years.
In December 2018, I am working through the essay, trying the concepts discussed in a freshly installed OpenOfiice 4.1.6 environment, on a Windows 10 (Sigh. Ich.) machine. (And tweaking the essay.) qFIX if you encounter THIS note after 28 Dec 18, please write (link at bottom) say "Fix 'qFIX if you encounter' in fdb1listb.htm". Thank you!
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 assign each person to a traditional gender, then you'll have a field which should only hold "male" or "female", or some other pair of codes, e.g. "m"/"f". Often, you may wish to know that, but have no legitimate need to. consider adding further options... "not recorded", "other".
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. (Note the subtle differences... it is easy to get confused between the options.)
"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. (Well, it was. Apologies to any reader who would rather I didn't say that... I DO realise that for some it is NOT a simple issue. My thanks to the team who produced the excellent TV drama "Butterfly" for helping me learn more. (It could have been cheap and dumb, but it wasn't.))
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 . Note that: "invalid data can be excluded from your tables". Sounds worth the trouble, when you put it like that, doesn't it?
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 small 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 USA! 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!
Create the database. Of course, the name isn't set in stone, but while "working the example", I called it FDB1LISTB_18c20. ("Free Data Base, List Boxes (etc) (name of webpage, too), yymdd, where m is 1-9, a-c for Jan-Sept, Oct-Dec) (Put all connected with it in a folder, also with that name. "Registered" the database.)
Create a table in it. (I'd suggest using the Design view approach.) We will call the table "HSL". (For "Hogwart's School List")
In it, provide the following fields:
Name/ Type/ Etc...
ID, integer, auto value, primary key Name, text[VARCHAR], length 30 House, text[VARCHAR], 3
Decide now: At 12/18, it seems the next thing must be decided NOW. (When I tried to change the relevant setting later in the process, I was blocked. There should be a way around that, but I don't know what it is at the moment. Just Do It: "it" being "decide now": Do you... and there are pros and cons... want to REQUIRE an entry in the "House" field at all times? If so, set the field's "Entry Required" property to True... now. But if you aren't sure you want to make it obligatory to make an entry, don't do it.)
Close the table design tool. There's no need to open the table.
Next, create a form. You might use the wizard, or the alternative method. But how appropriate it would be, Harry, for this one, to use the "wizard"... so we'll do that...
You only need a very basic form. No sub-form needed.Include all fields, use "data sheet" arrangement. Set the form up "to display all data". (Don't dis-allow any modification. I went with the default "style". Call it HSLwLookup for "Hogwart's School List With Lookup". Selecting "Modify the form" as the way you wish to proceed, and "finish", i.e. exit the wizard.
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..." (not "Insert", or "Delete" or "Hide column"!), and the ListBox's property editor should open up.
Click on the "General" tab, and type the following three letter codes into the "List Entries" field, thus:
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 press enter, to close the ListEntries data entry box)
In the List Entries box, you should see...
"rav";"gry";"huf";"sly"
(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.)
(There's no need to close the window displaying our shiny new form... but this would be a good time to save what you've done so far.)
Close the dialog we were just using, the "Properties: List Box" dialog.
(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, say, "rav" presented differently in output from the database, you can tell the computer to do that. Keep what's in the datbase, the underlying data, simple.)
In "the real world" it is usually wise to have a "mis" (for "Miscellaneous", too. You'd be surprised how often somethning "impossible" turns out to be necessary. If your data, when up to date, shouldn't have any pupils in the "mis" house, you can do a search to check. I've done real school lists in the real world. Sometimes we had a new pupil, and we didn't know what house he/ she would be in a the time the lists went to "press" (This was in the olden days of ink on paper. Poor management, and not a kind start for the kid at the schoo... but the data entry team is not always the data generation team.)
Use the toolbar associated with the form to leave design mode. The ruler/ pencil/ set-square button... circled in illustration above... takes you in and out of "design mode". (The tool bar may be floating, as above, or docked. If the toolbar is "missing", use "View/ Toolbars". The right toolbar's title says "Form controls".)
When you've left the form design mode, you should find a row of "boxes" showing under the headers we just specified. The first one will have "<AutoField>" in it, although you may only see part of that. Are we on the same page? If so... onward...
You should now be able to enter a name in the "name" field of the first record of the HSL table. (Click in the field's box, type... or use the tab key and/or arrow keys to move around the form.)
You can't enter anything in the first field, because we said it should be filled for us by the database... and it will be, as soon as we enter data someplace else for the record concerned.)
Moving into the "House" field will give rise to a downward pointing arrow, ("v") at the right hand end of the field. Clicking on that opens up the ListBox, with rav, gry, huf and sly as your choices. Click on one to make it the value for that field for that record.
Alternatively, once you are in the "House" field's cell on the grid, you can just type one of the valid entries. (Base will even try to help you, in a manner similar to predictive text.)
You should find that you can't... with the form... enter anything other than "rav","gry","huf" or "sly". (If the field hasn't been set up as one for which you must enter some data, you can leave the field blank, if you don't enter it, just go on, enter a further record. But you can't enter anything other than "rav","gry","huf" or "sly". And once something is entered, you can't go back to nothing.)
Base's is cleverness, that little bit similar to predictive texting works like this: 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. (Because it behaves this way, unless you have a strong reason for having codes with shared first letters, I would choose abbreviations which avoid that.)
Open Office Base is a serious DBMS. As such it has various features to control data entry.
There is, for instance, a way to say that when a new record is being added to the database, some or all of the fields must have data in them. (And if you are using constraints, not only must there be data, but the datum provided must be from a designer-determined list of all possible answsers.
Building your database to work that way can have advantates... and it can be a nuisance.
Unfortunately, turning "require entry" on and off seems to be a little tricky.
First of all: You should do it by editing the properties of the table. There is a "Input required" property on the data tab when, in Form Edit, you call up the List Boxs's properties. But changing that, there, doesn't seem to do what I would expect.
If you specify "data required" right at the outset, when you are setting up the table, things seem to work okay.
It may even be safe... it worked in one minor test... to turn OFF "require entry", as long as you do it at the level of the table.
Turning it on, either for the first time, or to return to a previous state, seems to be more difficult... if not even sometimes impossible. Be warned!
"The secret" may be as follows. You can (perhaps) switch "require entry" back on, if, at the present time, every record has a (valid) entry in the field in question. (Logical, when you think about it.)
----------------------------------------------------
Besides doing things with the mouse, you can also open (drop down) a ListBox with Alt-DownArrow. Once the list has been opened, you can go up and down it with the arrow keys. Press enter when you have the right house selected. (My thanks to RGB for writing in with that useful tip.)
Fine... you know a bit about what you can and cannot do.
Now let's think about what it is wise to want to do.
I am not a fan of making entering data for 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 entry required for the house field.
By all means restrict the values which can go into a field! This essay was written to help you do that. But often you need to allow users to leave it empty, even if they are doing so only temporarily. 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....
a) the records that are blank because you don't know
....from....
b) 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. As discussed previously, when it is 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.
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). (However, if you use a ComboBox, you have other tools to prevent invalid entries... use a constraint. (See even further below).
The "ComboBox"... similar to ListBox.
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". (The "built in" values we established earlier for the box's List Entries will persist.)
Why?
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. However, with a ComboBox, you can also enter things by hand. Now, in the Hogwart's example this wouldn't normally 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.
Personally, I haven't yet come to love the ComboBox. For one thing, it "accumulates" the things users enter. Purging bad stuff can be done, but it is a pain.
If, however, the fact that the ComboBox lets you enter anything in the field is important to you, there's still a way to have data validation...
I had a long section here about. I think that adding constraint to tables is a good idea... more important that the tricks you've learned about so far, in fact.
I have a separate page for you, all about using constraints to protect the data in a field from invalid entries.
====
I was in the final stages of testing using ComboBoxes when I discovered a major pain: If you enter a previously unused value into the ComboBox, even if you have a 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!
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.
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.
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.
. . . . . P a g e . . . E n d s . . . . .