Quick and Dirty with Open Office Base.

Even with all of the high quality software available, sometimes you just have to break down and write a custom application, and usually you don't have a lot of time to devote to it. This is the situation I found myself in when my wife started a new business and needed a simple means of tracking sales leads. To her credit, she realized that she couldn't simply use a spreadsheet and needed a database application. So I decided that I'd see just how involved it would be to write an application with Open Office's database program, known as Base.

When I started, I'd never used Base before and even had to install the program on both my machine and my wife's before I could get started. I had used Microsoft Access many years ago and I do have a lot of experience with PostgreSQL and MySQL, so I figured I'd be able to pick up Base in short order and be able to build a customized application for my wife. Now there have been many articles written about Base. (See References, below) This article isn't a How-to, or a product review. Instead this article is more of a crash course that covers the point in time right after initial installation to the point where you have a working application. I came in with zero experience with Base and not a lot of time to climb a steep learning curve. Two evenings later, I had a basic application that my wife could at least get started with. As I learn more, I expect to improve the program, but in the mean time, she's able to get some use out of it.

So let's get started.

As with any database application, and even ones as simple as this one, it helps to draw a diagram of the objects that you intend to manage and the interrelationships between them. Then I like to simply list the fields, or attributes, that each object requires. Once this is done, you can start creating tables in Base.

Once I started the Base program and decided to create a new database or connect to an existing database, I was presented with the main screen, as shown in Figure 1. As you can see, I was given the choice to use a wizard to create my tables, or I could use the “design view” and create them manually. I tend to hate wizards, but I did take a quick look at the wizard. When I did, I was given the chance to select from quite a variety of objects such as “products,” “customers,” or “assets.” Once an object was selected, I was presented with a selection of fields relevant to that object. I can see where this would be quite a quick-starter for many people. I decided to create most of my tables manually. You can see the definition of one of the tables in Figure 2. This process is fairly straightforward. You simply list the names of the fields, or attributes, that your table needs and you assign data types to them. The only problem I had here was that I wasn't sure what the difference was between a “real,” a “float,” and a “double.” The option of selecting “other” as a data type was also... unexpected. Then, at the bottom of the window, I was able to tweak each file type by setting the length, default value, etc. This part of the process was pretty straightforward and worked exactly as I expected.

Figure 1

Figure 1

Figure 2

Figure 2

For an application as simple as the one I'm building, using the relationships tool to configure referential integrity is a bit of overkill, but it has an added benefit, in that it allows you to see at a glance what the data model looks like, and how the tables are interrelated. See Figure 3. Here you can see that my wife wants to track sales contacts, as well as which organizations they belong to. She also has several shows that she participates in and needs to track them, as well as a point of contact for each. Finally, she wants to be able to schedule events such as phone calls, or registrations, etc., for each organization, contact, and show.

Figure 3

Figure 3

You'll notice that I have a “Relations” table in my model. This will eventually be used to store “many to many” relationships. It turns out that a given contact might be a member of more than one organization, and my wife wants to track that fact. However, creating the forms, which we discuss next, to create these relationships has proven to be more difficult than expected.

So, once I created the database tables, and a reasonable diagram of the tables, fields, and relationships, it was time to start creating application forms. Once again, I was offered the choice of creating the form manually, or using a wizard. Initially, I tried the wizard, at least until I was comfortable with the process. The wizard asked me to select a table to draw from, then it allowed me to select the fields from that table to include in the form. Finally, the wizard created the data-entry form for me. However, I just really didn't like the resulting form and decided to create the form manually.

The process of creating the forms needed for my application proved to be the most difficult part of the entire process. It turns out that Base actually uses the word processing application, Writer, as it's form engine. This was quite unexpected, but in hindsight, I see it as a very elegant solution. Writer is already able to create HTML forms, so why not use it to create database forms? I'll admit that I had a bit of difficulty figuring out how to bind a form to a given table, but once I found the “form” widget, which is next to the cursor in Figure 4, the process was quite intuitive. Then it was simply a matter of dragging text labels and data fields onto the canvas. The form shown in Figure 5 is pretty simple and plain, but I expect to improve it over time. Along the bottom of the screen, you see record navigation icons as well as sorting and filtering icons. Eventually, I'd like to create buttons for these functions, but I'll discuss that later. So, I created a data-entry form for each of the objects that my application needed to track. At this point, my wife could start entering her data.

Figure 4

Figure 4

Figure 5

Figure 5

