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

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.

edit retag close merge delete

Sort by » oldest newest most voted

Hi

Run ToolsSQL , 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

more

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.

( 2016-11-03 09:10:56 +0200 )edit

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.

more