AUTHOR'S MAIN SITE  »  TABLE OF CONTENTS for Open Office database tutorials.
Delicious Bookmark this on Delicious   Recommend to StumbleUpon

Open Office Database Tutorials
Putting Calculated Fields on Forms

You 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-3/10.



The Plan_______________

Part of the art of creating good databases lies in choosing what fields will appear in what 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 those rules.

The rules support a couple of ideas:

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...

....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, and that "8000" could be stored in the table. This would be a Bad Idea: In a sense you would breaking the "Don't enter something twice" rule (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 make the computer work out for him that the IBM holdings are worth $8000! How he gets that information, the right way, is the subject of this tutorial!

Before we proceed: A detail. Investors will want to know the value of their shares on specific dates. The per share value of a share varies from day to day. The database, as described here, will either need all the prices re-entered for whatever day is of interest, or a more complex database ("easily" created) will be needed. To keep things simple, which is all we need for the skills under consideration, just think of this database of a way to know the cost of different investments, and imagine that the PerShare figure records what was paid for the shares on the day they were purchased. (And yes, I do realize that if this database were to be used in the real world, the date the shares were purchased would also be in the table.)

And a bit of bad news before we proceed: As described in the main part of this tutorial, you can have a form to view information from a database, complete with some calculated fields (i.e. the "Value of holding" information). However, you will have, for now, to use a separate form if you want to make changes to any table or tables underlying the form with calculated fields. And, more bad news, you will probably have to click buttons to make the "info display" form update itself after any changes on the "data entry" form. Sorry! I'm learning all the time, and may "crack" this one sometime... but for now, here's "a way", be it ever so crude...



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". Do it however you wish, with the wizard, or in design mode. Be sure to set the first field as the primary key, with the "Autovalue" property set to "Yes".

Define the fields with the following field types, names, lengths, etc, as shown:

  ID (Primary key, Integer, AutoValue)
  Shares (Integer)
  PerShare (Number, length 16, decimal places: 2)(This for price)
  Company (Text [varchar], length:5)

Put some data in the table. Two records will suffice.

Close the table.

Now that you have the table, there are two ways you can provide yourself with a way to edit what is in it. For simple things, you may need nothing more than a simple form. But if you want a calculated field displayed, then this morning (July 2009, and again March 2010) I can only do that with a form based on a query. (I could have sworn I had a form working with a calculated field, without a query in 2009... but I can't do it now.) The calculated field will show the value of the shares.

Create a query. I used the wizard.

Include ALL of the fields. (You may not need to, but for now: include them, just in case.)

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. This will allow you to skip a few steps.

Next step: In at least some pre-version 3.1 variants of ooBase, at the next step, the default aliases include "Main." in front of "ID", "Shares", etc. If you are using an old version, and seeing this, edit the "Main." off of the aliases. (E.g., make the alias for "Main.Shares" just "Shares", etc.). If you are using ooBase version 3.1, you won't need to do this, and the step where you will be able to see the aliases will be step 7. I suspect it was step 7 for a long time... but there was what I think was a typo in this tutorial, and the tutorial said the "Aliases" step was step 5. (If you can confirm that it was once step 5, I'd be interested. Or if you are still using ooBase 2.4, please check what it's "Aliases" step is for me? (And then upgrade yourself!)

Call the query "QueryWithCalc"... even though it doesn't have a calculated field yet!

Before you click "Finish" to 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 four populated columns. Click in the "Field" cell, the top one, of the fifth column, the first empty one. Enter....

Shares*PerShare

... being careful to use the same capitalization as you used in naming those fields... it does matter. Do not be alarmed if ooBase puts some quotation marks around the names. If you have a field name with spaces in the name, enclose the name in quote marks, e.g.

"Per  Share"

Next, fill in Value of Shares for the alias of the fourth column. (It doesn't have to be bold, I just wanted to avoid using quote marks to delimit the alias, as you shouldn't use them with your entry.)

Put a tick in the "visible" box, if it isn't ticked already.

Finally, 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.)

I hope you wouldn't expect to be able to directly change the "Value of Holding" entry? Think about it. You can try.. no harm will come of doing so.

N.B. If you find that you can change the number of shares in a record, or change the cost per share field, then when you do it, the "Holding value" will not change immediately. It will change when you...

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.

Step 2: Don't set up a sub-form, just click "Next".

Step 5: I used a "Data Sheet" arrangement; I don't doubt others would work, too.

In step 6, set data entry to "display all", without "change" restrictions.

Step 7: Any style will do.

Step 8: 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? Yes... if you are only trying to inspect data in the table, and learn the values of different holdings, i.e. the answer to "number of share multiplied by value per share."

At this point, this morning (9Mar10, ooBase 3.1.0, WinXP) I am able to change data in the underlying table via the query based form! As long as, for now, I only change data in existing records.... but see below.

I have wrestled with this sort of thing again and again, getting different answers every time! I have not (yet!) tested altering data in tables from forms displaying the results of a query pulling data from more than one table. That may be A Different Story.)

