I am very much a beginner and could use some help. I am building a photography related database and would like to include some calculations in the main table. Here is an example including 4 fields. The first two fields are input to the table and I’d like the second two fields to be calculated with values returned to the table.
Cell B1 has 25 entered
Cell B2 has 180 entered
3a) =B1*B2
3b) =B3/60/60/24 format cell as Time [HH]:MM:SS
Thanks. I’m a real newbie… How do I input this to Libre Base?
Ahh sorry. I saw Calc in the tag and assumed it referred to Calc. I don’t use Base so can’t help.
You could try it in SQL in different ways:
SELECT "Number", "Length", "Number" * "Length" AS "Duration" FROM "Tablename"
You will get the result in seconds.
Next way is to do the same as in Calc:
SELECT "Number", "Length",
"Number" * "Length" *1.0000000000/(60*60*24) AS "Duration" FROM "Tablename"
You will get the duration as a decimal value. Note you have to multiplicate with something like 1.0000000000 to get enough decimal places. Without this you won’t get any decimal places and the duration will be 0. You could use this value in a formatted field, formatted as time.
And there is another way to get the “time” as string. This is nice to see, but you couldn’t calculate with this time any more.
SELECT "Number", "Length",
"Number" * "Length" / 3600 AS "Hours",
MOD("Number" * "Length", 3600) / 60 AS "Minutes",
MOD(MOD("Number" * "Length", 3600), 60) AS "Seconds",
FROM "Tablename"
This query shows the hours, minutes and seconds in different columns. You could concatenate this with ||':'|| to a string, which shows the time as text. But you have to set 2 leading ‘0’ for the minutes and cut the string after 2 characters from the right. If you won’t do you will get something like 8:1:0, which isn’t really what you want.
As it was not mentioned: For databases you usually avoid to store calculated fields. So @RobertG gave a solution, where you calculate the derived values “on the fly” in a select statement.
.
To use this in a form you may need to refresh/reload values, and your form needs the query as source of data, not the table.
.
If you really wish to write to the table you could use an UPDATE command. But for Base you would need either a macro or manual input at the sql-command window.
On the fly calculations, the results of which are stored in the table containing the values used to calculate, are usually obtained through the definition and use of SQL “triggers” or stored procedures. They wouldn’t be considered the easiest of objects to learn if you are a newbie wrt to databases, and their syntax differs slightly between database engines.
You would need to read the documentation for the database engine you want to use to decide whether, and how, you might use a trigger in such a circumstance. Also note that the LO UI doesn’t provide for a helpful interface for you to do this as the statements involved are considered to be outside the scope of “normal” use paradigm as conceived by the initial developers of the Base module. You will either have to use the Tools > SQL menu within LO Base, or the corresponding database command line tool, or some other editor if creating triggers or stored procedures requires the creation or modification of configuration files, for example.