AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS for version 2 Open Office database tutorials.

Open Office 2 Base (database) Tutorials-

A first table

You may find that the database being shipped with OpenOffice (ver.2) delights you as much as it has me. This page, and the others linked to it, can help you use it.

Forget any experiences you may have had with Adabas, which came with Star Office, the commercial version of Open Office 1. The Open Office Version 2 database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including Base, is free! But don't let that fool you. It's not new. Big organizations, civil and governmental, are adopting it as their standard office suite... and saving million$.

There's more about ooBase in the main index to this material.

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.

Page contents © TK Boyd, Sheepdog Software ®, 2/06.


The Plan_______________

In this tutorial, we will set up a database for keeping a personal address/ phone book. I will sketch a very, very, basic one, with just two frills. The obvious extensions are not missing because they can't be done, but just to keep the example concise.

The first frill: the user will be able to flag some records as people to whom Christmas cards should be sent.

The second frill: the user will be able to flag some phone numbers as ones which are called frequently.

The database's fields will be....

ID: A unique record identifier. It will be a number.

LastName: E.g. "Smith"

RestOfName: E.g. "Mr. Joseph E."

Addr1: First line of address, E.g. "Apt 57", or "123 Main St"

Addr2: Second line of address. E.g. "772 River Rd". For some records, it will be blank.

City: E.g. "Detroit"

State: E.g. "MI"

Phone number: E.g. 1-860-555-1212

Phone rank: This will be a number from 1 to 10. It will the user's best guess about how often he/ she is likely to need the phone number.

Cmas Card: Blank for "No Christmas card". Anything else says "Yes, this person gets a card."

The last field is being used with a little cleverness. If you wanted to put 2003 in it to say you've sent this person Christmas cards since 2003, you could. If you wanted to put R2005 in it to say you received a card from these people in 2005, you could. Etc! As far as the database I will build is concerned, though, the rule is simple: If this field is empty, don't prepare a Christmas card mailing label for this person, otherwise, do.

Executing the Plan_______________

Start Base. Typically, the Database Wizard will launch itself. If it doesn't use File | New | Database.

Choose to create a new database. Click Next.

Say "Yes, please register database for me" and "Open for editing AND Create tables". Click Finish.

Before I say more about what to do next, I want to talk about this "register" business.

First: It is NOT registering your database with anyone or anything outside of your computer. It is not giving the Open Office people access to your work.

What it IS doing is making an entry in the registry-like data structure which Open Office maintains. That data structure is GOOD in that different parts of Open Office (Writer, Spreadsheet, Base, etc) are more "aware" of things done by other parts of Open Office. It is BAD (?) in that it means you have to be a little careful about where you store things. You have to be careful and skillful, sometimes, if you want to rename or move them. Don't let me frighten you! A simple Writer document is a standalone entity, and you can move, rename, etc, its file as you see fit. Not so the files associated with Base work.

For the moment, create Base files with the right name, in the right place... i.e. Chose path and name with care, so that you won't have to move or rename them. They can be moved without tears, but we'll save that for later.

You will get a "Save As" dialog. For my work with Base, I have set up a folder called FDB ("Free Data Base") in my "My Documents" folder. Each project is given a folder within that. The project I'm building as I write this (to make sure I remember everything!) is being created in a folder called FDB004, and the database is also called FDB004. (Yes: Database called FDB004, within folder of same name.)

Work with the Save As dialog to create the folders and the database, then click Save.

The Table Wizard should launch.

Select the category "Personal", and then (from the pull down list) the sample table called "Addresses". Don't be alarmed if what we do for the next few paragraphs seems wrong... we're going to rough out something, and then clean it up.

Scroll down the available fields to "LastName", double-click on it, and you should see "LastName" appear in the right hand stringlist, which was empty until now.

Using the same procedure, add the following, in the order shown. If your version of Open Office doesn't have exactly the fields shown, just substitute something else which seems likely to be for holding a bit of text. 

FirstName
Address
Nickname
City
StateOrProvince
PhoneNumber
Title
ChildrensNames
Click Next (NOT Finish... yet... even though it is available!)

You should now be on step 2 of the Table Wizard. don't try to edit anything at this stage... you should be able to... but I had some trouble with the database creation process if I did. You can easily change things in a moment. Click Next....

