AUTHOR'S MAIN SITE
> > > > >
TABLE OF CONTENTS for version 2 Open Office database tutorials.
Open Office 2 Base (database) Tutorials
Putting Calculated Fields on Forms
You may find that the database being shipped with OpenOffice (ver.2) delights you as much as it has me. This page, and the others linked to it, can help you use it.
Forget any experiences you may have had with Adabas, which came with Star Office, the commercial version of Open Office 1. The Open Office Version 2 database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including Base, is free! But don't let that fool you. It's not new. Big organizations, civil and governmental, are adopting it as their standard office suite... and saving million$.
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.
Page contents © TK Boyd, Sheepdog Software ®, 3/06.
The Plan_______________
Part of the art of creating good databases lies in choosing what fields will appear in the tables. The tables are the bedrock of any database. There are serious rules about what should and should not be in them... try to find discussions of data normalization to help you start learning about the rules.
The rules support a couple of ideas:
-Don't enter anything twice in the database.
-Keep things simple.
-Avoid entering things you don't need to enter.
In connection with that last point, consider the following database application, and try to imagine the many, many similar situations.
Imagine that you are keeping track of some stock market investments. (Yes, I know that what follows would not serve an investor's real needs. It is just to illustrate a point.)
In the database we are going to have a record for each holding. If Fred had 100 shares of IBM, 50 shares of Google, and 10 shares of Exxom, then he'd have 3 records in his database. Each record would have the following fields: Company name, Number of shares owned, Value of a single share. (That last field would be a pain... if Fred wanted an up-to-date idea of his shares' total value, he'd have to go into the database and change the values in each of the "PerShare" fields.)
You might think it would be nice to have a "Value of the holding" field. I.e., if IBM were worth $80 per share, Fred's 100 shares would be worth $8000. This would be a Bad Idea: In a sense you would be entering something twice (The "Value of Holding" field would, essentially, duplicate a combination of the "Shares" and "PerShare" fields), and it would break the "Don't enter things you don't need to" rule.... but that doesn't mean that Fred can't know from the computer that the IBM holdings are worth $8000. How he gets that information, the right way, is the subject of this tutorial!
Executing the plan_______________
Some earlier tutorials give you more support than this one does. If you find it difficult to complete the tasks specified below, you might want to work though some of the earlier tutorials first.
Create a table called "Main":
Create it with three fields, any will do, and don't edit them within the wizard (in step 2). Step 2 seems to be there to let you change names, field type, etc, and seems to work... but then at the end of the wizard Bad Things Happen. I'm afraid this is a bug(?) (ooBase 2.0), but, Good News: You can change those things, just as easily, in a moment, after the wizard has started the table for you. In step 4, ask the wizard to create a primary key for you, "automatically", with auto increment.
When the wizard is done, rename, re-define the fields so that you end up with the following names and field types, with lengths, etc, as shown:
ID (Integer) <- leave as wizard provided
Shares (Integer)
PerShare (Number, length 16, decimal places: 2)
Company (Text[varchar], length:5)
Put some data in the table. Two records will suffice.
Close the table.
Use the wizard to create a query.
Include ALL of the fields. (I did this exercise once before, but excluding the ID field, and found that I could not edit the records in the table via the form. Anyone know why not?? (Click here for my eddress))
Do not set a sort order. (You probably could, but I'm trying to keep this simple.)
Do not set any search conditions.
Step 4: Accept the default "Detailed" type query.
Step 5: Take the table's name off of the aliases. (E.g., make the alias for Main.Shares" just "Shares", etc.).
Call the query "QueryWithCalc"... even though it doesn't have a calculated field yet!
Before you leave step 8, select "Modify query" for what happens next.
The main design window for the query should open. Across the bottom is a table, with three populated columns. Click in the "Field" (first) cell of the fourth column. Enter....
Shares*PreShare
... being careful to use the same capitalization as you used in naming those fields... it does matter. Do not be alarmed when ooBase puts some quotation marks around the names.
Fill in "Holding value" for the alias of the fourth column.
Put a tick in the "visible" box.
Save the query definition and run it. You should see sensible results, and you can alter the contents of the database.... if all is well. (Don't fool with the "ID" field's contents.) (The time I tried making a query without the ID field, I could see sensible results... but not change anything.)
N.B. Suppose you change the number of shares in a record. The "Holding value" will not change immediately. It will change when you...
Go to a different record
Click on the "Save Current Record" icon
Close the query.
So! We have a query that fetches the number of shares, and the value per share from the table, and then works out for us the value of the holding. In a sense, we have achieved our goal... but it would be better to press on, and complete additional steps....
Now use the wizard to create a new form.
In step 1, select our newly created query "QueryWithCalc", and ask for all of its fields to be included in the form.
Don't set up a sub-form.
I used a datasheet arrangement; I believe others work, too.
In step 6, set data entry to "display all", without change restrictions.
Any style will do.
Use "FormWithCalc" for the form's name, or "FormBasedOnQueryWithCalc", and tell the wizard that you want to work with the form after it is created.
Click "Finish", to complete the wizard, and you should find you have a good interface to the table, with the calculated field telling you the value of each holding.
Ta da!
The changes you make when working with the form (or the query) do percolate back through the system to the underlying table. In other words, you are working with the table, not some "scratch copy" thereof.
I hope you have a sensible result, after following the steps above.
Postscript_______________
I was puzzled why the following would not work. It seems so close to what we did before...
I used the form wizard as follows....
Step 1: Table or query: Table:Main
": Fields: All
Step 2: No subform. (Steps 3 & 4 are skipped over)
Step 5: Datasheet
Step 6: Have form display all data, not restrict changes.
Step 8: Name form FormWOutQuery, work with form immediately...
.. which worked. I then went on, opened form for edit, added a new column, type numeric field, Data field (on "Data" tab): "Shares"*"PerShare".
When I ran that form, I got nothing in the new column. Sigh. It seemed so close to what I was doing through the more complex route of table -> query -> form. Perhaps the query is providing essential "glue". Oh well... we have something that works!! If anyone knows why one works and the other doesn't, I'd be glad of help.
Editorial Philosophy
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.
Ad from page's editor: Yes.. I do enjoy
compiling these things for you... I hope they are helpful. However..
this doesn't pay my bills!!! If you find this stuff useful, (and you
run an MS-DOS or Windows PC) please visit my freeware and shareware
page, download something, and circulate it for me? Links on your page
to this page would also be appreciated!
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
One last bit of advice: Be sure you know all you need to about spyware.
. . . . . P a g e . . . E n d s . . . . .