How can I use functions & formulas in database fields?

[Skipper1]

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.

[qubit1]

@Skipper1 – Still looking for an Answer to this question?

[frofa]

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";

[mariosv]

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