How to Update Date Created and Date Modified Columns

[RussSoftware]

I have 2 datetime fields called DateCreated and DateModified.

How do I update the DateCreated column with the current date/time when I add a new record and the DateModified column when I save a record?

I'm using MySQL.

Thanks

[JimDK]

Re: How to Update Date Created and Date Modified Columns

If it is a grid you could use the two events CanUpdateRecord and CaninsertRecord, and if it is an UX you can insert the value in the aftwerDialogValidate event
xxx.pngyyy.png

[RussSoftware]

Re: How to Update Date Created and Date Modified Columns

If it is a grid you could use the two events CanUpdateRecord and CaninsertRecord, and if it is an UX you can insert the value in the aftwerDialogValidate event
[ATTACH=CONFIG]37468[/ATTACH][ATTACH=CONFIG]37469[/ATTACH]

Thanks!

[RussSoftware]

Re: How to Update Date Created and Date Modified Columns

Thanks!

I'm using a UX Component / Dialog now and not a grid.

The code to update the DateModified works properly. However, I'm not sure how to check the DateCreated in the afterDialogValidate.

I want to check the e.oldDataSubmitted.DateCreated to see if it is blank or Null but I can't find the correct function to do that.

If it is blank/null, I want to update that field with the date(), otherwise do nothing.

e.dataSubmitted.DateModified = date()
if e.oldDataSubmitted.DateCreated = ?????
e.dataSubmitted.DateCreated = date()
end if

Thanks

[JimDK]

Re: How to Update Date Created and Date Modified Columns

could you use the isdate function ??

http://wiki.alphasoftware.com/~alphafiv/ISDATE+Function

[Clipper87]

Re: How to Update Date Created and Date Modified Columns

use a computed column in MySQL =getdate() for the datecreated field , for the date edited field you can use a trigger in MySQL presumed that that is available in MySQL like it is in SQL server

[RussSoftware]

Re: How to Update Date Created and Date Modified Columns

use a computed column in MySQL =getdate() for the datecreated field , for the date edited field you can use a trigger in MySQL presumed that that is available in MySQL like it is in SQL server

I want to make sure it works with any database. I'm just using MySQL now but it could be MS SQL Server, Oracle, etc.

I have tried isblank(), isdate(), isnull(), etc. I'm doing something wrong but not sure what.

[RussSoftware]

Re: How to Update Date Created and Date Modified Columns

I think I have it figured out. Here is what I have:

if isnull(e.OldDataSubmitted.DateCreated)
e.dataSubmitted.DateCreated = date()
end if

Seems to work fine.

[Clipper87]

Re: How to Update Date Created and Date Modified Columns

Yes but then you let Alpha do the job, what I suggested is let your sql server do the job =curdate()

[JBrown]

Re: How to Update Date Created and Date Modified Columns

I use CURRENT_TIMESTAMP as default value for a datetime type field in mysql. This will fire only once when the row is inserted.
You must be running MySQL 5.6.5 or higher to use datetime columns for this purpose otherwise you're relegated to default only one timestamp column per table.

You can then also create an on update default for a second datetime type field that will fire whenever the row is sent an update command.
I know it takes all the fun out, but you can make these fields labels and not worry about user interaction.
Google mysql and current_timestamp together and you'll get it.