HOW TO USE THE OPENOFFICE 1.0 DATABASE ENGINE

(3/31/06 -- This was originally written about OpenOffice 1.0. OO 2.0 revisited the database engine and brought a lot of improvements. There is a specific database "startup" link (as opposed to starting OOwriter) that links directly to the databases and engine, with also some good wizards for building databases. There is also a native database system. I used to build my databases in dbase because I had been using dbase/foxbase/foxpro for years. However, the OO system is very useable unless you need to access the database with some other program that doesn't recognize it.)

(I also wanted to mention a couple of things I recently noticed. First, it always annoyed me that my OO forms had the wordprocess menus, ruler, etc. around them. Turns out that if you shut the menus, toolbars, etc. off and save the form that way, then the form displays in the same "clean" form you stored it. The second thing is something that I haven't figured out yet -- OO 2.0 seems to save forms generated via the database "desktop" as part of the "database." That is, I don't seem to be able to link the "form" so I can start directly with the form. The form can also be stored externally with a "Save As" command, but the one time I tried to do this my form lost its database links. This hasn't been an important issue to me, so I haven't figured out the solution, but I imagine I will get around to it one of these days -- it's pretty convenient to click on a link to a form and have the form come up without going through some other OO interface. -- My old forms and links from OO 1.0 still work, btw.

I'm sorry that my write-up below makes only limited sense. I'm kind of disorganized. If you have a specific question, you can write me and maybe I can help (or maybe not.) )

StarOffice 5.2 had a nice database engine and you thought it had disappeared, right? Oops, you have no idea what I am talking about, so ...

(My apologies if this is too basic and all you want to know is, "where the heck are the commands." I'll try to get to that in a bit, so bear with me.) Or, the Quick Summary.

What is a Database?

A lot of Office product users don't understand the concept and power of a database. Combined with a programming language, it is sort of like the mega-penultimate spreadsheet. But then, of course, you would have to learn all that programming stuff, so why bother with databases? Leave that for banks, insurance companies, places with full time IT Managers. Stick with a spreadsheet program and a wordprocessor.

In the "old days," that was probably a good idea if you didn't want to learn a bunch of programming stuff. Databases were extremely useful, but difficult to access and manipulate. Of course, most of us use at least a couple of "database" programs--the Calendar and the Addressbook. Someone else has done all the "programming" for these two specialized database tasks and we are happy to use them.

But an interesting thing has happened while you were avoiding learning about databases. They got very user-friendly. I guess that is because computer programmers have mega-need to manage their mp3 collection, their video collection, their software database ....

Think of it this way. A database is just a quick way to keep track of stuff. It's sort of like writing down notes on scraps of paper, throwing them in a drawer. So wouldn't it be great if we had a powerful way of retrieving those notes under any possible scenario?

Well, somebody at OpenOffice.org must love databases, because they made that possible. Maybe some programmer did it just for his own personal use -- I don't know, but I do know they didn't mention it was there or how to use it.

(Oh, well, they did leave behind a few clues, such as the "bibliography database" and the ability to import your "addressbook database.")

So, let's take a look at what this mystery programmer put in there for us.

1. Addressbook. Boring... Like, how many addressbook programs are there in the world? And this one is not even implemented. Forget it, I'm using Evolution ...

Well, hold on a minute. Wouldn't it be great if you could put your address book at the top of the page and drag names and addresses right onto your text page as you need them? When you need to make a phone call in the middle of writing something, there it is, ready to give you the phone number. When someone calls looking for an address for a friend ... well, you get the picture. If you are nodding your head 'cause this would be useful, let's import your addressbook, wherever it is.

Open OpenOffice Writer. (It works in Calc, too, if you like spreadsheets better.) File>Autopilot>Address Data Source Now you have a wizard to connect to your existing Addressbook. You have three choices:
1. Mozilla/Netscape 6.x
2. LDAP Address Data Source
3. Other External Data Source


Okay--I wish I could remember what I meant to say here ...
I guess I will just move on and come back to it later.

---


2. Abacus/Lawin -- Let me digress a bit. This is an example of how one might work with an existing database. A lot of lawyers use a client database/calendaring/file tracking/timekeeping system called "Abacus". So let's say you have all this data tied up in Abacus and you want to access it with your Linux computer. Semi-solution? OpenOffice.
   
   Abacus keeps its data in "dbf" files. These are readily accessed with the OpenOffice database engine. So what do we do?
   
   First, we need to make sure we have access to the file directory where Abacus exists. If you are using a dual boot computer, we will need to "mount" the Windows partition. If the files are on a different computer, we will need to connect to that computer and mount the drive in our Linux tree. (These are separate topics that I won't address here).
   
   So, let's assume we have access to the Abacus files. We can see them in our directory tree. Let's go get 'em.
   
   Click on the "Data Sources" icon. It's a little square icon and when you put the cursor over it, the "tool tips" pop-up will identify it for you. In the upper part of the screen we now have a list of databases. Right click in the left-most window. Select "Administrate Data Sources".
   
   Now we have the Database Sources dialogue screen. Choose "Add New Database" to obtain a blank screen.
   
   Make sure you select the "General" tab. This one should be showing anyway. Name your new database, eg. "Abacus Law".
   
   For the database type, select "dbase" and then to fill in the URL, browse to the Abacus directory. If we click on the "tables" tab, we should now get a list of all of the dbf tables in the Abacus directory. Eg., "law1.dbf, law2.dbf," etc.
   
   Save this. Now, our new database "Abacus Law" is available in the "tree" in the left side of the upper part of the screen. Click on Abacus Law and then on "tables" to expand the tree. Click on "law1."
   
   Magically, we now have the entire Abacus addressbook on the screen in the upper right. What can we do with it? Well:
   
   
1. We can scroll through the entire file.  We can scroll right and left to see the entire record.
   
   
2. We can sort on any column.  For instance, if we click on "LASTN" and then click on the "A to Z" sort icon, our addressbook is now sorted on our clients' last names.
   
   
3. We can do a more specialized sort by clicking on the black Z-A sort icon.  This will give us a dialogue in which we can, for instance, sort on "Class" then on "Lastn" then on "Firstn".  As a result, our addressbook is now sorted as follows (1) all "clients" are listed together, followed by all "vendors"; (2) within "clients," our clients are sorted by Last Name and then by First Name.
   
   
4. We can use a Filter.  One "automatic filter" icon will simply find everything that is the same as the currently selected field.  Is your cursor on "Jones"?  Then the filter will eliminate every client not name "Jones."  To undo it, just click on the "filter on/off" icon.  But, we can also use a specific filter and this is a great tool because it is much faster than trying to run a search -- which, by the way, we can't do right now anyway.  Click on the "default filter" icon.  Now you can specify criteria.
   
   Note:  There is a feature found in Outlook that is not found in Abacus, but I recommend adding it.  In Abacus, you can add an additional field.  Add one called "Cate" (for "categories") and make it about 150 characters long.  We will use this the same as we use "Class," but we can assign individuals to more than one category.  For instance, if a particular attorney is our adversary in both "Jones" and "Smith" cases, then we would enter "jones,smith."  Later, we will be able to sort to find either "jones" or "smith" and the attorney will show up in either list.  These avoids making multiple entries.
   
   
5. We can create queries.  Why?  Because queries are more useful.  A query is just a subset of the table.  For instance, we can select just the name and phone number columns for a compact phone list.  To create queries, right click on the "Queries" entry in the tree at the left and enter your new query.
   
   
6. We can use autopilot and create nifty forms.  So, for example, let's do this:  Create a query that includes only client name, and the clients' phone numbers.  This is a useful list.  But let's make it more useful.  Using File>autopilot>form, let's build a form.  We will select our query as the database source.  Then we will select one of the "block" data forms.  Now what do we have?  Well, in the lower half of our screen we have a convenient form that shows the name and phone numbers for one client.  We can scroll through this list, we can filter it >and< we can use the binoculars icon to "find" any bit of data anywhere in our data list.  We can add and delete data.  This is an enormously useful tool.  Say you want to find "Jonesy" but you are not sure whether it is in "last name" or in another field?  Just search for "Jone".  You will find every instance that contains that search term.
   
   Now, also, you can click an icon that will put the form data as a list in the upper screen.  So, now we have a "query" format -- a scrollable list of our phone database -- in the upper screen, and a nice "single entry" screen in our lower screen.  Are we having fun yet?
   
   
3. Why this isn't a "complete" ABACUS solution.  Well, there are a couple of issues.  They have solutions, but I can't describe them to you yet.  But the two issues are:  (1) we aren't linking the "notes" and "documents" databases to the "address" database, the way Abacus does.  Yes, we can read them and find stuff, but they don't conveniently scroll and appear together.
   
   The other significant problem is the calendar.  Most calendars are going to a standardized, text-based calendaring system so that you can share calendar entries via the internet or your palm pilot with other people in your working group.  In this instance, for example, attorneys in a case could coordinate their deposition calendars.  But alas, Abacus chooses not to follow this standard.
   
   No, the Abacus calendar is a dbf file.  Of course, it is readable, but there is a tiny annoying problem:  Abacus stores its "time" data in "minutes."  That means that my "8:30 a.m. hearing" will look like "510" in OpenOffice.
   
   
   
4. The Form Tool.  If you don't use the "AutoPilot," you can use the Form Tool to build your forms and now you can make them very complicated if you choose to learn OpenOffice Basic.  You see, the Form Tool is a RAD development tool.  Each form element can be linked to code that will execute and do really useful stuff -- assuming you can write the programming instructions to make it happen.
   
   Special hint:  The form tool has an oddity -- when you put the first item in your form, the toolbar disappears.  The solution is to drag the tool bar off of its docking into the middle of the screen before you start adding items to the form.
   
   Okay, so now you can build a form that will knock our socks off.  For instance, you can solve the "510" problem mentioned above.  (BTW, my intention is to use foxpro to export a text calendar file that meets the current industry standards.  That way, the calendar can be imported into the KDE calendar and into Mozilla's calendar.  The people who built the Mozilla calendar are said to be presently working to integrate it with OpenOffice, so that will become our standard, I imagine.  But, the problem with "exporting" is that if you make changes you have to "export" back.  Another solution is to "synchronize" the calendars via your palm pilot or palm desktop.  Cumbersome, but functional.)
   
   Okay, I am done for the day.  Write me if you have a question you think I might be able to answer.  I probably can't, but I will try.
   
   
5. Why do we want to use Abacus anyway?  Well, the Abacus system has some drawbacks, but it also has some elegant features.  Oh, yeah, it also has a hefty price tag.  But for instance, years ago I wrote a foxpro program to keep track of all of my documents by client.  The system will retrieve, store and zip (for space savings) all of my documents.  It also allows me to use templates for new documents very easily.   Had I continued to work on the program I probably would have evolved it in the general direction of Abacus -- that is, incorporating the "client list" with the "address list" and allowing use of multiple document types instead of just ".doc" documents.  In fact, I incorporated some of these features.  The fact is, Abacus lets you tie all your documents to your client entries (if you want to use a category, just make an entry such as "Lawforms" instead of a client name).  And, since the operating system will launch documents by "file type," you can launch just about any sort of document -- images, sound, video, etc. -- through abacus.  With a bit of planning, you can even run everything through a "zip" program for massive space savings.  Together, these features make it much easier to keep track of files on your computer -- as opposed to the "I wonder what directory it is in" system we use courtesy of the operating system's file management.  When you have 10,000 files on your computer, it's probable you no longer have any idea what is there or any way to figure it out.
   
   
   
   
6. 
   
7. 
   
   
   
   
   
   
8.