Novell Home

Novell Connection Magazine Home

March 2008

Features

A
Better Base

Building your carefree database in OpenOffice.org—made simple with forms
Tech Talk 6 by BrainStorm, Inc.

  • Show Menu for Current Issue

In the first article in this series on OpenOffice.org Base, I hoped to tempt you into trying out this database app that's included in the OpenOffice.org suite. In the most recent article, I explained Subforms. But now it's time to take a step back and look at just plain old forms for those of you who aren't as familiar with databases.

As you know, databases can seem pretty daunting at first. If you're new to the concept, you might even find that your first attempt at creating one is less than successful. But don't let that deter you. A properly functioning database will save you a lot of time in the long run. This is especially true when it comes to the forms feature of OpenOffice.org Base. Inputting large amounts of data would be more than just a little frustrating if it weren't for forms. Since you know how to build tables, create unique fields, link common fields from separate tables, and from the last article, create subforms, let's take a look at populating tables with data using simple forms. Obviously, this can be done by manually entering data in each table; but forms are really what you'll want to master.

The Scenario

Let's review our scenario: You're the IT director for a company with a slew of employees, and you have a lot of hardware to manage. Therefore, you're going to build an inventory database to help you manage and track the printers, laptops, desktops, scanners, fax machines and other important equipment floating around your company site. You want to include serial numbers, purchase dates, user information, vendor details and lots of other data about each item. You're also just starting out, so none of this information is really in one place, meaning it can't be easily exported from a spreadsheet to the database; we'll cover that in another article. What you need is a simpler, more straightforward way of getting the right information into your tables. (For more information on more advanced form features, check out the most recent article, Great Form: Discovering the Power of a Subform.)

Making Refinements

In the first article, I recommended that you carefully plan each table for your database. Then I showed you how to create those tables. And now it's confession time—and advice-giving time—after setting my database tables aside for a few days and spending some time boning up on forms, I decided that my tables needed a little tweaking.

Why? Well, sometimes a table contained information it didn't need, and other times two tables contained too much redundant information. I simply wasn't being specific enough. But that's okay. It's also exactly why I suggest that you create your tables, set them aside for a few hours—or even days—then come back to them for some fine tuning.

For example, I made these tweaks to the Status table: I added a few fields, namely the maintenance and comments fields, where IT

employees could make notes about a certain product or provide details and dates about the maintenance history. I also added a description field, where I could enter a simple term to identify each product (laptop, desktop, etc.).

I changed the Manufacturer table to a Vendors table because what I really want to know is where we purchased the product. The manufacturer is secondary to this information—and I added a field called make/model for it to the Status table.

At this point, I realized that the Inventory Details table was, at best, redundant, so I scrapped that too and moved vital fields from it to the Status table.

For the most part, this is what I had left, and what we'll use to move forward:

  • Product Status table, with fields for each product's description (laptop, desktop, etc.), the product's serial number, the product's make/model, the vendor's ID, the purchase date and price, the user's employee ID number, the estimated product life, the current condition (new, fair, poor) and fields for comments and maintenance issues. (See Figure 1—Product Status Table.)
  • Employee/User table, with fields for the employee's ID number, first and last names, department (marketing, sales, accounting, IT, product development, etc.) and phone extension, as well as the serial number of each product that employee is using. (See Figure 2—Employee/User Table.)
  • Vendors table, with fields for each vendor's ID, name, address, contact name, phone number, e-mail address, etc., as well as the field for notes that replaced the old field for discounts. (See Figure 3—Vendors Table.)
  • List tables for the department and condition fields in the first two tables

Next

  • Figure 1

    This is the main table in our IT Inventory Database. Several of the fields in the table, such as those for the Serial Number, Vendor ID, Employee ID, and Condition, are linked to other tables.

  • Figure 2

    This table is a secondary table in the IT Inventory Database. It is linked to the main table through the Employee ID field.

  • Figure 3

    Like the User/Employee Table, the Vendors table is secondary to the main table. It is linked to the main table through the Supplier ID field, which shares common information with the Vendor ID field in the main table.

  • Figure 4

    If you have only a few tables in a database, with relatively little information needed in each table, you can easily populate your tables by opening them and entering the data by hand. Simply tab over from field to field. Remember to save your data before closing the table.

  • Figure 5

    The Form Navigation Toolbar employs VCR-like buttons to help you scroll through each record. It is also the place to go to save, delete, undo, or further control a record while entering data.

  • Figure 6

    Youll likely want to change the name of many of the labels on your forms. This is because the label names come directly from your tables, which require that a field be named with only one word.

  • Figure 7

    After creating a relationship, a line will display between the common fields, indicating the type of relationship. This is a one to one relationship.

  • Figure 8

    Selecting a field type for each field in your table will help you create tables that provide the specific information you want to relay,

  • Figure 9

    By selecting a field, right clicking, and choosing Control, the Properties box displays. Here you can make changes to improve data entry. This image shows that the user has made changes that will place a drop-down arrow in the field, add parameters for minimum and maximum date entries, and set the date format to MM/DD/YY.



© 2014 Novell