Ask Your Question
0

Can we have calculated fields in LibreOffice Base? [closed]

asked 2013-12-11 15:52:34 +0200

mrmister gravatar image

updated 2013-12-11 21:38:03 +0200

David gravatar image

Hello I'd like to know if can we have calculated fields in LibreOffice Base. Please, realize I do not mean about getting the calculated fields when performing a query. I mean, to get the calculated field during the entry data process whilst you are in the table.

Example: I have a table with three fields:

  • FIELD 1 (INTEGER): First Quantity
  • FIELD 2 (INTEGER): Second Quantity
  • FIELD 3 (INTEGER): Result

So imagine the table, I am there... I enter 2 in the first field (First Quantity), and now in the next field (Second Quantity) I type 2 ... and now, what I want to see in the third field (Result) is the sum of First Quantity + Second Quantity, that is 2 + 2 = 4

May I have that whilst I am introducing data in the table, and get a field with calculated results? Or the only way to get calculated results is when performing a query? Thanks

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-16 16:21:04.555939

2 Answers

Sort by » oldest newest most voted
1

answered 2013-12-11 21:00:04 +0200

frofa gravatar image

updated 2013-12-12 10:42:04 +0200

You can use a QUERY or a table UPDATE to do arithmetic calculations. (Most people would just use a query, since usually there is no need to store the result in your table.)

For example: SELECT "FIELD_1"+"FIELD_2"+"FIELD_3" AS "TOTAL" FROM "YOUR_TABLE"

If you wanted to store the total in a field for each new row added, you would need to use a table UPDATE in a TRIGGER (currently available in HSQLDB version 2.x but not the default version 1.8).

More info about SQL UPDATES & TRIGGERS....

HERE is some additional info about the SQL UPDATE syntax. If the UPDATE statement is executed by a TRIGGER - see HERE - the TABLE-COLUMN ROWS referenced by the UPDATE statement will be updated when the TRIGGER is executed. See HERE for more info about TRIGGERS using the HSQLDB engine. Note: SQL UPDATE statements can also be executed using the TOOLS>SQL... menu in Base. A TRIGGER will simply execute such an SQL statement when a NEW record is created (and so update a 'TOTAL' column in your TABLE when a NEW row/record is created.)

Warning: do not 'play around' with SQL UPDATE statements without having a BACKUP of your database, because if the UPDATE does NOT work as expected, there is NO 'undo'!

edit flag offensive delete link more

Comments

Is this the same in Firebird?

JRvZ gravatar imageJRvZ ( 2019-05-01 10:22:59 +0200 )edit
0

answered 2013-12-11 22:57:09 +0200

mrmister gravatar image

updated 2013-12-12 01:52:20 +0200

Hello, thanks for you answer.

My question is about getting the calculations in the table and not doing queries. I know how to create calculated fields in queries, but I don't want that. I want to perform arithmetic calculations inside a field from my table. I have seen Access to do that, and I would like to do the same with Base. I wanto to store the result in my table.

You tell me... SELECT "FIELD_1"+"FIELD_2"+"FIELD_3" AS "TOTAL" FROM "YOUR_TABLE", is that SQL?

I don't understand what do you mean about a table update?

I want introduce 2 in the first field, then type 2 in the second field, and automatically in the third field (Result) getting the calculation. But I want to get the calculation automatically, without having to enter SQL commands and without having to perform queries. What I want is to define a field that add Field 1 + Field 2. However in the design view of the database I don't see any way to do this. I want the calculated field in the table, not in a form. I know also in a form you can create a calculated field, but my question is not that. My question is if can I create a calculated field when introducing data in my table, directly in the table mode, and not using a form. I have seen in Access you can create a calculated field, but in Base seems to be impossible. For that reason I asked this? if may I do this? but please, not in a query, not with SQL.

Do we have anyway to do this? In Access you have something called Expression Builder, I seen that is a feature never seen before, but only available in Access 2010.

I don't know if I am explaining well what I mean, but I want exactly what you can see in this video of Access:http://www.youtube.com/watch?v=B7NPQfjbyks

I wan't to do the same with Base. Is it possible?

edit flag offensive delete link more

Comments

@mrmister - Is it possible? The short answer is "no". (The video is very slick, though.) You can create a form with a default table ("spreadsheet") view, however. Why don't you want to use a query or expression? That seems an odd restriction when using a database. In fact, there are good design reasons for keeping calculations separate. // P.s. It would help other readers if you formatted your text as has been done in the original post. That continuous text is hard to read. Thanks!

David gravatar imageDavid ( 2013-12-12 00:38:56 +0200 )edit

As regard LibreOffice Base, okay, got it. No. It's not possible. In Access you can do it. I think the calculations in the table is cool, because you don't want always to start a form... thanks anyway.

mrmister gravatar imagemrmister ( 2013-12-12 01:47:38 +0200 )edit

@mrmister - thanks for editing your answer - better! As for "calculated fields" (not currently available in Base): you could file an enhancement request (or see this set of instructions). If you do, please add a comment noting the report number in this form: "fdo#98765", which will auto-link. Thanks!

David gravatar imageDavid ( 2013-12-12 09:07:09 +0200 )edit

@David - hi, thanks for your reply and your patience with me about editing the messages. I don't understand well what you suggest me to do to file an enhancement request? There are no instructions for that, just to report bugs and this is not a bug, this is a new feature. Are you telling me that I report a bug with the number Bug #98765 ? and describe there the enhanceded for calculated fields? please, let me know step by step I don't know how to deal very well with enhancements requests. Thanks

mrmister gravatar imagemrmister ( 2013-12-17 01:02:32 +0200 )edit
1

FILED THE ENHANCEMENT REQUEST. Here is the link:fdo#72787

mrmister gravatar imagemrmister ( 2013-12-17 13:38:15 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2013-12-11 15:52:34 +0200

Seen: 11,439 times

Last updated: Dec 12 '13