If at this point you try to add a new record, i.e. a new row of data, you will probably get "Error writing data to database... Input required... 'Value of Shares'...". But! We can fix this! There are two ways. In either case, start by closing the form.

First way to enable input of new records via query based form

This is a little more "cumbersome" that the second, but it appeals to my belief that the more checking you have the better. The second solution turns off more than you need to.

Open the form for editing. Right click on the column heading "Value of Shares" (the calculated field).

Click on the "Column..." option. A dialog headed "Properties: Formatted Field" should open. Select the "Data" tab. Set "Input required" to "No".

That should do it! Next I'll give you an alternative solution in case for some reason you don't like that. (Or in case it won't work for you. If it won't, and you are using ooBase 3.1.0 or later, please contact me, citing "fdb1calcf1", and I will look at this again.)

Second way to enable input of new records via query based form

The form should be closed. In the ooBase main project manager window, select "Forms" in the left hand panel, a then right click on "FormWCalc". Click on the "Database" entry at the bottom of the pop up menu. Click on "Advanced Settings". Remove the tick in front of "Form data input checks for required fields".

Save the edited form. Open it for normal use... you should now be able to enter data in the table through the form which is based on the query. If you can't, and you are using ooBase 3.1.0 or later, please contact me, citing "fdb1calcf1", and I will look at this again.)

If you worry (as I think you should) about the ramifications of this turning off of checks, I can at least reassure you that some checks remain in place. For instance, I tried to enter "xx" into a field that was of type "integer". The "xx" was converted to a zero.

If it won't work...

There is a "crude" answer you can use when you can't get data to pass back to your tables from a form: use multiple forms.... one (query based) with whatever you need to see, and another, simpler, form (or forms) for changing data in the table(s).

A Big Warning

Don't fall to the temptation of working directly with a query in hopes of changing what is in a table. When you change entries in the result of a query, it will appear to work, but you are only changing what you see on the screen, not what is in the underlying table.

Moving on....

I hope you have a sensible result, after working through what is above. Apologies if the answer is still flawed at this time... I've spent hours on this over the years. But do "complain" if need be!


Postscript_______________

Ha! Progress? Preliminary experiments suggest that the above techniques won't work if your query draws data from more than one underlying table. Sigh. But a least a candidate for why the thing "works" sometimes, and not others. So how DO we do it in multi-table situations?

An enquiry at the excellent oooFurum.org gave rise to this information from Villeroy, who has supplied many excellent answers for the community over the years. Note that he supports my idea that you need to include the primary key in the form or query, even if you don't display it. But note the additional point he makes: You DO need to display the primary key if its "autovalue" property isn't set to "yes". Also, alas, note that he says you can't use the techniques above on a query drawing information from more than one table.... unless, as they say, you know otherwise??

Your form is writable if it is bound to some record set
from a single table and if the table's primary key is included.