Entering data is one thing; getting it back out is another thing, and for that we use the Query tool in Base. Here once again, we are offered the convenience of using a wizard to help us with our query, or we can create our query manually. Additionally, we can use the SQL mode and enter our query in raw SQL; finally, something I was familiar with! I have to admit though, that the wizard really wasn't that bad. It allowed me to determine how the data would be sorted, and gave me a very intuitive means of selecting the data, much like the conditional formatting functionality of the Open Office spreadsheet application that I was already used to using. See Figure 6. Still, I decided to try out the manual query generator, which you can see in Figure 7. Both the wizard and manual method are extremely powerful, and quite intuitive. If I had to chose between them, I don't know which way I'd go. But over all, I preferred the SQL method because that is what I'm used to using.

Figure 6

Figure 6

Figure 7

Figure 7

My wife doesn't have extensive reporting requirements, so I only took a cursory glance at the reporting tool. Here we're only offered the option of using a report generating wizard. I actually can't imagine any other way to generate a report, and the wizard seemed to do what I expected it should do.

So after an evening or two of work, what do I have to show? Well, I have a means of storing my wife's business data, and she can enter it into a (reasonably?) attractive data-entry form. She can search and sort her data. The program is easy enough to use that she won't have any difficulty learning how get her work done with it. It's also extensible enough that I can make periodic improvements to it as my skills grow. Think of it as a living document.

On the other hand, what am I still lacking? The application isn't as polished as I'd like it to be; she still has to manually select a form before she can do any data input, or she can input directly into the table in tabular format. It would be nice if the program presented her with an opening menu and hid the underlying guts from her. I had difficulty implementing the many-to-many relationships mentioned earlier. However, I believe that both of these issues can be resolved by using a little bit of programing. Open Office provides a dialect of visual basic that I'll bet could be used to handle the problems I've mentioned as well as adding some polish to the whole application. If there is any interest, I might try to write a primer to Open Office Basic in a future article.

So as you can see, Open Office Base is a quite capable database program with a fairly shallow learning curve. I don't perceive Base to be as mature as the word processing or spreadsheet applications, but that is setting the bar pretty high. The fact that I can link Base to my MySQL or PostgreSQL databases makes it particularly interesting to me as a database front-end, though the linking process isn't as easy as it should be. Overall, Base is an extremely powerful tool and reasonably easy to learn.

AttachmentSize
Fig1.png41.39 KB
Fig2.png57.03 KB
Fig3.png71.55 KB
Fig4.png129.75 KB
Fig5.png100.46 KB
Fig6.png38.13 KB
Fig7.png48.56 KB
______________________

Mike Diehl is a freelance Computer Nerd specializing in Linux administration, programing, and VoIP. Mike lives in Albuquerque, NM. with his wife and 3 sons. He can be reached at mdiehl@diehlnet.com

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

open source

LDW's picture

Open source applications and programs like Open Office hopefully will help us move away from having to accept the bugs that Microsoft and others always leave in their code and have to fix later.
custom web design

good stuff author! i have

loft ladders's picture

good stuff author! i have needed both as usual.only want to say superb article.

Base - Ruby - PHP

UK's picture

I have been waiting for a Linux equivalent of MS Access and BASE is shaping up quite nicely so far. But as an IT Manager I think it is important for potential developers to understand some of the problems that Access faces, and that BASE will face too.

A lot of companies don't permit users to develop applications in Access, having been burned by past experience. This is nothing to do with any technical problems with Access, but with the way it has been used.

Typically a smart user will put together an Access database for their own use. They will keep adding new functionality until the application becomes essential for their work. From this point onwards there are several different failure modes that a BASE developer will also need to prepare for.

Sometimes the developer leaves the organisation, leaving behind an application that is critical to the organisation, but which nobody understands. There is usually no documentation and often nobody else who understands Access anyway. Sometimes an IT department gets handed a broken application and is told to fix it by 4pm. Mens Colognes - Mens Fragrances - mens aftershave That won't endear you to whoever gets handed the job.

Sometimes a hardware failure wipes out an Access database kept on a user's local hard drive. There is no backup and data collected over a long period of hard work simply evaporates. BASE developers need to understand information security. That's not only how to stop unauthorised access, but also how to recover from disasters.

It Help

Merry.B's picture

i love reading OO Base tutorials, it help a lot of people to spread its use, sometimes, we need to do dirty hard work like the one you said.wireless routers

I also had a productive play

Munnu's picture

I also had a productive play with Base recently after several disappointments with previous versions crashing, behaving unexpectedly star ebook

Menu System

Kevin Warrington's picture

I have never used Base to create a database and only had time to have a brief look at it. It certainly looks good and being Open Source is very attractive. I would imagine you could use a Form with just Buttons and Text in it to act as a menu for accessing other Forms. This Form would then become the default Form when opening the database for users. A simple menu system that hides the back end.

