Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgYou may find that the database being shipped with OpenOffice (ver.2 and higher) delights you as much as it has me. This page tries to help you use it.
Forget anything you may have heard about Adabas, which came with Star Office, the commercial version of Open Office 1. The current Open Office's database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.
There's more about ooBase in the main index to this material.
This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)
Page contents © TK Boyd, Sheepdog Software ®, 2/06-5/09.
This "How To" attempts to explain something that many people seem to want: A way to use lookups on forms. For the sake of illustrating both the question and the answer.....
Imagine a very small police force... let's say it is in charge of a island in the Caribbean.
It wants to keep track of motor vehicle incidents.
The details of the tables and the relationships in the database for this job are contained in a separate webpage, which you ought to go off and read now, if you are not familiar with it. There's a link there for downloading the database, too.
What the Chief of Police wants from his IT person is a simple form so that when a person is involved in an incident, the chief can quickly, easily, get a list of all the incidents in which that person's been involved. The chief wants a form with two lookups... one for first name, one for surname. If he selects "Jane" from all the firstnames on the island, the second lookup is re-populated so that it only holds surnames for which there is a Jane. When he selects Doe (having previously selected "Jane"), he should see (in a datasheet on the form) all of the incidents Jane has been involved in. (We will assume that every car has only one owner.) (The "incidents" lookup isn't achieved until the second tutorial on this topic.)
Yes... the schema (design of tables and their forms) described on the other webpage is more complex than we need for our immediate wants... at least those stated so far. But it will serve us well as the basis of other database tutorials.
This tutorial will get us started. We will end up with a form which has two datasheets on it. One will list the firstnames which occur in the database, each firstname being listed only once. On selecting one of those, we will be rewarded with a second datasheet holding the full names of anyone with that firstname.
Our sample People table holds the following names:
We'll do something simple, first... to make a start...
We're going to create something with two datasheets, and clicking on any record in the first will cause the second to repopulate with only those records having the FirstName of the record selected in the first datasheet. Crude... but a start!
We will create this with the form wizard. In a nutshell....
Step 1: Source: People table. Arrange for... People.FName and People.SName to be in the form. Move "FName" to the right hand panel first In this step, and in step 3. Step 2: Invoke "Add subform", "Based on manual selection" Step 3: Be sure you are selecting fields from the "People" table. and put the same fields as before on the subform Step 4: Put "Fname" both in the "First joined subform field" and in the "First joined main form field." Step 5: Datasheet for both form and sub-form Step 6: Set to display all, but not allow any modifications, addition or deletion. Step 7: Any style Step 8: Call it "Simple Selector"
That gives a "working" result we can study... although you may have to re-size the window to see both tables. You can do that (and adjust the size or placement of the controls on the form) in the usual way whenever you have the form open in the design mode, i.e. open for editing.)
The reason we told the form wizard to disallow any changes to the data is that sometimes when your form isn't yet working properly, you can corrupt the data in the table. In a "finished" database, you would of course implement a way for users to change the data... but this form is for looking up data, not entering new data
Given our small universe of people, don't miss the magic: The second worksheet fills with....
Jane Doe Jane Seymour
... if you select either "Jane" line in the first worksheet.
Close the form. Reopen it in Edit mode. Right click on the "Sname" column heading in the first datasheet. Click "Delete".
Already, the form looks more reasonable.... but in the first datasheet we have Jane twice and Jeremy twice... a little inelegant, don't you think? And, of course, we have all of the fields showing in both tables. The latter issue is easily dealt with: Go into Design Mode, right click the FName column heading, click "Delete column".
We are also going to fix the repeated display of any duplicated first names. We're going to do it two ways. Both require that you become aware of tricks for getting to parts of ooBase others sometimes never reach.
We're going to do it first by using a query to populate the main form's MainForm_Grid. Then we're going to revise our form to display what we want (up to a point) without using a query.
First, before anything happens to it... Save your form "Simple Selector", and close it. Make a copy of it called "SelectorByQuery", and open that for edit. (You make the copy as follows... Go to the ooBase main project manager window. Click on "Forms" in the left hand column. Right click on "Simple Selector", click "copy". Right-click in the "Forms" pane. Click Paste, and edit the name.)
Leave that open, but before we can do our form by a query, we need the query!
Go back to the ooBase main project manager window. Click on "Queries" in the left hand column. Click on "Create Query in SQL View. Enter....
Select distinct "FName" as "FName" from "People"
... in the disconcertingly empty window that opens. (The SQL command "distinct" does the magic here.) Save that as "DistinctFNames".
The "Run Query" tool (Green tick) will take you on to a more helpful GUI. (If you've entered valid SQL!) You should see a list of the FNames, but without duplications.
I'm afraid that the ooBase query wizard can't give you "complex" queries such as the one we've done with "distinct". You can use it to "rough out" a query, and then invoke "Edit in SQL view, if you wish.
Now go back to the edit window for your form "SelectByQuery".
Open the form navigator. (There's a button for it, probably on the bottom edge of your screen.... As long has you have View | Toolbars | Form Design ticked. Don't confuse the form navigator with the more general "Navigator". (I have a short introduction to the form navigator for you.)
In the form navigator, right-click on MainForm, then on Properties. Click on the Data tab.
Change Content Type to Query. Make Content "DistinctFNames", using the drop-down list, and run your form... you should see the first names (without repeats) in the first data sheet, and the second should display all people with whatever first name you select. Select Jane or Jeremy, and you see multiple individuals, if you have populated your tables as I suggested.
Ta! Da! We did it (the first way.) Have you been into raw SQL before? The wizards take care of lots of things.... but raw SQL lets you do more.
Tidy up by closing everything but the ooBase main project manager window.
Select Forms in the left hand panel again, and copy SimplSelector to SelectWithInternalSQL, as you copied SimpleSelector to SelectByQuery a moment ago.
Open that for edit.
Change the Content Type entry on the MainForm's "Data" tab to "SQL Command". Make the Content....
Select distinct "FName" as "FName" from "People"
... and run your form. You should again see the first names (without repeats) in the first data sheet, and the second should display all people with whatever first name you select. Select Jane or Jeremy, and you see multiple individuals, if you have populated your tables as I suggested.
Ta! Da! We did it again... a more elegant way.
That seems to be enough for this tutorial. In the next tutorial, we will look at a similar form, but using drop-down lists instead of datasheets to display the names. We will add a datasheet displaying the incidents a selected person have been involved with.
I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....
I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.
PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE
If you liked this ooBase tutorial, see the main index for information other help from the same author.
Editor's email address. Suggestions welcomed! - - - Want a site hosted, or email? I like 1&1's services.
Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org
. . . . . P a g e . . . E n d s . . . . .