Ask Your Question
0

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

asked 2016-11-02 10:45:47 +0200

EasyTrieve gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2016-11-02 11:53:05 +0200

pierre-yves samyn gravatar image

updated 2016-11-03 11:24:29 +0200

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

edit flag offensive delete link more

Comments

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 gravatar imageEasyTrieve ( 2016-11-03 09:10:56 +0200 )edit
0

answered 2016-11-03 09:17:56 +0200

EasyTrieve gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-02 10:45:47 +0200

Seen: 925 times

Last updated: Nov 03 '16