BASE Developing

Bernard Peek's picture

I have been waiting for a Linux equivalent of MS Access and BASE is shaping up quite nicely so far. But as an IT Manager I think it is important for potential developers to understand some of the problems that Access faces, and that BASE will face too.

A lot of companies don't permit users to develop applications in Access, having been burned by past experience. This is nothing to do with any technical problems with Access, but with the way it has been used.

Typically a smart user will put together an Access database for their own use. They will keep adding new functionality until the application becomes essential for their work. From this point onwards there are several different failure modes that a BASE developer will also need to prepare for.

Sometimes the developer leaves the organisation, leaving behind an application that is critical to the organisation, but which nobody understands. There is usually no documentation and often nobody else who understands Access anyway. Sometimes an IT department gets handed a broken application and is told to fix it by 4pm. That won't endear you to whoever gets handed the job.

Sometimes a hardware failure wipes out an Access database kept on a user's local hard drive. There is no backup and data collected over a long period of hard work simply evaporates. BASE developers need to understand information security. That's not only how to stop unauthorised access, but also how to recover from disasters.

I Think BASE is really a good

VM Ware's picture

I Think BASE is really a good option when it comes to make your customize database application. It does offer wizard to easily create forms, reports. I know some people hate wizards (like you) but it comes very handy to users who are new to database creation. Anyways I really like your application.

I am not a programmer but I

Vcloud's picture

I am not a programmer but I really needed such application. I wish if you can polish this application with some more automated things and i would contact you to make one for me. Keep up the great efforts.

Similar experience

Neil's picture

I found Base fairly straight forward and intuitive when doing similar things to you. What I found difficult was creating forms which would populate mutiple tables, and reporting from multiple tables, especially when the report required variable inputs.

Base example

oldrah's picture

Nice overview of a Base application. Suggest you build on it to develop a Base primer. The current Base documentation is spotty, at best. I've had some good success with programming so-called "Macros" in Basic. You might want to look into that for more complex manipulation of DB data such as statistical analyses. Thanks for taking the time to write up your experiences.

More information about OOo Base

r4zoli's picture

You can find more information on OOo Base wiki page:
http://wiki.services.openoffice.org/wiki/Base

Some links point to macro programming site.

The Basic guide:
http://wiki.services.openoffice.org/wiki/Documentation/BASIC_Guide
and Developer's Guide:
http://wiki.services.openoffice.org/wiki/Documentation/DevGuide
is a starting point for macro programmers.

VB Help

dbroadie's picture

I would love to see a primer in OOo VB. I have a need for both Base & Calc, but haven't had time to look in any depth, but a primer to get me started would be wonderful. I've worked in MS VB and would like to have a primer that I could relate to.

References?

Wm.A.Weasel's picture

Near the beginning of your article, you say "See References, below", but then there aren't any. I would have liked to see them.

Roberto Benitez has published a book, "Database Programming: OpenOffice.org Base and Basic". I have found the book useful, and it seems to be the only in-depth book that covers OOo Base (except maybe for one in German). It was written before OOo 3 was available. Base seems to have improved tremendously as of OOo 3.

Many-many

jhansonxi's picture

A well-traveled path:
http://en.wikipedia.org/wiki/Object-relational_mapping

I suggest a KISS approach and just model the most common relationships.

Access to Open Office Base ...

wareagle's picture

I've experimented with migrating an Access database to Open Office Base, but I haven't mastered it yet partly because I have tackled it seriously.

I can import the tables and queries from Access (defining the relationships is a big deal, as you suggested), but the forms don't import well from my view. I also need more extensive reporting.

A menu system on the front end would help organize the database and "hide" the guts (as you say). I find that helpful with Access so my users don't mess with the formatting. Most of my users are inexperienced.

Anyway, I agree with one poster that a series of articles, addressing a number of issues, on Open Office Base would be good.

This is a very good overview of both the problems encountered by a new user and the potentials available in the product. Great job!

A series of OO Base posts

Anonymous's picture

A series of OO Base posts would help greatly to spread its use to a lot of people who, sometimes, need to do dirty work like the one you said.

Base becomes useful at last

dmorrell's picture

I also had a productive play with Base recently after several disappointments with previous versions crashing, behaving unexpectedly or having frustrating interfaces (especially in the forms design area). I've really been hanging out for a Linux alternative to MS Access. I think it's finally here.

very nice look at Base

apexwm's picture

Very nice article. I've never needed to use Base, but this brings out some of the capabilities of it. The entire OpenOffice.org suite is mature and very powerful. I use the suite daily, mostly Writer and Calc. Well done, thanks!

http://members.apex-internet.com/sa/windowslinux

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix