Ask Your Question
2

How can I use functions & formulas in database fields? [closed]

asked 2012-12-10 23:16:26 +0200

anonymous user

Anonymous

updated 2015-09-12 21:12:54 +0200

Alex Kemp gravatar image

I am a long-time user of Microsoft Works, since it first came out (latest version 4.5, circa 1998), and am now looking at moving over to LibreOffice. I have had to stop upgrading at version 4.5 since later “upgrades” have not been able to work with my files and I’m concerned about how long I can carry 4.5 with each system upgrade (currently working on Vista).

Databases are what I use most in my business. One primary database I create annually has an average of 100 records with 55 fields - 10 fields of which have either a formula or a function and 7 fields have fixed data (unless I need to change it). I’ve been able to move my Works databases to LibreOffice, however, I’m having trouble figuring out how to plug formulas and functions into LO database fields. Examples of these, using references to other fields, are:

=IF(Deposit>0#OR#Payment>0#OR#Balance=0,"Booked","Tentative"),  
=IF(Balance=0#AND#(Paymt Posted>0#OR#Total Due=0),"Paid",IF(Date Start NOW()>30#AND#Balance>0,SUM(Date Start-30),NOW())),  
=IF(Time Start>0,Time Start-0.04166," "),  
=Total Due-(Deposit+Payment)

I don't have any problems with the formulas or functions - yet. My problem right now is how to plug a formula or function into a database field.

Any suggestions would be appreciated.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 09:53:16.277969

Comments

@Skipper -- Still looking for an Answer to this question?

qubit gravatar imagequbit ( 2013-03-10 04:47:28 +0200 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2014-02-12 11:04:23 +0200

frofa gravatar image

updated 2014-02-12 11:06:16 +0200

SQL statements allow the use of FORMULAS for calculations and text manipulations using operators and functions such as arithmetic operations on numerical field values, concatenation and other manipulations of text field values, date and time arithmetic, and so on. The syntax is rather different from that used in spreadsheets (Calc), and each database 'engine' has slightly different syntax. See HERE for more information on the HSQLDB (version 2.3) engine. NOTE: The default DB 'engine' for LO is still HSQLDB v1.8.

Generally, it's not usually necessary to update (i.e. actually store) such a calculated column's values in your database table; but instead simply to create a temporary column 'on the fly' when the SQL query is executed (more economical). Below is an EXAMPLE of SQL for a calculated column.

SELECT "FIELD_1"+"FIELD_2"+"FIELD_3" AS "TOTAL" FROM "YOUR_TABLE";
edit flag offensive delete link more
1

answered 2012-12-11 00:13:06 +0200

m.a.riosv gravatar image

Because OR and AND work like a function no like an operator, and if you are using named ranged, no spaces are allowed in the names.
=IF(OR(Deposit>0;Payment>0;Balance=0);"Booked";"Tentative")
=IF(AND(Balance=0;OR(Paymt Posted>0;Total Due=0));"Paid";IF(AND((Date Start-NOW())>30;Balance>0);SUM(Date Start-30);NOW()))

Also in LibreOffice the function separator usualy ; but it can be set in Menu/Options/LibreOffice calc/Formula

edit flag offensive delete link more

Question Tools

Stats

Asked: 2012-12-10 23:16:26 +0200

Seen: 11,794 times

Last updated: Feb 12 '14