A database of its own is one of the most-requested features for OpenOffice.org, and the development team came through with Base. So there's a solution built in to OpenOffice for those whose needs for storing and viewing data exceed recording everything in a spreadsheet. Most businesses or organizations of any size do require something more.
You don't need to keep paying for your license to Microsoft Access. (OpenOffice.org 1.x did connect to Access and a host of other databases.) You don't need to mess with JDBC (unless you want to). And you don't need to learn a more complex open source database. You just choose File > New > Database. That's fairly simple.
|Sovleig Haugland, Site expert|
With Base, you can easily set up databases and tables of your customer mailing lists, your invoices, your expenses and income for a year; track books and other media for a school or library; and record scientific data. These databases can be created from scratch or you can set up OpenOffice.org to connect to existing databases. You can also create entry forms to bring data into those databases, view any subset of that information and print it any way you want.
Base also works with the mail merge features of OpenOffice.org, so it's still easy -- it's even easier with the 2.0 mail merge wizard -- to send out holiday letters to everyone in your contacts database or gentle reminders to customers who have unpaid invoices.
In my first article on Base, I started out with an overview of the high points: how to create a basic database and the actual table and how to add data with a simple data entry form.
In this article, I'll go further into some of the features, such as views, that give you more control over what data you see, including joining a couple of tables in a view. Down the road, I'll look at reports, queries and other topics.
What's the purpose of views?
Now, you may want some more sophisticated ways of looking at the data. Let's say you need to have a huge list of customers, and you just want to look at the ones from Minnesota. Or you want to look at the name, address and outstanding invoice information for your customers, but that data is stored in a couple of different tables. You might want to look at that data, print it out or send a mail merge letter to them.
So, what do you do first to get the exact data you want out of your database tables?
The answer: views. A view is another way of looking -- hence the name -- at a table. A view is a specific subset of data in a table, showing all or some of the fields and all or some of the records. A view might show me the First Name, Last Name and E-mail fields for all the customers where the State field is Minnesota. The fields that you show and the fields that restrict what records are displayed don't need to be the same.
Views are for setting up ahead of time the exact data you want. Then, when you want reports, envelopes, mass mailings, etc., you just point to the right view.
In the first example I mentioned above -- the mailing to customers in Minnesota -- a simple view will give you what you need. In the second case, viewing information from two different tables, you'll need to create a relation between a couple tables in your view. That's pretty simple, too.
Queries versus views
Here's a side note for the curious: In 1.x , queries were the way you created subsets of your data, so that you could do more targeted mail merges, etc. To send a letter to the customers in Minnesota, you created a query restricting the state field. But guess what -- in 2.0, it's different. Views, which are quite similar to queries, are what you use as the basis for mail merges (along with tables, of course). Queries can be used as the basis of reports, but so can views.
Creating a simple view
Let's get started with a simple view. I've got a database already set up with a couple of tables in it, Customers and Invoices. I choose File > Open, find my customer invoices database and open it up.
I'm going to create a view to restrict the data I see in the Customers table. Here's the data in it now:
With the Tables icon in the far left pane of the window selected, click the Create View option.
You'll get a design window with a few columns and a smaller window that lists all your tables.
Select the table you want to use for the view and click Add; it'll show up in a separate window.
Now, to create your view, just double-click the first field in that little floating window that you want in the view. I double-clicked First Name and Last Name, since I want data for those fields to show up. I also double-clicked State Or Province, because even though I don't want to display that information, I want to restrict, or filter, what records show up based on that field. So I un-mark the Visible checkbox for State Or Province, and I type MN as the data. I only want records to show up in my view if the value for their State Or Province field is MN.
Now just save it by choosing File > Save or clicking the diskette icon in the upper left corner; you'll get a small window where you can name it. Then, close the view design window.
The view is listed with the tables. Select the view and, in the right-hand pane of the window, be sure Document is selected so you can see the results.
That's all there is to it. The view will now show up when you do mail merges, and when you choose View > Data Sources.
Creating a view with data from two tables
Here's another question: What if you wanted to see the names of those people from Minnesota, as well as the invoices you've submitted to them in the past? The invoice information is in the Invoices table. How do you join them?
It's actually pretty easy. Remember that window listing all your tables? Well, instead of selecting just one and clicking Add, you select one first, then the next. So, you have two floating windows, one for each table.
You need to make sure that there is some sort of relationship between the tables; you need to have Customer ID in both tables or something similar.
Here's another example: Creating a relation between a table listing your CDs with a table listing your DVDs isn't going to make sense, since there's no common data and no way for the database engine to know how to connect the two tables. However, it would make sense if both tables contained a field such as Media ID.
Once you've ensured that there is a common field, click on the field in the first table, and just drag over to the same field in the other table. A line will appear between the tables, showing the relation was created.
Now you just create your view as you normally would. Double-click on the field of the table you want to show up, and fill in the view design.
The tools are kind of cool, but what does this get you? How are you better off? Are you more likely to be able to leave work early because you've been so productive with Base views?
Views can be used as the basis for mail merges, envelopes and reports. (I'll be talking about these in subsequent articles.) When you've got your views set up, then you can create reports based on whatever views you want or print mass mailings or envelopes based on views.
Once you've set up views, it's really quick to use them. They're just sitting there, showing you exactly what you want. You don't need to go burrowing through the data, trying to sort it and find the data visually, every time you, your boss or your intern wants to see all the products in your inventory with less than five in stock.
Views are powerful. For example, you can set up complex filters and restrictions so that you see customers you sent invoices to more than 90 days ago. You could see which ones are from Montana or California or Oregon, and who owes more than $20,000.
You can create lots of views, or you can easily edit your views. If you find you need to create 50 views, one for each state your customers are from, go ahead. If you often need to look at data from a particular zip code, just create Customers_ZipCode, edit the zip code field so that it's equal to 59901 or 59088 (or whatever you need) and save it. The next time, quickly change it to 80026 (or whatever) and save it.
Essentially, Base views make it quick and easy to create reports, mail merges and other documents with exactly the information you want. Simplicity, efficiency and accuracy: three things it's nice to have at work or at home.
Solveig Haugland has worked as a technical writer, course developer, instructor and author in the high-tech industry for 12 years, including 6 years at Microsoft Great Plains, 3 years at Sun, and a year at BEA. Currently, Solveig is a StarOffice and OpenOffice.org instructor, author and freelance technical writer. She is also co-author, with Floyd Jones, of three how-to books: Staroffice 5.2 Companion, Staroffice 6.0 Office Suite Companion, and OpenOffice.Org 1.0 Resource Kit, published by Prentice Hall PTR. For more tips on working in OpenOffice, visit Solveig's OpenOffice blog.
This was first published in December 2005