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 ®, 7/09.
This is a modest little thing... but it does take you to a place that wizards alone won't reach.
A little database is created to record and display attributes of different plants. (Any gardeners reading do not need to get their hopes up! Head on over to, say, the RHS website if it is plant information you want. Here we are just illustrating some database techniques.
Our database has the names of sundry plants, and for each information on the color of its flowers, and the nature of the plant: annual, biennial, perennial.
The "best" bit of this is that it shows you how to set up a form with two sub-forms.
To help keep the data "good", instead of one table with, say, "Daffodil/ Yellow/ Perennial", we have multiple tables... among them a table listing the colors allowed in the "color" attribute column for the table that is one of the fruits of the exercise. Thus it will be impossible to have "yellow" daffodils and "yellwo" forsythia. (I have made a typo in the color, just as you might, if you used the simple "answer" to this database task.)
At one point, you could download the database, you do not need to set it up and populate the tables. It was written under ooBase 3.1, on a Win XP box... but i seem to have misplaced my copy and deleted the online version. Sorry. Write if it is a big problem, I'll see what I can do. A little later, there is a similar database for you to download!
This case study was inspired by Topic 86596 at the Open Office Forum, oooForum.org
At the very useful oooforum.org, someone asked,
I have the following tables and would like a form with a Textbox showing the Flowername on top and then two listboxes respectively listing all relevant AxDescriptions for the flower. What is a suitable "Form navigator tree structure" for this? Table 1: FlowerTable FID: integer, primary key Name: text Table 2: Attributes1Table A1ID: integer, primary key A1Description: text Table 3: Attributes2Table A2ID: integer, primary key A2Description: text Table 4: LinkTable ID: integer, primary key FlowerID: integer <- this filled with integers from FlowersTable.FID Attrib1ID: integer <- this filled with integers from Attributes1Table.A1ID Attrib2ID: integer <- this filled with integers from Attributes2Table.A2ID
How could I resist the temptation of such a clear question?
The necessary tables were set up pretty well as set out in the question. (The clear "this is my need" exposition is half the reason I started writing this.)
I used type Text[VARCHAR_IGNORECASE]for the field FlowerTable.Name. Sliderule recommends that data type, and I thought it was time I gave it a try. There were no consequences for this project, taken as far as it has been to date. A few changes crept in between my database and the EXACT spec in the question... but I stuck to it pretty well. I limited the length of many fields to less than the default 50 characters.
I used Attrib1 to record the color of the plant's flowers, and Attrib2 to record whether the plant is an annual, biennial or perennial.
Then, before any invalid data had been entered, e.g. an entry in the link table with, say, 8 for attrib1 for a flower when no attrib 8 is defined, relationships established for the whole database. (You can also establish relationships at other levels... I'm not sure whether those relationships are really for the whole database, or as "local" as they seem to be.):
From ooBase main project manager window, Tools, Relationships:
Added all four tables to the design window from the sub-window.
Dragged from FlowerName.ID to LinkTable.FlowerID
Dragged from LinkTable.Attrib1ID to Attributes1Table.A1ID (Notice: It doesn't matter which field you drag from... you will get a 1-to-many link, the right way round, regardless.)
And dragged from LinkTable.Attrib2ID to Attributes2Table.A2ID.
So... three links.
Saved work so far.
Entered some data. (Please don't take my botany as reliable. Also, I've been a bit "amateur"... I DO realize that poppies come in colors other than red... but the database is to demonstrate database techniques, not help you be the next Joseph Banks!)
Tried to enter some "bad" data, just to be sure database picked that up. The way it is set up, only entering data in the LinkTable will cause refusals. Trying to delete an entry from the other tables can trigger a refusal.
(Confession: At this point, I was working directly with the table. A No-No.)
Initially, my tables were defined quite simply, no clever stuff. After I'd entered some data, I aspired to cleverness.. never a good thing.
While I generally avoid "Entry required", I thought in this instance that maybe it would be appropriate, especially for LinkTable.FlowerID. While I was at it, I made the two LinkTable.AttribID fields' data entry required... but at the same time, set them to default to "0", and in both attrib tables, I made the text for "0" "Not Sure".
Hmm... easier said than done. First I went through the data I already had in LinkTable, made sure every field in every record had some valid data. (Thanks to setting up the relationships early on, at least the data was valid, i.e. if there was a "6", say, in LinkTable.Attrib1ID, then there was a record in Attributes1Table with an ID of 6.
"The secret" to going back and imposing the changes on the tables was temporarily to edit my relationships. I right-clicked on each relationship and deleted it, and then saved the new, "empty" relationship rules. I didn't close the relationship design window, so it was a simple matter to put the 1-to-many relationship rules back as soon as I'd change the tables' properties.
Having set things up thus, I'd hoped that the "0"s would fill in automatically ("default") if I created a new record in LinkTable without doing anything about either or both of the AttibID fields.... but my wish wasn't the computer's command. So if, say, I want to enter several flower names, working down the LinkTable.FlowerID column, and then go back and fill in the attribute information... I can't, because of my "cleverness". There must be a way, short of turning off the "entry required". Why have a "default" value, otherwise?
ANYWAY.... we have the basic structure up and running. Now, how about a form to display the data?
I used the Form Wizard to rough out what I wanted, and then went back and tweaked it. So...
Step 1: Table: LinkTable. All fields. (We can hide un-needed ones, but they may be needed by the form "internally", and I'm not risking having to go back and add them.)
Step 2: I added a sub-form. Interestingly, "Based on Existing Relation" was grayed out... which to me suggests an answer to my earlier question: It would seem that database-wide relationship rules are NOT necessarily available to individual forms. So how do we tell a form about the relationships it might want to use? Maybe the form I was creating had no way to use any of the database-wide rules?
Step 3: At this stage, I only added the Attributes1Table to the form, as sub-form. In due course, we will also add Attributes2Table to the form. Added all fields.
Step 4: Set the joins. Ah! This is the "relationship" of the elements in the table. Joined Attributes1Table.A1ID (the "joined subform field) to LinkTable.Attrib1ID (the "joined main form ID")
Step 5: Data Sheet for both forms.
Step 6: Left it with "for entering new, only" NOT ticked, and all changes allowed.
Step 7: Whatever
Step 8: Called it DisplayAttribs
Ran my lovely form... and it worked fine, after re-sizing so that I could see the sub-form.
Click on any record in the first form, the form displaying the contents of the LinkTable, and the subform will show the record from Attribute1Table appropriate to the record selected in the main form.
If the ins and outs of messing with the form's structure that follow, you might want to skim through another tutorial I wrote, and then come back here. (I fixed the link to open that in a new tab or window for you.) Don't worry about the fancy stuff, but study the distinction between what I called, there, the "fform", and the "forms on the fform". (I haven't used the "fform" word in the case study you are reading, just "form" for both entities.)
End of detour
I then closed the form. Re-opened it in Edit Mode.
Opened the FORM navigator.
Right-clicked on MainForm. Clicked New, Form.
That gave rise to a form called "Standard", subordinate to MainForm, but of status equal to that of "SubForm".
Clicked on Standard, to select it.
On the "More Controls" palette clicked on the Table Control icon, then dragged out a new Table Control on the design window. It was assigned the name "TableControl" in what the form navigator displayed, and was subordinate to (contained within) the "Standard" form. As soon as I dragged it out, a little wizard popped up to ask what table I wanted the control bound to, and what fields I wanted displayed. I think you have to have the form design wizard turned on for this to happen, but at the time I'm writing this, I can't find the appropriate button to point it out to you! If you don't get the wizard, you can set the association by hand, and add the fields. Better, if you get the TableControl, but "unpopulated", to delete it, find the button to turn on the wizard, and try again!
Saved the newly enhanced form. Ran it. (From the design window)... and....
It didn't work. I was merely getting a complete display of the whole Attribute2Table in the new sub-form.
Went back into edit mode, compared the properties of the two sub-forms.
Have you guessed?
Yes... we haven't set up the link fields for the second sub-form yet, have we?
They are easily set when you know how...
Go back into edit. In the form navigator, right click on the subform called "Standard", click "Properties" (If it isn't in the list, the properties window is already open.) On the Data tab....
Set the Link Master Fields (You'll select "Attrib2ID" for that... just one link field, in this instance)
Set the Link Slave Fields: "A2ID"
And now your form will work as it should!!!
As long as you are happy to look up the attributes of a flower using the flower's ID.
It should be really simple to create a query which combines....
The fields from LinkTable, and FlowerTable.Name
... and use that as the main form for the Display Attributes form...
Never satisfied, I then re-worked the whole thing. You can download the resulting database file.
Changes from the first one:
Instead of "Attrib1" and "Attrib2", I've used more meaningful names.... "Color" and "LifeCy".
The "Grand finale", the table to display the color and habit of the different plants is now based on a query. The query pulls in the fields of LinkTable, and of FlowerTable, and, in the query, something like a relationship manager window was used to "connect" (perhaps wrong term) FlowerTable.FID with LinkTable.FlowerID. This operation gave rise to the....
WHERE "FlowerTable"."FID" = "LinkTable"."FlowerID"
....in the SQL for the table. It means that you don't get many, many lines of result.
Once that query was available, it was easy to build the form called DisplayAttribsFancy, which does pretty well all we would want for looking up the data in the tables for a given plant, I believe.
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 . . . . .