This means that the form is bound to the entire table and
the table is editable OR the form is bound to some row set like

SELECT  FROM "SIngle_Table" WHERE ... ORDER BY...

This row set is editable just like the table if and only if the
field list includes the primary key of the single table.

If the PK is an auto-value you don't have to display the PK
by means of a form control. Visible or not, It is part of the
form's row set.

Subforms follow the same rules. A pair of subform and
parent form reflects a one-to-many relation and makes
this relation editable. The editable parent selects editable
records in the subform.

As I said above, I've wrestled with this for some time. The following may be useful of interesting if you find that what's above doesn't Just Work.

In a discussion at the Open Office forum, two comments were made, which may be of interest:

The first suggests that what as of 9Mar10 I think works never has. But I've been through periods of not being able to make it work!

The second suggests up an interesting alternate path to a solution. I believe that answer is even suited to forms that display more than one record at a time. I wonder if it would require a full blown query, or whether the sub-form could be asked more directly just to display the result of "Shares"*"PerShare". (I didn't have much luck when I tried to do this without a query... but I live in hope that it CAN be done, at least for a form displaying just one record. (There are ways if you want to get into using macros.)(Adding a sub-form IS covered in another of my pages, but that page talks about a great deal else! (Adding the subform isn't hard... when you know how... but what you need to do in order to add a subform may not seem "obvious"... it wasn't to me, anyway!))

(Another discussion which may be useful if you don't mind using macros: oooforum thread.)

Another oooforum.org discussion which may be useful is a form that can BOTH be used to enter new data, AND which can display a calculated result from fields. Also uses a macro.

Hmmm... "stranger, stranger and harder...."

In exploring a question from a reader, 3 March 10, I've stumbled into something that may or may not be true, may or may not be useful....

My current best guess... needs work... is....

If you just want to look at some data, e.g. the investment data used as the example at the top of the page, AND have a calculated field, then The Way To Go is to set up a query to "harvest" the data, and calculate what will be in the calculated field... as set out in the main part of what starts at the top of this page.

** B U T **: If you want to ADD OR EDIT RECORDS via the same form, then things get a little tiresome. However, it MAY (P.S. I now (9 March 2010) think that the following is unnecessarily complicated, that the simple answer in the main part of this page WORKS.) be possible to proceed as follows. What follows is NOT extensively tested, and the text needs work, but may get you where you want to go, if you want to be a pioneer!

Set up a form. Put a datagrid on it, based directly on the underlying table. Continuing with the example I started with, on that you would DISPLAY the three "obvious" fields: Shares, PerShare, Company. I think you'd want the ID field available... it may be anyway.... but not showing.

Set up a query to return ID and "Shares * PerShare"

Add a subform to the main form, have it display the "Shares*PerShare" part of the query.

I THINK you can get that subform lined up on the screen with the main form, creating something that almost looks like one datagrid with 4 columns. The human won't know (or care) that the first 3 come from one control, and the last from a separate control... but that MAY get you around the problem of creating a form which can display calculated fields AND be used to edit the underlying table. (If any reader KNOWS this idea is a non-starter, or better yet, tries it and gets it to WORK!!, I'd be delighted to hear from said reader!)

============================

Lastly_______________

It makes little sense to show the record ID on the form displaying the table's data and the calculated values of the share holdings. It, in this example, is just an "internal" thing, of interest only to ooBase. I think that the ID field must remain in the query if you have a query based on more than one table (which may or may not be able to pass data back to the underlying tables, but you are allowed to do the following. (At the time of writing, I haven't tested these techniques with queries pulling data from multiple tables. Apologies if since writing this, I've gone on to the more advanced case, found it working, and forgotten to remove this warning.)

Open the form (the one based on the query) for editing.

Right-click on the "ID" column heading. Click on "Hide columns". Save your form.

The form should still work, but not display the ID column, which is a necessary column, for the computer, but one of little interest to a user of the database.



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.




Valid HTML 4.01 Transitional 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 . . . . .