Can we have calculated fields in LibreOffice Base?

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

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’!

Is this the same in Firebird?

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?

@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!

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 - 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!

@dajare - 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

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