The date when the record in Base was last modified

[MarekKisly]

Is this possible to add to the Base a field which will show the date the record was modified?

[fpy]

it sure is :

Need Date fields that automatically insert the date that record was created and updated

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…

[MarekKisly]

Thank you very much for prompt response. I still do not know how to create it, I am sorry for my ignorance but I have no experience with databases except File Maker long time ago.
I understood that I need open Tools, than SLQ I typed to the field command to execute this command: SQL ALTER TABLE “Table” ALTER COLUMN “CreateDate” SET DEFAULT CURRENT_DATE, but do not know if this right.

I sent screenshot what I see.

Few questions it this the same command if I am using LO Base on Mac OS 14.2?
Before I go to "Tools’ is it relevant if I am in “Tables” or “Forms”?
Kind regards
Marek

[RobertG]

@MarekKisly : Start for the code is wrong: The right code will start with ALTER. Move away `SQL. You could see this if you click on link for the other thread.

[Villeroy]

ALTER TABLE "Table" ALTER COLUMN "CreateDate" SET DEFAULT CURRENT_DATE
changes the column “CreateDate” in table named “Table”, so it writes the current data when no value is given for any new record. This is not what @MarekKisly asked for.

[fpy]

@Villeroy

This is not what @MarekKisly asked for.

unfortunately.

actually the anwser (…TRIGGER…) is in the following paragraph of the suggested solution post.
but seems it’s not just a question of syntax here

@MarekKisly

same command if I am using LO Base on Mac OS 14.2?

yes

@MarekKisly

Before I go to "Tools’ is it relevant if I am in “Tables” or “Forms”?

doesn’t matter.

@MarekKisly

no experience with databases

maybe you could give us an idea of where you are with the creation of your table(s)
attach an example.

[RobertG]

@Villeroy : Problem is: @MarekKisly doesn’t use the link to the other thread, where it is described for Firebird how it works without macros.

Need Date fields that automatically insert the date that record was created and updated

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!

[Wanderer]

@RobertG

where it is described for Firebird

Second problem: He never told he is using Firebird, all he wrote was LO Base on Mac OS 14.2 so I guess it is more likely he uses “HSQLDB embedded”.

[RobertG]

… and with HSQLDB I don’t know a way insert the current date while updating a row - except by macros.

[MarekKisly]

Thank you all for hints. I think I am closer to the solution.

1. I created in my table called “T_NBD” additional column called “CreateDate”.
2. I Pasted SQL Command: ALTER TABLE “T_NBD” ALTER COLUMN “CreateDate” SET DEFAULT CURRENT_DATE
   and click on ‘execute’.
   Status says that executed successfully abut 0 rows updated.
   I created a new record but the date did not appeared.
   See screenshot attached.
   
   

[MarekKisly]

Sorry, after the reopening the DB the date appeared for new record. Which is OK. Thanks for help.
Now the second topic: What is the command to add the date when the old record is modified?

[Wanderer]

@MarekKisly

What is the command to add the date when the old record is modified?

Read the thread. This depends on the type of database YOU use. And you didn’t tell.

[MarekKisly]

I am using LibreOffice 7.6.4.1 (X86_64) running on MacOS 14.2.
Thanks for your help.
Kind regards
Marek

[RobertG]

@MarekKisly : You have written witch version of LO you use. Base could connect to different databases. If you connect to the internal HSQLDB (have a look at the statusbar of opened Base file) you could only get the date a table has been changed by macro. It isn’t implemented for internal (very old) HSQLDB.
If you connect to Firebird (the experimental internal database) you could get the date for update a date by a trigger.
There are other databases won’t need a trigger for this. But this are databases, which need a server.