I showed you how I made data form relations in OpenOffice 2.0 in part one of this tip. Now, you can follow along...
as I create a data entry form for related tables, creating the relations in the form creation wizard.
Referring back to my bookseller example, let's say that you've got two very simple tables, an Authors table and a Books table, reflecting your inventory. Every author has written one or more books, so there's a one-to-many relationship between the author table and the books table. You want to set up a data entry form, so that your intern can enter books as they come in.
Open your database where the tables are, and click Forms. Choose to create a new form using the wizard. (If you're handy enough to create one in design view, you don't need this article.)
In the first wizard window, select the main table. In this case, it's Authors, since it's the "one" in the one-to-many relationship. Be sure to insert all the fields you need, including the field Author ID that's also in the related field. Click Next.
In the next window, choose to create a sub-form. There's nothing in the list of relations, even though I did set up a relation between Authors and Books. That's because the relation only shows up if I choose Books as the main table...thus making the whole thing backwards. At any rate, in this window, choose Manual Selection of Fields. Click Next.
In the next window, just select the Books table, and insert all those fields too, including Author ID. That's the field the relation depends upon.
In the next window, you'll need to select the fields that the relation is based on. There's just one here, Author ID. Select that for both lists.
In the next window, you can do whatever you want, but you'll usually want something with normal fields for the main table and a table-type list for the sub-form table.
Click Next. The rest of the wizard is obvious, just like setting up a normal form.
Choose to work with the form, and click Finish. The form will look something like this:
Enter your data using the icons at the bottom of the work area.
Remember forms this holiday season
All complaining about flip-flopped form wizards aside, the data entry form wizard is reasonably easy and useful. The new sub-form feature is pretty good if you know the data, though you probably don't want to put someone new to OpenOffice.org or your database in charge of table creation. One crazy, mixed-up intern who can't tell the difference between InvoiceID and ItemID can make your data flip-flop, not just your forms.
As long as there's someone responsible in charge, the data entry forms are a good way to enter, as well as look at your data. Forms are sometimes easy to forget in the morass of spreadsheets, filters, queries, views and reports. They're not always what you need, but keep them in mind; they're a good way to view of enter information for anything you've got. You can set up the forms as read-only, restrict it to allow new data or limit the viewing of existing data.
This can be pretty useful for a variety of people in your organization besides saving you work. When the vice president or manager keeps bugging you for the figures on last month's sales, just tell her there's a form out there that she can look at. She's not going to look at them anyway, but it's a lot less effort to point her to a form than to create reports.
Solveig Haugland has worked as an instructor, course developer, author and technical writer in the high-tech industry for 15 years, for employers including Microsoft Great Plains, Sun Microsystems,and 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 books: Staroffice 5.2 Companion, Staroffice 6.0 Office Suite Companion and OpenOffice.Org 1.0 Resource Kit, published by Prentice Hall PTR. Her fourth book, on OpenOffice.org 2.0, is coming this summer. For more tips on working in OpenOffice, visit Solveig's OpenOffice blog.