OpenOffice.org Base: Editing Information in a Database
Once you have a database set up, sooner or later you will want to edit its tables or add a new record. You have four ways to do so.
If you created a database using an existing OpenOffice.org Calc or MS Excel spreadsheet, you must edit the spreadsheet itself—in Base, the database will open read-only, and you will be unable to edit it.
For other databases, you have three choices: editing the tables directly, using the form wizard to create a form or using the design view to create a form. Whichever way you create a form, you can then use it as a more user-friendly way to edit your database.
Editing a Table Directly
Editing a table directly is the purist's way of adjusting a database. It is not hard to do, but some users find it intimidating and may prefer one of the other methods.
To edit a table in a database directly, open the database and change the view in the Database pane on the left side of the window to Tables. Then, select the Table in the Tables pane, and right-click for the context menu.
If you want to add a field to the database, select Edit to open a view of the table. Click the first vacant role to start adding a new field. As with other fields, you need to enter a name in the first column and a field type in the second column. You also can enter the field properties in the window of the pane that opens below the main editing window, setting whether the field is required, the number of characters it can contain and, optionally, a default value and example. In addition, back in the main window, you have the option of adding a brief description of the new field in the unnamed third column.
When you are done, right-click on the gray left-hand column to select the new field, and use the context menu to cut and paste it into the position you want. You also can alter, delete or move existing records using the same technique.
If you want to add a record to the database, select Open from the context menu in the Tables pane, or double-click the table to open the editing window. With this selection, you see the fields listed at the top of columns and the individual records in rows. Otherwise, just as when you edit fields, you add new entries at the bottom. Because records are far more flexible than fields, you can change how records are viewed by using the icons on the right of the toolbar to sort all records in the table, in ascending or descending order, or to display only those you set in a filter.
Designing a Form Using the Form Wizard
If tables seem a forbiddingly cold way of working with a database—a good example of why you've shied from databases in the first place—you may prefer to design a form for editing records. The easiest way to design a form is using the Form Wizard.
To use the Form Wizard, select Forms from the Database pane, followed by Use Wizard to Create Form in the Tasks pane in the top middle of the window. This selection opens both the Form Wizard and the Design View, where you can see the form taking shape as you progress through the wizard.
On the first page of the wizard, you select the table for the form from the drop-down list, and the fields to include in the form by selecting them and moving them from the Available fields pane to the Fields in the form pane using the arrow controls in between the two panes.
You can skip the next three pages of the wizard, where subforms are created (they're a topic for another day). On the fifth page, you select how entry fields will be arranged on the form and whether they will be left- or right-aligned. Which arrangement you choose is mostly a matter of whether you want the labels of the field to the left of the entry fields or above them, and whether each entry field is the same length or reflects the number of characters it may have. A third option gives you a view much like the one you have when you edit a table directly, but apart from the fact that you can select colors and maybe add a background picture, this option probably won't appeal to anyone who wants to use the wizard.
Clicking the Next button, you receive a list of choices of what data you want to appear on the form. Most of the time, you probably want the default of displaying all the available data, but you can choose not to show existing data and whether modification of existing data, deletions or addition of new data are possible on the form.
On the next pane, you can select a pre-existing color scheme. To be honest, most of the schemes are uninspiring, but you always can alter them later in the design view if none appeal to you.
Finally, set the name of the form and save your work.
Creating a Form in Design View
The Design View is a more hands-on method of creating an editing form. Chances are that you won't want to design a form from scratch in the Design View, but you might want to use it to edit a form after creating it in the wizard. For example, you might want to add a title to it, change the background color or add a picture as eye-candy to help calm those with a database allergy.
To open Design View, select Forms from the Database pane, followed by Create Form in Design View in the Tasks pane in the top middle of the window. You open in Design View, which is identical to OpenOffice.org Writer, except that it has the Form Controls toolbar down the left side of the window. If you look closely, you can see that the grid is turned on to help you arrange entry fields as neatly as possible.
To add an entry field, select the field type from the Form Controls toolbar. Then, in the window, drag with the mouse to define the size and shape of the entry field. You probably will have a hard time indicating exact field lengths (unless you want to count grid points), but otherwise, you should have little trouble if you keep an eye on the grid. If you want to reposition or resize the result, click the resulting entry field and move it about just as you would any other object in OpenOffice.org.
Double-click the entry field, and the Property box appears. Here, you set the characteristics of the entry field, using the same values that the fields in the database have. To keep the form and the database in sync, go to the Tables view and select Edit from the context menu so you know the names of the fields and their types and properties. If you are working on a small screen, you might want to jot the information down on paper, rather than juggle the two different views.
Once the field's properties are set up, add a text label from the Form Controls toolbar. You probably will want to give it the name of the field associated with it, but you might consider another name, especially if the form will be used by people who might not be comfortable with the straight field names.
Do the same for all the other entry fields, and position them as you want. Annoyingly, you cannot group label fields and entry fields together so you can reposition them as a single object, but the grid should partly compensate for that shortcoming.
As a final touch, you can enter other text, such as instructions or title, or go to Format -> Page to edit the background for the form.
Using a Form
No matter how a form is created, using it is the same. Select Forms from the Database pane, and click the name of the form in the Forms pane in the bottom middle of the editing window to open the form. The form opens in a modified version of the Design View, with some other controls grayed out and the Forms Control toolbar at the bottom of the window.
The Forms Control toolbar has icons for sorting records in ascending or descending order and for setting up filters—either of which can help you find an existing record more quickly. It also has arrow buttons to move through the records and a field to allow you to jump to the record you wish to edit.
To delete the current record, click the Delete button in the Forms Control toolbar. To add a record, either select the fifth arrow button from the left or, at the last record, the next arrow button. Either way, all entry fields in the form become blank so that you can enter the new record.
None of these three editing methods is absolutely superior to the others. Which you use depends on your preferences and, perhaps, your circumstances—for instance, if you have reduced vision, a form might be more practical, because you can select fonts for the labels and entry fields that you can easily see. Others might prefer using the wizard for the sake of speed, or using the Design View for the complete control it offers in form creation.
But, no matter which you use, once you are comfortable with your choice, you will have come a long way in taming your initial misgivings of databases. Who knows? You might even begin to believe that databases are a useful way of storing information.
Bruce Byfield (nanday)
- Geek Guide: The DevOps Toolbox
- Nmap—Not Just for Evil!
- Download "The DevOps Toolbox: Tools and Technologies for Scale and Reliability"
- March 2015 Issue of Linux Journal: System Administration
- High-Availability Storage with HA-LVM
- Resurrecting the Armadillo
- Real-Time Rogue Wireless Access Point Detection with the Raspberry Pi
- DNSMasq, the Pint-Sized Super Dæmon!
- Localhost DNS Cache
- Days Between Dates: the Counting