Base: "assign a calculated value to a form field"

[EasyTrieve]

Q1) In Base table Edit, for a “Date/Time [ DATETIME ]” type field, under Default value, how to I set the date/time to now, as in right this second, or =now()?

The only thing I can seem to enter for a Date/Time [ DATETIME ] field in the Default value box is an integer. Can one calculate a default value? There doesn’t seem to be any relevant help via F1 for the “Default value” input box.

I am converting a bunch of databases from MS Access and have many tables containing a Created field. In MS Access this is set to =Now() or similar functions.

Q2) Similarly, I have some fields named “Edited” that want to get updated with the date/time stamp when the record is edited. How do I do that? (This might have to be done at the Form level via an event, but I think the the Created fields in Q1 should be done at the Table level if at all possible.)

Using LO 5.2.2.2 on a MariaDB 10.1, but I would like a solution that is universal if at all possible.

---

This is really the same exact question, as was closed without ever being answered here.

Thanks.

[PYS]

Hi

Run Tools▸SQL , type (paste) this command (adapt to your table & column names):

ALTER TABLE "Table" ALTER COLUMN "Date" SET DEFAULT CURRENT_TIMESTAMP

Click Execute

Of course your field need to be Date/Time [Timestamp]

[EDIT]

If MS Access doesn’t support Timestamp, you can associate this script to the Before Record Action event.

I suppose you have two controls: Date Field & Time Field (named TSDate & TSTime in the script, adapt the name of course)

Sub PysTimeStamp

dim oCtrlDate as object, oCtrlTime as object

dim PysDate as new com.sun.star.util.Date
dim PysTime as new com.sun.star.util.Time

oCtrlDate = thiscomponent.DrawPage.Forms.getByName("MainForm").getByName("TSDate")
oCtrlTime = thiscomponent.DrawPage.Forms.getByName("MainForm").getByName("TSTime")

with PysDate
	.Day = day(date)	
	.Month = month(date)
	.Year = year(date)
end with

with PysTime
	.Hours = hour(now)	
	.Minutes = minute(now)
	.Seconds = second(now)
end with

oCtrlDate.Date = PysDate
oCtrlDate.commit
oCtrlTime.Time = PysTime
oCtrlTime.commit

End Sub

Regards

[EasyTrieve]

Thanks! This helps, and probably would work for most people. However, in my case I’m dual booting a database and so can’t change from DateTime to Timestamp because the MS Access I’m using on the other side doesn’t support Timestamp, so I’m stuck w/ DateTime. …Which now from a careful reading of the MySQL docs won’t work. They say that the DEFAULT value must be a literal, with one exception, for CURRENT_TIMESTAMP which is magic I guess.

[EasyTrieve]

The answer unfortunately is no, DEFAULT can not be computed.

It must be a literal, with one special exception, it can be set to CURRENT_TIMESTAMP, which will default the field to the current timestamp, but that only works on a field that is of type timestamp.

So a field of type DateTime type can not be timestamped. Not this way. However it might be possible to run a script inside a form, so I’ll work on that.