[Solved] Calculated Fields

[Hoylander]

Hi, I need to create a simple database, probably only needing one table. My main need to to have some of the fields automatically calculate their values. So, in simple terms, I enter a description, price and purchase date of an item. Now I want the 'current value' field to calculate automatically knowing today's date (in the system) and the purchase date, the price and also knowing the depreciation rule, e.g. 20% per year. I built all this long ago in FileMaker Pro 3 and now I want to rebuild it in a modern database. I can't see any way to do this from the available user information.

[r4zoli]

Welcome to the forum!
Please search on forum before post, your problem possibly solved and can find it with search facility on right upper corner.
See topic: http://user.services.openoffice.org/en/ ... =13&t=7902

[Hoylander]

Thanks, but I did a search on calculated fields before posting and I've just done another one now. There seems to be nothing useful on the subject. There is some complicated stuff about SQL (which I've used with Access databases) but nothing on simple calculations in a field. When I start defining fields, I can choose Text, Number, Scientific, etc, but Calculation... is not an available choice. Does Base in fact support calculated fields in a table? I simply want to do things like calculate a current value from IF(ItemType = D, PurchasePrice*0.6, PurchasePrice*1.03). In words, if the ItemType field contains "D", then current value of item equals the purchase price x 60%, otherwise current value is purchase price x 1.03. I would also want to include time-dependent calculations. Where do I find out how to do such calculations, assuming it's even possible? I could do this in FileMaker Pro 3 ten years ago.

[kabing]

You have to create these calculated "fields" in a query, not in a table. (It might work in a view, too, but I'm not sure).

The Built-in_functions wiki page discusses the functions available.

kabing

[Hoylander]

Thanks, Kabing, but I've spent many hours sweating with this and have got nowhere. For practice, I quickly built what I wanted in MS Access (which worked), although I fail to see why calculations only work in Access queries in not in tables. I then tried to replicate the process in Open Office and despite crawling over the limited help pages and the web generally, I got nowhere. In Access I was able to build a function "PresentValue: [UnitPrice]*(1-((Date()-MainTable!AcquisitionDate)/365.25)/[ExpectedLife])", but nothing works in Base. I eventually found the CURDATE() function on the web (instead of Access's Date()), but I could not even get an expression like "CURDATE() - AcquisitionDate" to work in a query. I just got repeated error messages. There's just no way of knowing what I'm doing wrong.

I suppose my conclusion is that Base is not intuitive enough for end users to work with and maybe you already have to be an expert to do anything. That's a shame, as I want to use my Mac and not be forced to use Access on a PC. I could use a new version of FileMaker Pro, but that's a big investment for the simple things I want to do. My 10-year old FileMaker Pro 3 works fine, but the hardware it runs on is not going to last much longer. Maybe somebody will come out with a Base for Dummies one day, or Wiley will produce a Base Bible.

[kabing]

If memory serves, calculations with Dates aren't supported in Base. So that may be part of the problem.

Base is the newest of the modules, and you are correct that in many ways it is not particularly user friendly.

kabing

[Sliderule]

Holylander:

Welcome to the world of OpenOffice Base.

I will assume, your database is the 'default' . . . HSQL database engine . . . you can confirm this by looking at the bottom, on the status bar after opening your database file.

Let's break the question down into a few pieces:

1. Functions: to perform 'calculations' besides to +, -, /, * . . . a list and description may be found at either of these two:
   • http://wiki.services.openoffice.org/wik ... Procedures
   • http://www.hsqldb.org/doc/guide/ch09.html#N1251E
2. Fields ( sometimes called Column ) names are 'wrapped' in double quotes ( " ) rather than brackets
3. A Table stores the atomic data, so, calculations are not stored in the table. But, you can perform the calculations on the data, either with a Query, or, a View -- a pseudo-table -- that does not contain data, but, retrieves data and can perform calculations
4. Date / Time calculation: Please see the link below with my explanation, for an example . . . using the function DATEDIFF
   
   http://user.services.openoffice.org/en/ ... 99&p=35447

Bottom line . . . something like:

Code: Select all

[UnitPrice]*(1-((Date()-MainTable!AcquisitionDate)/365.25)/[ExpectedLife])

might look like this in a Query or View:

"UnitPrice" * (1 - (DATEDIFF('day',CurDate(),"AcquisitionDate") / 365.25) / "ExpectedLife")

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.

[Hoylander]

Hi Sliderule - a million thanks, your formula worked (apart from a minor error, easily fixed). The question now is, where can I find a list of all the available functions, including "If this, then that, otherwise that" (conditional function), with their correct syntaxes? Also, I'm finding it quite clumsy to switch between design and view modes. I seem to have to back right out of each mode and then come back in again and right-click to find the edit mode. There's no obvious little mode switching button as in MS Access. Anyway, the main thing is, what I want to is probably possible; it's just a question of how much effort it will take to get there.

Again, many thanks.
Hoylander

[Sliderule]

Holylander:

You stated:

your formula worked (apart from a minor error, easily fixed)

Probably have to reverse the order of CurDate() and "AcquistionDate" in the DATEDIFF function . . . I was aware of that when I posted it . . . but . . . I did want you to experiment with it, and, learn from it.

You asked:

The question now is, where can I find a list of all the available functions, including "If this, then that, otherwise that" (conditional function), with their correct syntaxes?

Please reread the post I gave you above . . . and check for CASEWHEN ( one word ) -- just like an IF statement in a spreadsheet ( such as Calc or Excel ), and, a CASEWHEN function may be 'nested' :

http://www.hsqldb.org/doc/guide/ch09.html#N1251E

CASEWHEN(exp,v1,v2)

if exp is true, v1 is returned, else v2

You said:

Also, I'm finding it quite clumsy to switch between design and view modes.

I have no idea what you are talking about if you mean while creating a Query . . . unless you are not using either:

1. The button on the Toolbar . . . Switch Design View On/Off
2. From the Menu: View -> Switch Design View On/Off

Sliderule

[Hoylander]

Hi Sliderule,

You stated:
Probably have to reverse the order of CurDate() and "AcquistionDate" in the DATEDIFF function . . . I was aware of that when I posted it . . . but . . . I did want you to experiment with it, and, learn from it. Indeed, that was the error.

You stated:
Please reread the post I gave you above . . . and check for CASEWHEN ( one word ) -- just like an IF statement in a spreadsheet ( such as Calc or Excel ), and, a CASEWHEN function may be 'nested' : Thanks, I see that everything is explained here.

You quoted this conditional function:

CASEWHEN(exp,v1,v2)

if exp is true, v1 is returned, else v2. This indeed seems to be what I am looking for.


You said:

Also, I'm finding it quite clumsy to switch between design and view modes.

I have no idea what you are talking about if you mean while creating a Query . . . unless you are not using either:

1. The button on the Toolbar . . . Switch Design View On/Off
2. From the Menu: View -> Switch Design View On/Off

That's not how my version of Base (the latest) seems to work. With the query having been opened and being viewed, there's no button to switch back to design view. With the query closed and listed under 'Queries', there is an Edit button that gives access to the design view of the query. The design view can also be accessed by right-clicking the name of the query in the query list. Once in design view, then there is a button that says 'switch design view on or off', but it doesn't actually do that - it just switches to SQL view, which is really an alternative design view. I'm talking about switching between datasheet view and design view. In Access (sorry to mention that) there is a handy button that switches between datasheet view, design view and SQL view. Datasheet view is of course what you see having run the query. Anyway, not to worry, I see the way it works. It just means I have to close the query before I can re-open it in edit (or design) view.

Hoylander