DISCLAIMER: This article was written quite a few years ago now (sometime around 2003) and refers to an old version of OpenOffice.org that was current when StarOffice 6 was also current. Unless you're running a very old pre-2.0 version of OOo, this article will likely only be of historical interest.
I'm going to walk you through setting up a Contacts database that will work in OpenOffice.org and StarOffice version 6. It should also work in StarOffice 5.2, but I haven't tested it. It will be a simple dBase-format flat-file database; fully relational databases are beyond the scope of this article. Fields in the database will roughly equate to similar fields in the Evolution PIM/e-mail client, although you can decide which fields will be most useful for you.
I'll show you how to create the database using OpenOffice.org or StarOffice (from now on I'll refer to them both as OO), how to define the database fields, how to create a table to contain the data, how to enter data using the table, and how to create and use a form for data entry. I'll also show you how to set up a separate Categories table for use with a Categories field within the main Contacts database. I'll even show you some of the many ways you can view, sort, and filter your data. There will be plenty of tips along the way, and a handful of screen grabs to show you how things should look.
We'll start off by deciding which fields we want to use to hold the data. This is the list I came up with and all you need to do is decide which ones you'll find most useful. Remember that you can always add or remove fields later on.
Personal fields
Formatted Text 50 Formatted name
Title Text 20 Mr, Ms, Sir, Rev, etc
FirstName Text 20 First or given name
MiddleName Text 20 Middle name or initial
LastName Text 30 Last or family name
Suffix Text 20 Bsc, Phd, etc
Prefname Text 20 Preferred name
Nickname Text 20 Social or Internet name
Salutation Text 50 Dear whatever...
Partner Text 50 Spouse or life partner
Family Text 250 Family members' names
Birthday Date Date of birth
Annivers Date Wedding or other anniversary
Gender Text 1 M, F or O
Home Address
HAddress1 Text 50 Flat number, House name
HAddress2 Text 50 House number, Street name
HAreaBoro Text 50 Area or Borough
HTownCity Text 50 Town or City
HCoStaProv Text 50 County, State or Province
HPostCode Text 10 Post or zip code
HCountry Text 50 Country
Work Address
WAddress1 Text 50 Flat number, House name
WAddress2 Text 50 House number, Street name
WAreaBoro Text 50 Area or Borough
WTownCity Text 50 Town or City
WCoStaProv Text 50 County, State or Province
WPostCode Text 10 Post or Zip code
WCountry Text 50 Country
Business
JobTitle Text 50 Job title or description
Company Text 50 Company name
Department Text 50 Department they work in
Boss Text 50 Boss or manager's name
Assistant Text 50 Assistant or secretary's name
Telephone
HPhone Text 25 Home phone
HFax Text 25 Home fax
WPhone Text 25 Business phone
WFax Text 25 Business fax
Mobile Text 25 Mobile phone
Pager Text 25 Pager
Network
Email1 Text 250 1st e-mail address
Email2 Text 250 2nd e-mail address
Email3 Text 250 3rd e-mail address
HURL Text 250 Home Webpage URL
WURL Text 250 Work URL
CURL Text 250 Calendar URL
FURL Text 250 Free/busy URL
Misc
Categories Text 250 User-defined categories
Notes Memo User-defined notes
Tag Bool Tagged record Y/N
All of the above fields will be contained in the main Contacts table. In order to be able to assign user-selectable categories to each record, we'll also have a table called Categories, whose fields will be as follows (they are the same as the default set defined in Evolution):
Birthday
Business
Competition
Favorites
Gifts
Goals/Objectives
Holiday
Holiday Cards
Hot Contacts
Ideas
International
Key Customer
Miscellaneous
Personal
Phone Calls
Status
Strategies
Time & Expenses
VIP
Waiting
Creating the database
Usually, you can't use OO to create a database. For the most part, you
create a database using the tools that come with that particular type
of database, whether it's mySQL, Oracle, Access, or whatever, and then
define it as a Data Source for OO to use. The one exception to that is
the dBase format database.
A dBase format database consists of a directory that contains one or
more files that define tables within the database. So start off by
creating a directory
to contain the database, for example, ~/contacts. This directory will
not only
be used to contain the database tables, it will also contain any forms
you create to assist with data entry.
Now run OO and click Tools, Data Sources, then the New Data Source button. Name the data source 'Contacts'. Select dBase as the Database Type, then click the ellipsis (...) to the right of the Data Source URL box and navigate to the directory you created. Click OK to finish defining the new data source. Note that at this point no actual database has been created in your new directory. That comes next.
"
Define Source Data dialog
[click to view full-size]
Press F4 to view all available Data Sources (you can also get to it via the View menu). You'll see your new data source in the list. Click the + sign to open it, then right-click 'Tables', then click 'New Table Design'. At this point, if you've ever used MS Access, you'll know exactly what to do next. If so, you can skip the next paragraph.
The Data Sources list
We're going to start off by defining the Categories table so that we can later reference it in the Contacts table. Each line defines one field in the database. You need to supply a name for the field and a type (the Description field is optional). Our table will consist of a series of records with one field each: the name of the category. So enter 'Category' as the Field Name and accept the default Field Type of 'Text (VARCHAR)'. Click the Save icon (the floppy disk on the toolbar) and save the table with the name 'Categories'. Press Alt+F4 to close the Table Design window.
Defining the Categories table
[click to view full-size]
If you now click the + sign to the left of 'Tables' in the Data Sources list, you'll see your new 'Categories' table listed. Let's go ahead and populate the table with data. Click the 'Categories' name and after a while a single-record table will display in the Data Sources viewer (to the right of the list). Click the empty field and enter our category names, pressing Enter after each. If you make a mistake, just click the erroneous field and retype the name. If you've used the category names I've suggested above, you'll end up with a 24-record table, with a blank space for a new field at the bottom of the list of names. There's no need to specifically save this table; it will have been saved automatically after you entered each record.
Entering the Categories records in the Data Source Viewer
Now it's time to define our main Contacts table. Right-click 'Tables' in the Data Sources list, and click 'New Table Design' again. Use the fields I've suggested above (or your own set) to create the table. Use 'Text (VARCHAR)' as the Data Type for all fields except the ones shown in the lists as 'Date', 'Memo' or 'Bool'. Since we're using 'Text (VARCHAR)', we don't need to specify maximum field sizes, but you can if you want to. Just set the Length field in the Field Properties section below the list of field names.
Defining the Contacts table fields
[click to view full-size]
When you've finished defining the fields for the Contacts
table, click the Save icon and save it as 'Contacts'. Press F4 to close
the Table Design window, then click 'Contacts' in the Data Sources
list. You'll see the table with one empty field in the Data Sources
viewer. If you list the contents of your ~/contacts directory, you'll
see something like the following:
[garry contacts]$ ls -l
Try adding some data to the table by clicking in the 'Formatted' field,
entering some text, then pressing Tab to get to the next field (just
press Tab on an empty field if you don't want to enter anything in that
field). When you've finished, the cursor will be on the next empty
line.
total 8
-rw-r--r-- 1 garry garry 1136 Nov 14 18:40 Categories.dbf
-rw-r--r-- 1 garry garry 1601 Nov 14 18:57 Contacts.dbf
-rw-r--r-- 1 garry garry 0 Nov 14 18:57 Contacts.dbt
Creating a data entry form
It's boring entering data into a table, especially one with this many
fields. And besides, you might as well have used a spreadsheet instead
of going to all that trouble creating a database. So we'll create a
data entry form. We don't need the Data Sources viewer in order to do
this, so press F4 to close it.
We'll start off by using an Autopilot to set up the form, then we'll refine it until we get it the way we want it. Click File, Autopilot, Form. In the dialog, select 'Contacts' in the 'Data Source' box and 'Contacts' in the 'Tables or queries' box. Click the =>> button to move all of the fields from the 'Existing fields' pane to the 'Fields in form' pane, then click Next. Leaving the Page Style set to '(Standard)', click the second-from-left button in the 'Arrangement of DB fields' section. After a few seconds, you'll see the form with the fields in place. If you don't like the page style, select a new one and view the result. If you don't like the arrangement of the fields, click a different button. I recommend that you don't use the middle button, as that inserts the fields as a table, and if you're going to do that you might as well use a spreadsheet as a data entry form.
Using the Form Autopilot
[click to view full-size]
When you've got things looking more or less the way you want them, click the Create button. You'll be asked for a name for the form, and a location to store it. You can save it in the same location as the database itself: the directory you created earlier that now contains your .dbf and .dbt files. I suggest you call it 'contacts-entry-form'. Depending on your settings, you might be asked for a description of the database, including a Title and Keywords. This is optional. If you now look in your database directory, you'll find the form saved with as .sxw filename extension. If you press F4 to open the Data Sources viewer, then click the + next to 'Links', you'll see your form listed there, and you can, in future, double-click its name to open the form.
You can try out the form. Click the 'New Record' icon at the bottom of the form and start entering data, pressing Tab after completing each field. Click the Save icon (the Disk) below the form when you're finished. Note the order in which the cursor jumped to the next field when you pressed the Tab key (known as the Activation Order). You can change this, and you can change both the layout and the formatting of the fields.
The Form Functions icon
If you look at the toolbar on the left-hand side of the form window,
you'll see
that the fifth icon down is the Form icon. Clicking this icon toggles
between
form editing and form entry mode. If you click and hold the icon, a
flyout panel
appears which contains icons for the various form design tools. You can
click
and hold the blue bar at the top and drag the panel to float on top of
the form
window. Most of the icons on the Form Functions panel are used to place
controls
and/or fields on to the form.
The Form Functions panel
The word 'Formatted' at the top-left of the form is a 'label field' and the data entry box below it is an example of a 'text box'. The label fields and text boxes on the form are grouped in pairs. If you click a text box, eight green drag handles appear around it; you can use these handles to resize the box and you can click and drag the middle of the box to move it; since the label is grouped with the box, they both move at the same time. You can right-click a label/textbox pair then click Group, Ungroup to separate them. You can shift-click another label/textbox pair to temporarily group it with the first (drag handles appear around both of them), and you can right-click and click Group, Group to permanently group them. So the first thing you might want to do is to move the label/textbox pairs around into a better arrangement.
You don't actually have to keep ungrouping and re-grouping labels and textboxes. You can open the Form Navigator (the 5th icon from the left on the bottom row of the Form Functions panel) and use it to alter one control at a time, no matter how it's grouped. Try it: open the Form Navigator and click one of the names in the list. You'll see the green drag handles appear around the control. You can resize the control, or you can right-click it then click 'Position and Size' to relocate it, or you can right-click then click 'Control' to change the control's properties. Play around with the Form Navigator and the Properties box; you can leave the latter open and it will update to show the properties of whichever control you click in the Form Navigator.
You can use the Properties box to change the formatting of the labels and the text that appears inside the textbox controls. Try this now: click the 'Formatted' label and you'll see the group (the label and the associated textbox control) highlighted in the Form Navigator. Click the label name in the Form Navigator so that the textbox is unselected, then right-click the label itself, then click 'Control...' (or just click 'Control Properties' on the Form Navigator). Change the 'Label' field to read 'Formatted name' and change the Character Set to Bold. You'll see that the text of the label has changed, but you won't be able to see all of it; click and drag the middle drag-handle at the right-hand end to resize it.
The Properties box for the Label field
[click to view full-size]
What if you want to change all the labels to bold without changing the textboxes? You can't directly select all of the labels on their own, since each of them is grouped with its textbox. To manually select them by clicking, you'd have to ungroup every single one. Instead, use the Form Navigator. You can click the top label in the list to select it, then hold down the Ctrl key while clicking the other label names. When you've highlighted all of the labels, click the Control icon on the Form Functions panel, then click 'Character set...' and select Bold. Just like magic, all of the labels will become bold.
You may have noticed that when you select more than one control in the Form Navigation box, the Properties tab only shows those fields that the selected controls have in common. You might also have realised that you can select everything on the form by clicking the top item in the Form Navigator list, then holding Shift and clicking the last item; alternatively, you can press Ctrl+A to do this when the Form Navigator is the top window. Clicking the Form name in the Form Navigator ('Standard' unless you've already renamed it) will also select everything in the form.
I said earlier that you could change the activation order: the order in which pressing Tab moves you through the controls. Just click the 'Activation Order' icon on the Form Functions panel, click an item, then click the button to move it up or down the list. Alternatively, just click and drag it into place. This can be tricky if the controls have their default names ('Label47' for example: how could you possibly know where in the tab order that should go?), so you might want to rename them to something more memorable: for example, 'lblFormatted' for a label, or 'tbTitle' for a textbox; once you change one of the names, all controls that reference it will update to reference it by the new name. By now you will have realised that the quick way to change the names is to have the Form Navigator and the Control Properties panel open at the same time, selecting the name in the Form Navigator, then switching to the Control Panel to enter a new name. One other thing you can do very quickly with the Form Navigator and control Properties panel is entering Help text that will be displayed in the form of 'tooltips' that pop up when the mouse pointer is held over a control.
In most ways, the form itself is just a regular document; you can add graphics (a company logo, for example), plain text, and other objects to it. You might want to spend some time turning what you have now into a good-looking and functional data entry form.
The completed data entry form
[click to view full-size]
Entering data from a dropdown list
At the moment, when using the form to enter data, we can only enter
text
manually into our Categories textbox. But
we've already set up a Categories table, so let's see if we can get our
form to
use it. Ungroup the Categories textbox from its label, click the
textbox to
select it, then press Del to delete it. We'll replace it with a combo
box. Click
the combo box icon on the Form Functions panel (the 8th from the left),
then
click and drag a box in the form that is approximately the same size
that the
old textbox was. An AutoPilot will appear that says, 'Choose the table
from
which the data should be used as the basis for the list content'. We
want the
list to be populated from the Categories table, so select 'Categories'
from the
list then click Next. The next page of the AutoPilot says, 'The
contents of the
field selected will be shown in the combo box list'. Select 'Category'
from the
list: this is the field in the Categories database that we want to use
to fill
the dropdown; now click Next. You'll be asked, 'You can either save the
value
of the combo box in a database field or use it for display purposes. Do
you
want to save the value in a database field?' We want it saved in the
Categories
field, so click the 'Yes' radio button and select 'Categories' from the
list,
then click 'Create' and the combo box will be created.
Now right-click the new combo box and click 'Control'. You'll see that the 'Label' field is empty, so link the combo box to the label by selecting the Categories label from the list. You might like to change the 'Line count' value from the default value of 5; this is how many lines appear in the combo box.
When trying out the form by entering data, I found that there was no way to get this control to accept more than one selection from the Categories list, whether I used a combo box or a simple drop-down list. At the moment, you can select one item from the list, or you can type as many items as you want, whether they are in the list or not. If I work out how to get it to work the way I want it, I'll change this section of the page. I hope you have better luck than I did.
You can test any changes you make to the form by clicking the 'Design Mode On/Off' button on the Form Functions panel. When Design Mode is off, you can enter data into any of the records you've entered so far. Remember to click the 'Save Record' icon below the form to save your data.
Adding fields to the database
You can add fields to your database, or edit existing ones by opening
the Data
Sources viewer (press F4), then clicking 'Edit Table'. Just add or
insert fields
wherever you want them. You'll then probably want to add the new fields
to
your form. Open the form, bring up the Form Functions panel, click the
control
you want to have handle your data (usually a textbox), then click and
drag a box
the size you want on the surface of the form. Right-click the control
and click
'Control', then the Data tab and select the name of your new field from
the
'Data field' dropdown. If you add a label for the control, you might
want to
associate it with the control; you can do this by highlighting the
control then
bringing up the Properties dialog and, on the General tab, select the
label from
the 'Label Field' dropdown.
Creating a form to edit your Categories table
You now know enough to be able to create a data entry form that allows
you to
add to your Categories table. Just to recap, click File, Autopilot,
Form, select
'Contacts' as the data source (remember, this is the entire database,
as
contained in the ~/contacts database, not the 'Contacts' table). In the
'Tables
or queries' list, select 'Categories'. Move the single field 'Category'
to the
'Fields in form' pane, click Next, select your background and
arrangement, then
click Create. You might think it isn't worth having a form for data
entry to a
single field, so you might want to either have a table on the form, or
edit the
data directly in the Data Sources viewer instead of using a form.
However you update your Categories list, each item you add to the Categories table will automatically appear in the Categories dropdown in the Contacts data entry form, but only after you've closed and re-opened the form.
Viewing, sorting and searching your Contacts data
It should be pretty obvious by now that you can view your Contacts in
the Data
Sources viewer that you get to by pressing F4, or you can open your
data entry
form and view them there using the navigation buttons below the form.
If you've
spent some time getting your form to look good, you'll probably use the
second
method, but I'll give you a brief overview of what's possible with both
methods.
Open the Data Sources viewer by pressing F4. Open the Contacts table, then check out the icons above the Data Sources list. You'll find (from left to right): Explorer on/off, Find Record, Sort Ascending, Sort Descending, Autofilter, Default Filter, Sort, Remove Filter/Sort, Apply Filter, Refresh, and Edit. The 'Explorer on/off' icon simply shows or hides the Data Sources list (it's OO's equivalent of StarOffice 5.2's Explorer bar). 'Find Record' brings up a dialog in which you can enter the text to find and where to look for it; it should be fairly obvious how to use this feature. The 'Sort Ascending and Descending' buttons simply sort the list in the appropriate direction. To apply 'Autofilter', click in one of the fields of one of the records, then click the 'Autofilter' icon; only records that contain identical text in that particular field are displayed. This allows you to, say, display only records whose HtownCity field (Home Town or City) is 'London'. Click the 'Remove Filter/Sort' icon to revert to viewing the entire list.
Wheras 'Autofilter' allows you to display selected records based on a single criterion, 'Default Filter' allows you to define a filter based on up to three criteria. For example, you could set it to display only those records where the home town is 'London', the last name is 'Jones' and the gender is 'M'. Again, click 'Remove Filter/Sort' to revert to displaying the entire list. Click 'Apply Filter' to apply the last-used filter. The 'Sort' icon allows you to sort records on up to three fields; this could be handy when you have several people living at the same address but with different names, for example. The 'Refresh' icon is handy when you've accidentally entered something in a field and want to revert to the data that was there previously, although pressing Esc usually has the same effect. To ensure you don't accidentally edit anything in the table, click the 'Edit' icon. Click it again to go back to editing the data.
Press F4 to close the Data Sources viewer and make sure your Contacts data entry form is open. At the bottom of the form, you'll find a set of navigation buttons, some data entry buttons, and some of the same icons you found above the Data Sources list. The 'Form-based filters' button lets you define what to filter for by entering sample data in the form. Click 'Form-based filters' then click the new 'Filter navigation' button that appears at the bottom-left of the form. Enter 'London' in the HtownCity field; click the + to the left of 'Filter for' in the 'Filter navigation' list and you'll see 'HtownCity: 'London'' in the list. Enter 'M' in the Gender field and you'll see it appear below 'London' in the Filter navigation list. Each value you enter in a form field must be present in the record for a record to be displayed when the filter is applied. In other words, by entering 'London' in the HtownCity field and 'M' in the Gender field, you're saying "Only show me records containing 'London' AND 'M'". If you look in the Filter navigation list, you'll see the word 'Or'. Click this and the form will empty. You can now enter 'Manchester' in the HtownCity field, and you're effectively saying "Show me records where the HtownCity field is 'London' and the Gender field is 'M', or records where the HtownCity field is 'Manchester'". You can build some quite complicated filters this way. When you've defined the Form-based filter, click the 'Apply filter' icon at bottom-left to return to the form. Until you click 'Remove Filter/Sort', only records that conform to your filter will be displayed.
One other icon appears below the form: 'Data source as Table'. This simply opens the Data Source viewer (as if you'd pressed F4), but without the Data Sources list being displayed.
Bugs and problems
Gernot Muenster tells me that he's using OpenOffice 1.01 and that
sometimes
the contents of some of the fields don't display correctly when the
form is
first opened or when jumping from one dataset to another. According to
Ocke
Janssen of Sun, "this bug is fixed in the OOo1.1beta2 version".
And you remember my mentioning above the
problem with getting the combo box to accept more than one selection
from the Categories list? Well, Filipe Morais of Portugal e-mailed
to say that "the combox defects seems corrected in the OO
1.1.1rc". At the time I'm typing this, I'm still using 1.1.0 and
haven't had time to check it myself.
Thanks to Gernot, Ocke, and Filipe for the above information, and
thanks also to Filipe for translating this tutorial into
Portuguese.
Removing test databases
There might come a point at which you've decided to get rid of one of
your databases so that you can start again from scratch. Or maybe
you're no longer using it and, having archived it, you want to delete
it. There are two steps to this procedure. Open the Data Sources viewer
(press F4), then right-click the
Data Source (e.g. Contacts) then click 'Administrate Data Sources' (what's
wrong with the word 'administer'?). Now right-click the name in the
list in the dialog, then click 'Delete Data Source'.
Having removed the database as a data source for OO, you can now delete
the actual files in the database directory.
Getting adventurous
As well as having dropdown lists populated from other tables (and as
many as you want per form), you can also have fields from multiple
tables on the same form, so you can update more than one database
using the same form. The more you explore, the more features you'll
find and you'll end up realising that, far from having few database
features, OpenOffice.org and StarOffice are fairly
feature-rich in this department. One thing we haven't touched on in
this article is the relational database. You can create a database
using, say, mySQL, then set it up as a Data Source using a JDBC or
ODBC driver. You can also create Data Sources based on text files,
spreadsheets, or various address books (e.g.
Mozilla), although you can't edit these types of databases within OO
(although you can edit the text files or spreadsheets themselves).
The other thing I haven't discussed is StarBasic; this will have to
wait until I've learned more about it.
Now you've got the basics under your belt, I suggest you experiment. Remember to keep backing up your stuff, click everything in sight to see what it does (right-click as well) and use the Help facility when you get stuck. Pretty soon you'll be a power user. Good luck!
I've uploaded the demonstration database, including the
data entry form for the Contacts database. It's in .zip format and you
can download it from here. It
includes a README file that tells you how to install and use the
database. I would draw your attention to the following:
DISCLAIMER
Because I put the database together purely as a demonstration, it is
provided "as-is" without warranty of any kind, either expressed or
implied, including, but not limited to, the implied warranties of
merchantability and fitness for a particular purpose. The database
is NOT intended to be used in a working
environment, but is a demonstration only, so the entire risk as to the
quality and performance of the database is with you.
Having said this, I welcome feedback in the form of constructive
criticism of the demonstration, or suggestions as to how to improve
it. I don't have a lot of time to answer specific questions on
OpenOffice.org or StarOffice,
so don't e-mail me with those; I hang out in the alt.os.linux.mandrake
newsgroup, as well as in comp.os.linux.misc and a handful of others,
and you'd best direct your questions there in order to maximize the
possibility of getting a response. You can contact me by e-mail at
garryknight@gmx.net