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 ®, 3/06.
This is part of a case study into using Open Office's database. This page carries on from Project Participation, Part1, where the database's tables were explained. (A sketchy recap is provided below.)
The good news? I have put a copy of the database online, which you can download. If you downloaded it from Part One of this case study, you do not need to download it again. N.B.: I don't think you can open it with ooBase version 2.xx. It may not be exactly as described below, and "building" the thing from scratch may aid your understanding... but if you'd rather download an approximation of what follows, you can!
Let me recap the nominal "object of the exercise".... but remember that what is presented here has been written up because variations on it will serve many needs.
Imagine that you are in charge of SourceForge.net, the great internet site which helps the open source community bring us new applications. Sourceforge provides sites to projects. People sign up to work on projects. Someone, somewhere, probably wants to see who is doing what on how many projects, and get answers to similar questions. The database developed in this case study is for that hypothetical somebody.
The database will allow users to see....
.. were explained in detail in part one of this case study. They are...
The first two tables are very straightforward.
The records in the People table hold whatever you need to know... apart from what projects they are part of... about the people involved. Beyond "the obvious", there is a "secret ingredient", which is important: The "PersonID" field. No two people will be assigned the same "PersonID", and, besides the other things we want it for, it is the table's primary key.
The next paragraph may give you a sense of "deja vu"... don't be disoriented... it is different (and similar) to the previous paragraph... with reason. When you can build symmetry into a database, benefits arise.
The records in the Projects table hold whatever you need to know... apart from what people working on them.... about the projects. Beyond "the obvious", there is a "secret ingredient", which is important: The "ProjID" field. No two projects will be assigned the same "ProjID", and, besides the other things we want it for, it is the table's primary key.
Now we come to the last table, PeopInProj. This table will seem badly designed to some readers. But, I promise you, it is the way to go. And it illustrates something you will see over and over in database work.
You really should read part 1 of this case study. Among other things, you'll find the details of the "People" and "Projects" tables there. As they are unremarkable, I'm not going to say more about them here. As the PeopInProj table may seem less self-evident to some readers, I am now going to recap some of the material about it....
The PeopInProj table has the following fields:
In the PeopInProj table, in the Project field, we will enter a project ID found in the ProjID field of the Project table, hence the data type and length must be the same for PeopInProj.Project and Project.ProjID. (Note those shorthand ways of saying "The Project field of the PeopInProj table" and "The PProjID field of the Project table".)
Likewise, in the PeopInProj table, in the Person field, table we will enter a person ID found in the PersonID field of the People table, hence the data type and length must be the same for PeopInProj.Person and People.PersonID.
Each record in the PeopInProg table will document one person's involvement with one project.
Some people may be involved in only one project; others will be involved in many.
Some projects will have only one person working on them; others will have attracted many people.
The glory of the data structure, or "schema" described above is that there is virtually no limit to either the projects a person is involved in, nor to the people involved in a give project. And also we haven't set up a lot of wasted space, which would happen if we'd organized things more along the lines that we might have, if we didn't know this "trick".
Here is some sample data for the PeopInProj table....
PPID Project Person Role Joined Left 0 20090125a JoAr head 01/01/09 1 20090125a BrCh assist 01/01/09 2 20090130a BrCh head 02/02/09 3 20090125a SmJa assist 01/01/09 30/01/09 4 20090130a SmJa head 20/01/09 5 20090130a BeHe assist 20/01/09 6 20090130a JoAr assist 25/01/09
That's a very limited set of data, but it already illustrates a number of things,
The last three lines tell you that project "20091030a" has three people working on it: James Smith, who is the head of the project, And Henry Brown and Arthur Jones.
Up to here, we have merely been re-capping what was covered in part one of the case study. Now we are going to do something important to the database. (If you are using the downloaded version, this has already been done to it.)
Get the database, i.e. FDB7PeopProj, open... but don't have any tables, queries, forms or reports open. In other words, you should be seeing just the main ooBase project management window, the one with three panes. (It doesn't matter what you have selected for display in the lower right.)
Click on the main menu item "Tools", and from the sub-menu click on "Relationships...."
A new window should open, with a dialog box open in the center labeled "Add Tables", displaying the names of the tables in our database.
Double click on each of the three table names. As you do this, a box showing the table's fields should appear on the main window's working surface. When you have all three tables represented, close the "Add Tables" dialog.
Rearrange the tables' representations (if necessary) so that "PeopleInProj" is between the other two.
Put your mouse pointer on "PersonID" in the "People" table. (That's supposed to be the table's primary key, and if it is, there will be a small "key" icon in front of the field's name.)
Carefully drag to "Person" in the "PeopInProj" table. Before you release the mouse button, in Windows, at least, you will see the "shortcut" icon associated with the mouse pointer. After you release the button, you should see a line between People.PersonID and PeopInProj.PersonID. There should be a little "1" at the "Person" table end of the line. (There's more on all of this in my tutorial on referential integrity, but for now, just press on!)
In a similar way, create a line between PeopInProj.Project and Projects.ProjID.
Click the window's "Save" icon, and then close the relation design window, which is what you've been working in! (If you forget to do the save, or can't find the icon for saving, you'll be given a chance... take it!... to save as the relation design window closes.
What have we accomplished?
It is now impossible to put an entry into PeopInProj.Person or PeopInProj.Project unless a person or project has been given the ID you are trying to enter. This might be annoying in some circumstances which you can easily work around, but it is a Very Good Thing. Be Happy.
Not only is it impossible to create an invalid record in PeopInProj, you are also protected from the following possible "breaking" of your database.
Imagine that you have a person with the computer ID of BrCh... and imagine that his ID appears in one or more PeopInProj records. What happens if you then delete the BrCh record from the People table? If that was possible, you would end up with some "bad" records in PeopInProj.
Ta da! ooBase is SO CLEVER that, if you've set up the relationship as described above, you won't be able to delete ChBr until you first delete all of the PeopInProj records which involve ChBr. (I'm just glad that I didn't have to write the software to take care of all of this!
The rest of this page is in two sections. First there's a section discussing various queries, forms and tables which have been set up to access the data in the tables. The discussions merely cover use of the entity described.
The other section describes how each query, form or report was built.
The names assigned to the queries, forms and reports are a little extreme. For a user friendly database, they would have less rigorous names. Alternatively, or additionally, I believe you could use the Switchboard to put a human-friendly face on underlying rigor.
Here we have descriptions of what they do, their names, etc. Go to the "How to" section if you want to learn how they were created.
Form: Simple Entry of People- fPeopE1 (Description)
This form is a very crude, but it allows users to put data into the People table.
The "data sheet" (looking like the underlying table) format is suitable because the data per record is not extensive. It is helpful to be able to see multiple records as you are entering data.
Put some new records into the database. Note that you can't put a record in without giving it a unique PersonID. Try it, to see the fearsome error message that you get.
You can enter a record with only the PersonID filled in. The form could be altered to prevent this, but then it would be harder to enter batches of data, as you must go row-by-row. I often prefer to type in, say, the PersonID for 5 records, then the names for them, then the eddresses.
Return to start of Descriptions.Form: Simple Entry of Project- fProjE1 (Description)
This is just the "enter project data" equivalent of the Simple Entry Of People form, fPeopE1.
Return to start of Descriptions.Form: Simple Entry for PeopInProj table- fPIPe1 (Description)
This is a crude answer to the need to enter data in the PeopInProj table. It "works", but....
1) It makes the human work hard, looking up the computer's IDs for people and projects by hand. (We'll develop something to help with this in a moment.)
... and ...
2) The form does nothing to check that the IDs entered are valid IDs... i.e. IDs which exist in the relevant tables. If you have taken the steps to set up relationships in the database, explained earlier in this part of the case study, the database will be taking care of this. I only mention it here, because sometimes you put your data validation in the form.
This form was also used to illustrate a few non-essential "pretty face" changes one can make to a wizard-generated form.
A superior version of this form is high on my "to do" list, but may not appear for some time, I fear.
Return to start of Descriptions.Form: For People and Project ID codes- fDispPnP1 (Description)
If we are going to make entries in the PeopInProj table (via one or another forms, of course), we will (either directly or indirectly) need to know the "abbreviations" for the people and the projects we are collecting data about... e.g. "BrCh" and "20090125a"
In due course, I hope that an "all singing, all dancing" database will emerge.... but for now this simple form will help us: It is a quick way into the People and Projects tables. The two tables on the form can be sorted on any column; the form can be set up to initially sort the data on whatever column (in each) is thought most suitable.
Return to start of Descriptions.- - - - - - - - - - - - - - - -
The forms described above are all very pedestrian... but with them alone, a user could manage the data we've set out to manage. The queries, forms and reports that follow are more "clever"... and less easy to set up (!)... but they make it much easier for the user.
- - - - - - - - - - - - - - - -
A human friendly display- fForSimple1 (also uses qForSimple1) (Description)
So far we've kept things very simple. To produce the following isn't very hard. As usual, what is necessary is described in the "How To" section.
I couldn't copy that as text, hence the graphic.
It may not seem too remarkable at first.... but look closely. It is listing the projects... not too surprising. But look at what's listed with them. Instead of the cryptic codes used to identify projects and people, good, human friendly information is listed. We can see who is working on which project. But this is just the start!
If you can create the above, you are well on the way to creating almost any report you might want from the data in the three tables. Help with the above can be found at my page about Multi-Table Forms and Queries.
Return to start of Descriptions.(Description)
x x x x A placeholder for a future item.
Return to start of Descriptions.(Description)
x x x x A placeholder for a future item.
Return to start of Descriptions.Here we have descriptions of how to make them. Go to the "Descriptions" section for simpler descriptions of what they do.
I haven't discussed every bit of form making you can do, but I hope I've covered the essentials in each case.
As an example of the sort of thing I have left out, I haven't described how I made the forms the size they are. I have to admit a little frustration with ooBase in this area. I can't seem to tell it to set up a form to open maximized, and I can't seem to tell it where I want the form to appear. But I can (!) get the form the size I want it, if I have open in the "restored" state. The secret: open the form for edit. Make it the size you want it. Save it. Close it. It will then open at the right size.
Other things left out....
1) Putting titles and messages on pages, e.g. those in the form fPIPe1. (The page is essentially an ooWriter "sheet of paper". Whilst in "edit" mode, you just type. Paragraph margins, tabs, fonts, etc, all work "normally".)
2) Setting the alignment of the text in the columns. Again, some of this has been done in fPIPe1- the text is centered. A little trickier this one. Go into edit mode. Be sure you are in the design mode, which will be indicated by the absence of data in the table. Right click on a column heading, click "Column...", and the "Properties" window will open. With it, you can set the alignment of the data in the column. Setting the font of the data in the table seems to be something you have to set for all columns, and not including the columns headings. To set the data font, get the table selected. If the properties window is already open, you can set the font. If it isn't right click and select "Control..."
Form: Simple Entry of People- fPeopE1 (How to make)
This was created with the form wizard. The step numbers below are those which arise if you are using ooBase 3.0.0, but the sequence should be similar, even if other ooBase versions change the step numbers.
There you have it.... almost!
After you clicked "Finish", the form should have come up, and worked. You could even resize the columns (make them wider). However, those changes were only temporary. To make them permanent....
Close the form. Right click on its name (fPeopE1) in the ooBase main project management window's "forms" panel. Select "Edit" from the menu that pops up. Use the "Design Mode On/ Off" button to switch to a view of the form with data in it. Drag the short vertical lines between the column heading labels left or right until you have good column widths. When all's well, save what you've done (you'll be asked if you meant it if you forget), and exit the designer. (The button is second from top, toolbar at left of screen. Is a yellow set-square on a ruler, with a pencil... at least on the screen in front of me at the moment. I couldn't (bah!) find a menu based alternative.)
Return to start of "HowTo"s.Form: Simple Entry of Project- fProjE1 (How to make)
This was done the same way as the Simple Entry of Person form.
Return to start of "HowTo"s.Form: Simple Entry for PeopInProj table- fPIPe1 (How to make)
Apart from one "tweak", this too was done the same way as the Simple Entry of Person form.
The "tweak" was to exclude the "PPID" field from the form. There's no reason for the humans ever to see the value in this field, and the humans may "interfere" with something the computer manages quite nicely, thank you, if they (the humans) get the chance.
The "pretty face" tweaks were accomplished with the things I said in the preface to this section that I would not go into on a form by form basis.
Return to start of "HowTo"s.fDispPnP1 (How to make)
Fire up the form wizard.
In Step 1, Field Selection, ask for, from the People table, the PersonID, Surname, and FirstName fields.
In Step 2, Add Subform Fields, ask to add a subform, using manual selection of fields.
In Step 3, Select the Projects table, and move at least ProjID and ProjName to the "Fields in Form" memo. (You may wish to move more fields across. If in doubt... move them. They are more easily deleted than added later.)
In Step 4, Get Joined Fields, do nothing, define no joins. Just click "Next"
In Step 5, Arrange Controls, be sure that both forms are destined to be displayed as data sheets.
In Step 6, Set Data Entry, you will probably find things already set as we want them: For the form to display all data, and prevent nothing. (Those permissions can easily be changed later, by the way.)
In Step 7, and Style will do.
In Step 8, to maintain consistency with various references in this case study, name the form fDispPnP1. (In the real world, any (sensible) name would do.)
The resulting form is already pretty satisfactory, but now we'll "do things" to make it even better... and illustrate those "things".
Close the form, and then right click on its name in the ooBase main project management window. Click "Edit" on the sub-menu. Use the "Design Mode On/ Off" button to switch to a view of the form with data in it. Drag the short vertical lines between the column heading labels in both tables left or right until you have good column widths. When all's well, save what you've done (you'll be asked if you meant it if you forget), and exit the designer. (The button is second from top, toolbar at left of screen. Is a yellow set-square on a ruler, with a pencil.)
Go into design mode (data not showing) and you will be able to select the tables. Re-size them, and move them to more sensible locations on the "page". Adjust the overall size of the window. (Leave a little extra space below the tables, because when you come to use it, you will possibly have an extra tool bar to fit in. Make the bottom too close to the table, and you will have to fool with scroll bars.)
Save your design changes, close the window, reopen the form. All well, I hope?! (Remember not to be alarmed by the weirdnesses mentioned in this form's description, in the section above.)
Now for some more "magic". Open the form for editing. Use the "Design Mode On/ Off" button to switch to a view of the form with data in it. Click in the blank line at the bottom of the People table... until you do that, the "sort" icons, which we'll need in a moment are grayed out. Why? Don't know!
Click in the PersonID column. (On the heading or the data- doesn't matter). Click on the "Sort Ascending" icon. (If you hover, you get the button name. It is on the form navigation toolbar, a blue "A" over a gray "Z", with a down-pointing arrow to it's right.
That should have sorted the data in the table to put it in order by the "PersonID" value. Save the form, reopen it for ordinary use.
While open in ordinary use, you can re-sort the table as you see fit... but the sort won't be persistent. As you enter data, the new data does NOT immediately "pop" to its proper place in the table... but it will if you press the "refresh" button. (Not the Windows "refresh", F5... the ooBase "refresh"... two curved blue arrows near the sort button.) Even if you don't hit "refresh", the next time you re-open the table, the new data will have been moved to its correct place, according to the sort rule you defined while editing the form's design.
Return to start of "HowTo"s.- - - - - - - - - - - - - - - -
The forms described above are all very pedestrian... but with them alone, a user could manage the data we've set out to manage. The queries, forms and reports that follow are more "clever"... and less easy to set up (!)... but they make it much easier for the user.
- - - - - - - - - - - - - - - -
A human freindly display- fForSimple1 (also uses qForSimple1) (How to make)
Query based form to yield human friendly results.... we will end up with...
A quick word about "Queries": One of their features is that they create tables. Query- produced tables can be used by forms to present data.
Before you start this, define the relationships in the database, as set out just before the "Descriptions" section on this page.
Go to the ooBase project managemant window, se the task to "queries", and invoke "Create Query in Design View".
A window will open, and in front of it there will be a "Add Table or Query" dialog. Our database's three tables will be listed. Double click on each of them. This will cause representations of the tables to appear in the upper panel of the query design table. The previouslyt defined relationships will also be shown. Drag the table representations around, and they become easier to see.
Which fields you chose to include in the output are, up to a point, up to you. the ones I've chosen to include in this example are not the only logical choices.
Across the bottom of the query designer you will see several columns to the right of some row labels, to wit: "Field", "Alias", "Table", etc.
Start with the "Field" cell of the left hand column. It it doesn't already have a small "v" at its right-hand end, click on the cell, and the "v" will appear. Click on it for a list of the available fields. Click on "Projects.ProjID". Then click on the "Fields" cell in the next column. A tick should appear in the "Visible" box for the first column.
Select People.Firstname for the second column. Proceed thus, selecting "People.Surname" and "Project.description" for the subsequent columns.
Save the query you hae created; call it "qForSimple1".
There's a "Run Query" button. Click it and you get a four column table. It may not seem too remarkable at first.... but look at it closely. It is listing the projects... not too surprising. But look at what's listed with them. Instead of the cryptic codes used to identify projects and people, good, human friendly information is listed. We can see who is working on which project. But this is just the start!
In a moment, we're going to do something better to display the data from the query, but as an aside....
Once you have saved a query, right click on its name in the ooBase main project manager window. Click on "Copy" in the sub-menu. Open an ooCalc document. Right click on a cell. Click on "Paste" Presto! Your data appears! The cheap and cheerful way. Now we'll move on to something better!,/p>
Go back to the ooBase main project manager window. Click on "Forms" in the left hand column. Click on "Use Wizard to Create Form".
Step 1: Select "Query: qForSimple1". Move all of the Available Fields into the Fields in Form memo.
Step 2: No subform needed. Just click "Next", which will skip you to Step 5.
Step 5: Arrange controls as a Data Sheet.
Step 6: Specify the form to display all data, and not impose restrictions. (The permissions are easily changed later, if you wish.)
Step 7: Any style is fine.
Step 8: Call the form fForSimple1, and click "Finish". A nice, human friendly table appears. Close it, and reopen for edit to fine tune, setting column widths, etc.
That's it! Done!
Return to start of "HowTo"s.(How to make)
x x x x A placeholder for a future item.
Return to start of "HowTo"s.(How to make)
x x x x A placeholder for a future item.
Return to start of "HowTo"s.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 . . . . .