Ask Your Question
0

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

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

mrmister gravatar image

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

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 +0100

frofa gravatar image

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

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
0

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

mrmister gravatar image

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

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 +0100 )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 +0100 )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 +0100 )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 +0100 )edit
1

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

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

Question Tools

2 followers

Stats

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

Seen: 10,752 times

Last updated: Dec 12 '13