... which will bring you to step 3, "Set Primary Key". The "Create a primary key" and "Automatically add.." box and radio button are probably already ticked, which is what you want. Also tick the "Auto value" box. Click Next.

In step 4, change the name of the table to NameAddrPhone ("Addresses" would do, but I like my name better), Make the radio button selection "Modify the table design." Click Finish, not Next.

You should see a string grid with familiar information in it. The first line will be a field called ID. This field is your primary key. We'll talk more about keys another time, but for now, a warning: Don't try to make changes to this one! I tried to rename it "RecID", and wrecked the auto-increment function. Almost certainly it is possible to call the field "RecID", but let's save that battle for later, shall we? "ID" will do! ("RecID" may be a reserved term.)

Change some of the field names to make the list as below. Notice that I've not put any spaces in any of the names. I think you have to name things this way. 
ID
LastName
RestOfName
Addr1
Addr2
City
StateOrProvince
PhoneNumber
PhonePriority
CmasCard
Select the "PhonePriority" field, and change the Field Type to "Small Integer[SAMLLINT]". (You do this with the drop down list that you open by clicking on the field type entry for the field you want to modify.)

Click on the "LastName" row. At the bottom of the window, you should see various things starting with "Entry required". The next one is length, and probably says 50 at the moment. Make that 35.

Adjust the length properties of the others as follows. We are setting a limit on the length of what can be put in each of the fields. (You'll have to select each row, one after the other, to access the field's length property.) 
LastName:35
RestOfName:35
Addr1:40
Addr2:40
City:30
StateOrProvince:10
PhoneNumber:30
PhonePriority: (The length can't be changed, it will be 5)
CmasCard:8
(If you leave the table editing window and return to it later, don't be alarmed if the order of the fields changes. Odd... but it seems benign.)

Click File | Close. Say "Yes" to the warning that your table has changed, do you want to save the changes? The table design window should close, and you should see a project manager window with three main panes: Database, Tasks, and a third, probably labelled "Forms" at the moment. In the pane labelled "Database" (left column) click on "Tables", and the "Forms" pane should be re-labelled "Tables", and a table called "NameAddrPhone" should appear. (If you have an extra table, called "addresses", right click on that, delete it.) Double click on the "NameAddrPhone" entry, and a window should come up with columns headed with our field names.... possibly in a bad order.

Put some data into the table! The computer should automatically fill in the ID field. If you want to delete a row, right click on the grey area just to the left of the row's "ID" column. You should have a "Delete rows" option. Click it. ("RowS" because if you can select multiple rows (shift-click, or ctrl-click) before right clicking, so you can delete many rows in one operation.)

If you delete rows, the previously assigned ID numbers do not get re-used. These numbers are for the program's internal purposes, and you are advised to pay little attention to them. They aren't even very important in our first table, but you should know how to create an ID field, and you should include one in most tables.

Concluding remarks_______________

We've done it! Congratulations!

You have a table set up which will hold contact details of your friends. That table is the foundation for as much work as you want to invest in a system that will serve you. Once the table is right, you can build on the foundation by adding forms, queries and reports to the database. We will look at these in future tutorials.

Just before finishing this tutorial, I'd like to revisit the "PhonePriority" field, about which I've so far said little.

My own list of phone numbers currently runs to nine pages. Obviously, there are many numbers in that list which I rarely call. By adding the "PhonePriority" field, and marking numbers I call often with a "1", and numbers I rarely call with a "9", I can print two phone lists: One gives just the numbers I call frequently, and the second is a complete list of all the numbers in my system.

That solution could be accomplished with a field called "ShowOnShortList", which would have a "Y" (for yes) for the numbers I wanted on the short list, and nothing, or an "N" for the numbers I want left off of the short list. My solution using a field called "PhonePriority" seems more powerful, more capable of extra features, without any attendant extra effort for the user.

One last point: It is not a good idea, in general, to work directly with your tables... but you can get away with it for now. Eventually you should learn to create forms, and use forms when you want to edit the contents of a table. When you are ready to tackle forms, this set of tutorials includes a quick introduction to forms, and a more comprehensive introduction.

Now that you have a basic database, you can return to the main menu for my ooBase tutorials, or just jump to the tutorial on printing labels, which uses this database.

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.org

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

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