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

Open Office ooBase Tutorials

A first table

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, 2/06-10/12.


The Plan_______________

In this tutorial, we will set up a database for keeping a personal addressbook, including telephone numbers. 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.

This tutorial is aimed at novices. As such, not all of your options will be mentioned. If you have some ooBase experience, you may know of other ways of doing things, which may be perfectly acceptable alternatives to the ways suggested here.

In various forms, this tutorial has been on the web since February 2006. At some point, with ooBase 3.0, I went through the instructions from scratch, using a machine with Windows XP. They were tidied up, but everything basically "worked". That process was repeated again in September 2011 with ooBase 3.3.0, again using Windows XP (SP3). So things really should work!

I will try to avoid distracting frills. The first I can't resist is that the user will be able to flag some records(ShrtDef) as people to whom a Christmas card should be sent, while other people in the address list will be Christmas- card- less.

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

Some "tricks" are possible in your use of the last field. 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.

Database wiazard screen

Choose to create a new database. Click Next.

Say "Yes, please register database for me" and "Open for editing" AND Create tables using the table wizard". (You have to tick a box to make that last request.) Click the "Finish" button.

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. This is not as true for the files associated with Base work, but the problems won't affect you until you are doing more advanced things. Don't worry about them. (Similar problems may arise if you do advanced things with ooWriter, ooCalc, etc files, and are cavalier about renaming or moving them. Still: don't worry!)

For the moment, create Base files with good names, in the right places... i.e. Chose path and name with care, so that you won't have to move or rename them. They can be moved without too many tears, but let's try to avoid creating any need to move things, for now.

You will get a "Save As" dialog. (If you don't know it already, you should be delighted to learn that you can create new folders and move around the file structure of your backing store from within the Save As dialog. Careful use of folders is one pillar of support for computer users' sanity.)

For my work with Base, I long ago set up a folder called FDB ("Free Data Base") in my "My Documents" folder. While you are getting started, it will probably pay you to create a folder within that for each project, although this extra level of nesting isn't really unnecessary. The project I'm building as I write this is being created in a folder called FDB004. We will also call the database we are creating "FDB004". (Yes: Database called FDB004, within a folder of the same name.)

Work with the Save As dialog to create the folders. Fill in the "File Name" edit box with FDB004, and click the "Save" button.

The Table Wizard should launch.

Table Wizard

(If you don't get the Table Wizard immediately, you didn't tick the box I told you to... naughty you. Just click "Tables" in the left column of the screen you did get, then "Use Wizard to Create Table" in the upper right panel to get back onto the page the rest of us are on.)

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 start by merely roughing something out, 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.

A few comments about field names....

These are the fields to add...

FirstName
Address
Nickname
City
StateOrProvince
PhoneNumber
Title
ChildrenNames

That should bring you to...

Table Wizard, after user input

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 here at this stage... you should be able to... but long ago, with earlier versions of ooBase, I had some trouble with the database creation process if I did. You can easily change things in a moment. Click the "Next" button....

... 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 the "Next" button.

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." In a moment, you will click the "Finish" button. (The "Next" button is grayed out, anyway, at this point.)

But first... and this has tripped many readers... be sure to click on the "Modify The Table Design" radio button. Once you've done that, click the "Finish" button.

You should see....

-

If you did get that... Well done! We'll say what to do with it in a moment.

Maybe you got...

-

That would happen if you didn't see the instruction to select "Modify The Table Design". No problem. You are looking at the main ooBase project manager window.. something important we will come back to often. To get to where you ought to be...

One way or another, you should now be seeing 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! With an older version of ooBase, 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. By the time you are finished, the list should be as below. Notice that I've not put any spaces in any of the names. I think you have to name things this way. (If you really, really "need" a space, use an underscore: Like_This. Works. But a Bad Idea.)

ID
LastName
RestOfName
Addr1
Addr2
City
StateOrProvince
PhoneNumber
PhonePriority
CmasCard

Select the "Field Type" column on the "PhonePriority" field's line. 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 says "Your table has changed, do you want to save the changes?" The table design window should close, and you should see the main ooBase project manager window...

-

... which probably won't look quite like that at the moment. Click on the "Forms" icon in the left hand pane, if you want your screen to look exactly like the above.

The main ooBase main project manager window is something you will become very familiar with. It is your usual "point of entry" to any ooBase work. You got wizards the first time you launched ooBase, but once you are "up and running", you will open at the ooBase main project manager window. I've done a little page about the ooBase main project manager window, if you want to review the basics.

In the ooBase main project manager window, in the pane labeled "Database" (left column) click on the "Tables" icon, if that is not already selected. The lower right hand pane should be labeled "Tables", and a table called "NameAddrPhone" should be listed. (If you have an extra table, called "addresses", right click on it, delete it.) Double click on the "NameAddrPhone" entry, and a window should come up with columns headed with our field names.... possibly not in your original order....

Empty ooBase addressbook database table

It won't be as easy as it will be, and it isn't the right way to do it, but just to see that you can, put some data into the table! The computer should automatically fill in the ID field with a unique number for each record. If you want to delete a row, right click on the gray area just to the left of the row's "ID" column. The list of options you get should include "Delete rows". 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. Forms are the right way to put data into a table... and they are easily configured to make the job more convenient. Other tutorials in this series show you how.

Just before finishing this tutorial, let me tell you about 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.

In closing

One more important 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 look at or 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 discussion.


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 the database you just created.



Editorial Philosophy

I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.

I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Details at my page about how the material is split up and how it is posted.

Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....

Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using the material for inmate education. Situations do exist where good internet connections are not possible!)

Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)



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. Mostly passes. There were two "unknown attributes" in the 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 . . . . .