Hello.
I’m very new to Libreoffice Base, but I could build a datebase quiet well, but I can’t figure out how to do this:
In a table I have two fields: “birthdate” and “age”. In the form I created from this table I want the age to be calculated and autofill the “age” field when the customer fills the “birthday”. Just that, but I have no idea to do it.
Could you please help me.
Thanks
Try basing your form on a query, not a table. The query can have a computed age. See this page for methods to compute age from birthdate (when using the embedded HSQLDB engine). Here is one of the methods from that page, assuming your birthdate field is “BIRTHDATE” in table “MYTABLE”.
SELECT "BIRTHDATE", YEAR(CURRENT_DATE) - YEAR("BIRTHDATE") - CASEWHEN(MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE) >= MONTH("BIRTHDATE")*100 + DAYOFMONTH("BIRTHDATE"),0,1) AS "AGE" FROM "MYTABLE".
In your form set the AGE field to not require input. Then when the user enters a birthdate and saves the record, the age will be filled in on the form.
If you plan to have untrained users enter a birthdate, be sure to give them guidance about how to do it or some will surely get it wrong.
EDIT If a table containing BIRTHDATE is your main form, add a subform contaning a formatted field (to display age). In the Form dialog for the subform formatted field make the content type a SQL command. The content will be a select statement containing both BIRTHDATE and an age computed from the BIRTHDATE, i.e. SELECT BIRTHDATE, [sql to convert BIRTHDATE to age ] AS AGE FROM MYTABLE. Still in the Form properties control link master field BIRTHDATE to slave field BIRTHDATE. Now in the Control dialog for the formatted field make the data field AGE.
Thanks for your answer.
The thing is that I need the form to be based in the table because it has a lot more fields (I didn’t name them for making an easy question). The form has even two different subforms. I tried to make the field “age” in the form to be based in a query, but I couldn’t find the way to do it.
Edited my answer.