• Registered: 2019-07-15
  • Posts: 73

Topic: Put today's date in DateAdded column

I have a field called DateAdded in my Person table.

When I'm adding a new record what's the best way to load todays date into the field so it's there when I save the record?

Also if I want to do the same thing with a DataModified field when the user modifies the record.

I've tried searching but just not quite seeing how to do it.

Alan

  • Registered: 2019-07-15
  • Posts: 73

Re: Put today's date in DateAdded column

This is what I have so far.

I was trying to load today's date to test, but trying to get it to be automatic.

   frmmPerson.dtDateAdded.sqlDate := '2020-12-09'; //strftime('%Y-%M-%D',DATE('now'));
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,685

Re: Put today's date in DateAdded column

Try it like this (see attached), but there are other ways as well.
The 'date added 'takes care of itself - set 'default checked' and 'checked' to be true in the 'date added' properties of the datetimepicker object so that it always uses the current date.
The 'date modified' stays blank if the record has only been created.
The 'date modified' is updated with the current date if the record is changed.
The 'date modified' is not updated if the record is viewed but not changed.
The 'save' button is made visible only when a new record is created or when a change has been made.
Derek.

Post's attachments

Attachment icon created and modified date.zip 338.22 kb, 210 downloads since 2020-12-10 

  • Registered: 2019-07-15
  • Posts: 73

Re: Put today's date in DateAdded column

Derek,
If I don't want to go to the work of adding extra fields to tell if the actual values have changed, and just want to update modified date if they click save, but only if it's not a new record, how could I do this?

procedure frmMedications_Button5_OnAfterClick (Sender: TObject);
begin
        If Action <> 'NewRecord'  then
        Begin
            SQLExecute('Update Medications set ModifiedDate = CurDate() Where ID = ' + frmPerson.tablegrid1.sqlvalue );
        End;
end;

I tried this but either have a typo or something in it.

5 Reply by asawyer13 (edited by asawyer13 2020-12-10 01:58:32)

  • Registered: 2019-07-15
  • Posts: 73

Re: Put today's date in DateAdded column

I tried this also but that didn't work either..

It seems to update the modified date on adds.

begin
        If frmMedications.dbAction <> 'NewRecord'   then
        Begin
            SQLExecute('Update Medications set ModifiedDate = CurDate() Where ID = ' + frmPerson.tablegrid1.sqlvalue );
        End;
end;

Looks like the value of dbAction is 'SaveRecord'
But it's SaveRecord on the add of new and also the save of the modify.

Obviously I'm confused.

6 Reply by brian.zaballa (edited by brian.zaballa 2020-12-10 04:04:42)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Put today's date in DateAdded column

asawyer13 wrote:

Derek,
If I don't want to go to the work of adding extra fields to tell if the actual values have changed, and just want to update modified date if they click save, but only if it's not a new record, how could I do this?

procedure frmMedications_Button5_OnAfterClick (Sender: TObject);
begin
        If Action <> 'NewRecord'  then
        Begin
            SQLExecute('Update Medications set ModifiedDate = CurDate() Where ID = ' + frmPerson.tablegrid1.sqlvalue );
        End;
end;

I tried this but either have a typo or something in it.

I can think of 2 solutions:
1. Have a global boolean variable e.g. IsANewRecord and set its value on the OnShow of frmMedications

IsANewRecord = (Action = 'NewRecord');

2. Set frmMedications.Tag in the OnShow of the form

if Action = 'NewRecord' then
    frmMedications.Tag = 0
else
    frmMedications.Tag = 1;

Then, change 

If Action <> 'NewRecord'  then

to
case 1:

if Not IsANewRecord then

case 2:

if frmMedications.Tag=1 then
brian
  • Registered: 2019-07-15
  • Posts: 73

Re: Put today's date in DateAdded column

If I do this on the OnClick event, I believe I get the right info.

Will test some more

8 Reply by brian.zaballa (edited by brian.zaballa 2020-12-10 04:07:43)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Put today's date in DateAdded column

asawyer13 wrote:

If I do this on the OnClick event, I believe I get the right info.

Will test some more

My Bad. I wasn't on my mind when I suggest that. LMAO, I updated my first reply. Stick to OnAfterClick event for it must be saved first. Anyway, I think derek's solutions is an awesome solution. Having many fields to check will require you to add additional components but I think it is the best solution. Anyway, if you really want to have it on after click even the user didn't change any record, here I modified derek's solution.

Post's attachments

Attachment icon created and modified date v2.zip 496.21 kb, 205 downloads since 2020-12-10 

brian

9 Reply by derek (edited by derek 2020-12-10 13:41:20)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,685

Re: Put today's date in DateAdded column

Hi Brian, Alan,
Another option would be as per the attached (it's a simple modification of something I use to check for duplicate records).
It allows you to test as many fields as you want for any changes to one or any number of them.
If there are no changes and even if 'save' is clicked, the 'modified date' is not altered. 
If there are changes and 'save' is clicked, then the 'modified date' of the record is updated with the current date. 
New records are simply added with a 'creation date' of the current date and the 'modified date' is left blank.
Can't think of any other way (but there probably are).
Derek.

Post's attachments

Attachment icon created and modified date3.zip 339.08 kb, 226 downloads since 2020-12-10 

  • Registered: 2019-07-15
  • Posts: 73

Re: Put today's date in DateAdded column

Derek,
Thanks
Alan