|Solveig Haugland, Site expert|
Rather than having a shallow affair with OpenOffice 2.0, we can use it to get a little more intimate with the data. We can even badger it into forming relations. Why force your database tables into a marriage with OpenOffice? Because, as with human relations, life -- and data -- are pretty meaningless without tight connections.
This tip on creating forms is part of a series I'm writing on OpenOffice.org 2.0 Base. So far, I've discussed making a plain database from scratch, creating tables, entering data using the table editor and a simple form and creating a view of a table or tables.
Let's start off with a description of what table relations are anyway, then discuss how to create a data entry form like this one, which has data from two related tables.
What's a relation?
Sometimes, you don't need your tables to relate to each other. If you're just keeping mailing list info in your database, you don't need to care much about relations. Your data is just a list, complete in each record.
Let's say, however, that you have a company with employees. You could keep all your information about employees in one enormous table, but there are many reasons not to do that. For one thing, performance might slow down a bit. Also, there could be security issues, as you don't want everyone who has access to address information to have admission to the Social Security number and salary info. So, you probably want to have your employee data in two separate tables.
Of course, you don't want that data floating around unrelated, so that Mary's salary information shows up with John's address information. Therefore, you must create a relation between the two tables to keep the right data together. You have the employee ID and name in each table, and that's how you relate the records in each table.
There's just one record in each table for every employee. That is, for each record about Mary in the employee address table, there's also just one in the HR (human resources) table. That's called a one-to-one relation.
Relations can get more complicated, though. Let's say that your company sells things, like books. Do you sell just one item to just one customer, each time you make a sale? Probably not. If you're a bookstore, you probably sold Mary several books on Linux the last time she came into the store. And with luck, Mary is a repeat customer so you've sold her stuff before.
For every customer, there are many possible sales, and for every sale, there are many possible items in that sale. These are called one-to-many relationships. You set up the relation the same way by having at least one common field on both tables.
How do you make your data form relations?
Now that you've got tables that are related, you may need to enter more related data into your tables. A data entry form that handles this relation process would be nice. How do you create that form? That's a very good question.
I've been answering that question, in various ways, all day. I've taken a few breaks to make tea, feed the cats, and make some fleece headbands (long story), but mostly I've been wrestling with table relations and forms.
I could go into a lot of detail about what I've discovered, but it was confusing enough for me and would make a really long, baffling article. Here's the short version.
There's an option in the form creation wizard that lets you point to existing relations between tables. I figured I would set some up. It's easy to do; just open your database and choose Tools > Relationships. Then click Add to add the tables for which you want to create a relation, and drag from the common field in one table to the same field in the other table.
You'd think that when you create the form, you could just say, "Hey, use that relation I already set up." Then, everything would work great. That's true of the former, but not the latter.
I wanted to create a data entry form like this, using the wizard.
I kept getting a form like that, but upside down. The perfectly good relation I had set up was flip-flopped in the wizard. No matter what I did, I couldn't figure out how to make it come out the way I wanted. (I didn't try doing it in design view, since that's a little bit more of a challenge, and I wanted to write about the easiest way to do it.)
I discovered that with creating a data entry form like the one I wanted, which contained the single item on top and the multiple items related to it on bottom, I didn't need to set up relations between the tables ahead of time. There's another way to do it. Just choose a different option in the form creation wizard.
In the next part, I'll show you how to create a data entry form for related tables, creating the relations on the fly in the form creation wizard.
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.