Can we have calculated fields in LibreOffice Base?

[mrmister]

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

[frofa]

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

[JRvZ]

Is this the same in Firebird?

[mrmister]

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?

[dajare]

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

[mrmister]

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.

[dajare]

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

[mrmister]

@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

[mrmister]

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