Hello. I am trying to learn how to use LibreOffice Base 7.5 (64k) version on a Windows 10 desktop.
.
On my form, I need two Date fields that automatically insert the:
** date that the record was created, and
** the date that the record was last updated.
.
I have inserted these two date fields from my table onto the form, but don’t know what to do next. Does a command need to be entered in the “Default Date” option under the General tab of the Control Properties?
Thanks!
Usually I would suggest to write this information via TRIGGER to the records, but you may be interested in another solution, as a trigger may only set the value after update…
.
Another problem: The solution deoends on the actual database you are using, as for example in the following discussion:
Thank you.
Did it this way with internal HSQLDB:
Default dates for the fields won’t be shown before entering data to the HSQLDB. Fileds for dates might be write protected. So you could only see the result after saving the data.
Open Tools → SQL
ALTER TABLE "Table" ALTER COLUMN "CreateDate" SET DEFAULT CURRENT_DATE;
The field “CreateDate” will be set to the ‘Createdate’ of the row.
For updates you will need a command like
UPDATE "Table" SET "UpdateDate" = CURRENT_DATE WHERE "ID" = …
This could run by macro in HSQLDB. You will need to get the value for the primary key (here: “ID”) from the form.
If you will use Firebird instead (also included in LibreOffice, but could only be created with experimental features on …), you could use this trigger (input once in Tools → SQL):
CREATE OR ALTER TRIGGER BEFORE_IN_UP_TABLE FOR "Table"
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
NEW."UpdateDate" = CURRENT_TIMESTAMP;
END;
I think in Firebird can be done at field creation.
create table MyData (
id int not null primary key,
record_created timestamp default current_timestamp,
...
)
.
Yes this works just fine (current test) but OP has asked for two different fields - one date at creation and another at each update.
.
Still agree with @Wanderer about a Trigger in other than HSQLDB embedded.
@mariosv Most people create a table in GUI of Base. I did it the same way and changed the field for the date after creating the table. Your solution will also work for internal HSQLDB.
Now the question for internal HSQLDB 1.8 : How should a trigger work for this special feature setting a CURRENT_DATE or CURRENT_TIMESTAMP while updating a row? I don’t know a running code for this - also after looking at description for internal HSQLDB and looking at the example link posted by @Wanderer So I will always use a macro for create a new date for updating a row.
THANK YOU so much, RobertG! Both of these SQL codes to automatically enter the “CreateDate” and update the modification UpdateDate work great for me so far.
.
By the way: The database I am using is HSQLDB embedded.
.
.
The following additional questions will really show my complete ignorance about Base, but I’m going to ask them anyway. Lol.
.
It is not to difficult to move the data to another database, but depends a bit on the size of your data. For example you may read about SQL DUMP.
.
More problems wait to recreate all queries, as Syntax varies. Some databases allow || to concat strings, some need a CONCAT()-function. I’m using IFNULL() in SQLITE, while others may use COALESCE. The more specific code you have you increase the necessary work to migrate to another database…
.
One hint: It is usually relativly easy to switch from the embedded databases to installed “split-db” of the same type like HSQLDB-embedded to HSQLDB in server-mode.
@DMG
There is also the option to upgrade (make copy of Base file before trying this as not always successful) from HSQLDB embedded to Firebird embedded by turning on Experimental features. From Tools->Options:
.
@RobertG Well… I now have 2 follow-up questions with which I hope you’ll be able to help me:
.
[Using LO version 7.5 (64k), with HSQLDB embedded, on Windows 10 desktop.]
.
Your solution to CreationDate is working fine for NEW records (understandable). But my database was imported from MS Access, so now I have over 1,200 records with an empty CreationDate field (because these records were pre-existing). [I tried to copy the CreationDate column in the Calc spreadsheet, and then right-clicked on the table’s name… but the “Paste” option is disabled.] IS THERE A WAY to copy and paste my old original dates in?
.
The SQL code worked fine in my Test database because I named the Unique ID field “ID.” But I’m having trouble with the SQL code in my real database where I have the following:
. Table Name: tblVideos
. Unique ID field: fldVideoID
. Update Date: fldUpdateDate
.
I entered and executed the following SQL Code because I thought this was the correct syntax, but…
.
UPDATE “tblVideos” SET “fldUpdateDate” = CURRENT_DATE WHERE “fldVideoID” = fldVideoID
.
Unfortunately, this code gave me the following error message: Column not found: fldVideoID
.
How do I construct this code to update the current record?
@DMG
Not trying to influence your direction, but had the chance to create a simple Firebird example. Used SQL from @mariosv as basis to create table and Code from one of the links I posted for a Trigger. The code for each is saved in Queries within the sample (cannot execute from there - only from Tools SQL).
.
Sample → FirebirdTriggerSample.odb (3.3 KB)
Just for test, enter directly into table view only a unique id & name:
Then arrow down to next record:
Next change the name & go to next record again:
.
That should give you some idea of what is talked about above. Datestamp automatically inserted depending upon new or modified.
