Posted: Tue Feb 01, 2011 8:31 pm Post subject: Calculated fields in Neooffice Base
I have used Microsoft Works 4.0b (MSW) for Mac for many years, but find it is getting flaky in its old age under current versions of OSX. I am using NeoOffice 3.12. Patch 2, which I think is current.
Most of the work I do is in flat file databases, no need for much more.
I have been able to move most of my information handling requirements to NeoOffice, but the database app is stumping me big time. I can SEE relationships to MSW and the file structure, tables, queries/searches, reports, but seem to get hung up on a couple of issues, and can't find information to get me over these humps:
When setting up View, the Function, Criterion and several Or fields and how they work is unclear. Can't figure out what to enter and what I want to enter: formula, etc., isn't an option.
I also used a calculated field often, but don't understand how any two fields can be related with a calculation involved. For example, in a current database, we list checks for our business. I now enter the amount w/o decimals, 1234500. A second field multiplies this by .01 and yields 12345.00. How does this work in Base?
With these issues resolved, I am at least on the journey, but so far I can't even find the map!
Posted: Tue Feb 01, 2011 8:51 pm Post subject: Re: Calculated fields in Neooffice Base
ROGERCHRISTIAN wrote:
When setting up View, the Function, Criterion and several Or fields and how they work is unclear. Can't figure out what to enter and what I want to enter: formula, etc., isn't an option.
I also used a calculated field often, but don't understand how any two fields can be related with a calculation involved. For example, in a current database, we list checks for our business. I now enter the amount w/o decimals, 1234500. A second field multiplies this by .01 and yields 12345.00. How does this work in Base?
I don't know how Microsoft Works is structured, but what you describe above sounds more like a spreadsheet than a database. Databases don't have formulas and, instead, you would create a database query that has such a formula in it.
If your database only has one table - the check listing - I think that you may be find it much easier to use Calc spreadsheet instead of a Base database. If so, here is how you would convert your database table to a Calc spreadsheet:
1. Open your Base check listing table, select all rows, and copy the the selected rows.
2. Select the File :: New :: Spreadsheet menu and in the new spreadsheet that appears, select the Edit :: Paste menu.
3. Move the selected cell to the first empty column in the spreadsheet, type in the following formula, and press the Return key. This formula assumes that you are in cell G1 and the check amount without decimals is in column C1:
=ROUND(C1*.01;2)
Does the above steps work? If so, you can then copy the cell with the above formula by selecting all of the cells in that cell's column and pasting.
If I have misunderstood the problem or you have multiple tables in your database that you run queries against, let me know.
"Databases don't have formulas and, instead, you would create a database query that has such a formula in it."
True these days, but in old-school flat databases (like dBase and some others) you would get 'symbolic' fields that use formulae to reference the data in other fields (e.g. to total a range of numeric fields in a record). So it's quite possible that MS Works DB has symbolic fields rather than queries, given the bizarreness that is MS Works in general.
There are some simple formulas that you can use within Base query. You won't be able to see or use such formulas when editing a table, but you should be able to produce a computed column when you run a query.
To see a computed column like in your example, you will need to create a query directly using SQL query language. If you download and open my attached New Database.odb database, click on the Queries icon, right-click or Control-click on the "Query1" query, and in the popup menu that appears, select the "Edit in SQL View" menu. The following SQL query should then appear:
Code:
SELECT "Check Number", ("Check Raw Amount" * .01) AS "Check Amount" FROM "Table1"
In the above, "Check Number" and "Check Raw Amount" are real columns in the "Table1" table but my query does not display the "Check Raw Amount" column. Instead, my query multiplies that column by .01 and displays the results as a computed column named "Check Amount".
If you close the "Edit in SQL View" window and double-click on the query, you will see that the query actually displays 1/100th of the value in the "Check Raw Amount" column.
Is that closer to the functionality you are looking for?
Posted: Wed Feb 02, 2011 9:00 pm Post subject: I found a reference manual
This afternoon, while in the midst of my forum post, I found a book I had purchased "A Conceptual Guide to NeoOffice 2", which, by example, cleared up a lot of things which had stumped me (see above) and helped me work through some of the structure issues I had.
Thank you for your posts and info, I will look at the database example you provided and see if it helps.
With your busy schedule, it is a great help and assist for you to be so generous with your time.
Looked at the Base file, and what you suggested seems to do exactly what I want to do.
Thanks.
Roger
Last edited by ROGERCHRISTIAN on Wed Feb 02, 2011 9:06 pm; edited 1 time in total
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You cannot download files in this forum
