Method to track Record update?

[Beltronics]

I include a record date in my tables which is a default entry of date() so I can always tell when a record was added. I also like to have a field which tells me when a record was changed in any way. Can someone tell me the best method to do this? I know of the following options but wonder if any of them will get me into trouble. Let's call the field "Record_Update"
1. In Field Rules I can define an ON SAVE event as follows:
tbl=table.current(1)
tbl.change_begin()
tbl.record_update=date()
tbl.change_end(.t.)

2. I can make Record_Update a calculated field = date()

3. I can define a field rule, Data entry = always required, Simple default expression = date(), check the box for "At end of enter, set the default value if field not touched by user."

I am presently using both option 1 and 3 but think I might prefer only option number 2. Will that recalculate every time I put the record in edit mode?

[Tom Cone Jr]

Re: Method to track Record update?

I use two fields.

In the first I want to store the date the record was ENTERed. I use a default value field rule expression = date(), enter at start of data entry AND I set the data entry skip field rule to TRUE, thereby preventing the user from ever being able to edit the field value. The field value is set to the current date on initial entry. Never changes.

In the second I want to store the date the record was LAST CHANGEd. I use a calc field rule expression = date(). The field value is set to the current date each time any edit to the record is saved.

[Beltronics]

Re: Method to track Record update?

Thanks again Tom. I will use your method of setting a SKIP condition so the date can't be changed. I had not thought of that and I appreciate the advice. I am not sure where I got the RECORD ONSAVE method but had used it in a couple tables. I am now going to use the calculated field and the default as you do. It means adding fields to all my tables but will most certainly be worth it in the future.

[CALocklin]

Re: Method to track Record update?

Tom et al,

I highly recommend that you DO NOT make your change date a calc field!!!

If you ever happen to run a global update to, for example, set a new Logical field to a default value of True (.T.), the change date on EVERY record will be set to 'today'. This is seldom the desired result. There are other situations that can also cause records to be updated when it isn't desired. Of course, if you are aware of this possibility there are ways to work around it - but if you ever forget.....

My preferred method is to use the CanSave event with this code:

tc = table.current()
tc.change_dtf = date()

'used only if a *script* creates the record but doesn't include the creation date: (i.e., in case I mess up)
'it may not be the correct create_dt but at least it won't remain blank forever.
IF tc.create_dtf = {}
    tc.create_dtf = date()
END IF

DO NOT use the OnSave event as suggested above. When the CanSave event fires, the table is still in edit mode and it is only necessary to assign the new value to the field. (Note that there is no change_begin/end() in my script.) However, if you do this in the OnSave event then you must put the table back into edit mode, make the change, and save the record again. Think about that - it's like telling your car engine to stop - then restart and stop again before it has fully stopped. Sounds like a good way to make an engine backfire or damage a starter and I suspect it might do something similar in your program.

FWIW: I've said this before but it's worth stating again. I also highly recommend putting a Create_dtf and Change_dtf field on EVERY table. Even on lookup tables.

Just this week I've run into two situations where someone wanted to do something that required having one of those dates - long after the table was created and long after data was added. Luckily these tables were created by me and all tables created by me in the last few years have these fields. (Yes, it's very unusual to get two in one week. That's probably closer to the average for a typical year.) I add these fields so often that I even built automated routines for adding both them and the field rules.

Sometimes it's as simple as "If I knew when these lookups were added I'd have a better idea which ones could be deleted." Other times it involves a new feature that would be virtually useless if old records didn't have dates on them.

Even if these fields are never needed for the application, they can be very useful for CYA. I've run into situations where someone complained about problems with my app and said something like, "That record wasn't there last week", and I was able to tell them what day it was actually created. After a bit of thought they realized I was right and that the issue was really theirs. Something like, "Oh, that's right. I was in here last Saturday doing some updates."

[Tom Cone Jr]

Re: Method to track Record update?

Cal, I see your point. Do you mean to use the CanSave event in the form, or the CanSaveRecord event in field rules?

[MikeC]

Re: Method to track Record update?

Hi,
A related question about yet another way to accomplish the task.

I tend to use the OnChange event of a field that has to have a value placed for a record to be created (not via field rules but from form events). If the field is initially blank the script assigns date() to the Creation Date field and the Modified Date field. If a value exists, then date() overwrites the Modified Date field.

I also, at times, then create an entry to another table that contains the new value for the field and the dates for a history or audit trail .

Is there anything inherently flawed in this methodology or forseeable problems that could manifest as a result of doing it this way??

[Beltronics]

Re: Method to track Record update?

Cal
Thanks for the input and especially the insight from experience. It brought up a question. When you create an operation that posts new records into a table, that usually bypasses Field Rules. So, how do you create the two date fields then? Or, did you cover that in your method and I missed it?

Also, I assume your code tc.change_dtf indicates that your field in that table is named change_dtf and most likely stands for change datefield and I would modify mine accordingly or change my field names to create_dtf and change_dtf?

Edit:
I see, in your code, where you set the date if the record is created by a script (operation) and I missed it.

[Beltronics]

Re: Method to track Record update?

I use two fields.

In the first I want to store the date the record was ENTERed. I use a default value field rule expression = date(), enter at start of data entry AND I set the data entry skip field rule to TRUE, thereby preventing the user from ever being able to edit the field value. The field value is set to the current date on initial entry. Never changes.

In the second I want to store the date the record was LAST CHANGEd. I use a calc field rule expression = date(). The field value is set to the current date each time any edit to the record is saved.

Tom, I tried Cal's code (which seems to work fine) and I set the data entry SKIP field rule to .T. but I can still change the create date when I access the record in the default browse of the table.

[CALocklin]

Re: Method to track Record update?

Cal, I see your point. Do you mean to use the CanSave event in the form, or the CanSaveRecord event in field rules?

The CanSave RECORD event. I even verified with Lenny that using table.current() in the CanSave Record event will always refer to the table that is saving the record - no concern even if it's in a set.

Besides, this eliminates the need to worry about adding a script to each form.

[CALocklin]

Re: Method to track Record update?

Hi,
A related question about yet another way to accomplish the task.

I tend to use the OnChange event of a field that has to have a value placed for a record to be created (not via field rules but from form events). If the field is initially blank the script assigns date() to the Creation Date field and the Modified Date field. If a value exists, then date() overwrites the Modified Date field.

I also, at times, then create an entry to another table that contains the new value for the field and the dates for a history or audit trail .

Is there anything inherently flawed in this methodology or forseeable problems that could manifest as a result of doing it this way??

It sounds like you're a little bit concerned about the idea of changing field "B" from the OnChange event of field "A". This would be my concern as well. It sounds little bit like changing two fields at the same time. However, from what you say it sounds like it's working so I'd have to guess it's OK.

My only other thoughts are:
1. Just keep this in mind if strange things start happening with these fields.
2. Could it be done just as easily in the OnWrote field rule?

[MikeC]

Re: Method to track Record update?

Hi Cal,

1. Just keep this in mind if strange things start happening with these fields.

Definitely will!

2. Could it be done just as easily in the OnWrote field rule?

I hadn't thought of using that...but don't think it would work for most of my scenarios as I only want it to "fire" if a particular field value changes and not if any other value changes.

Thanks for your thoughts on this though.

----Time for zzzzzzzz now as head is getting a bit foggy. Seems as if you're a bit of a nightowl too, especially considering you're an hour ahead of me.

[CALocklin]

Re: Method to track Record update?

When you create an operation that posts new records into a table, that usually bypasses Field Rules. So, how do you create the two date fields then? Or, did you cover that in your method and I missed it?

Well, first off, you shouldn't have to enter a Create date when posting because posting doesn't create new records.:) However, if you are appending records then I know it's possible to add "fixed" values (as in "today's date" rather than a field value from the other table) as part of the append. I don't recall the specific technique right now but I know it can be done - at least in xbasic. I'm not sure if the genie can do it but I suspect it can.

Also, I assume your code tc.change_dtf indicates that your field in that table is named change_dtf and most likely stands for change datefield and I would modify mine accordingly or change my field names to create_dtf and change_dtf?

Yep.

Note that I would personally never use "Change" or "Last_Change" or "Create" or "Create_Date" as the field names. I very seldom use "one word" names for anything because they are seldom unique. I use these particular names, Create_dtf and Change_dtf, because they are under 10 characters (i.e., bullet proof), contain two "words" to make them more unique, and have an "f" on the end so I can create a similar but unique name for an index by changing the "f" to an "_" or create a variable based on this field by changing the "f" to a "v". This makes my code more searchable without wasting time sifting through invalid hits. At the same time, it makes it a bit easier to read the code (at least in my opinion) because I immediately now that there is a relationship between Create_dtf, Create_dt_, and Create_dtv. And, I know exactly what that relationship is. If I see a variable named Create_dtv, I know it came from a field named Create_dtf. (In most cases, if a variable is just a variable and not based on or spelled like any field, I don't put a "v" on the end. It isn't necessary because it can't be confused with anything else.) If I see Lookupd("F",Input_Date,"Create_dtf","MyTable","Create_dt_") I immediately know that the Input_Date is probably not based on another Create_dtf field (although it might be if it's inside a function), I'm returning the Create_dtf field, and using the Create_dt_ index. This may seem like a rather strange thing to do but I might be checking to see if any records were actually created on that date. If the returned value is a blank date then there are no records with that creation date.

I see, in your code, where you set the date if the record is created by a script (operation) and I missed it.

I'm not sure you are reading that correctly. An xbasic script (and probably most operations??) will not activate either the CanSave Record event or the default field value for the creation date. This means that both of these dates must be specified in any xbasic routine that creates a record and the change date must be specified when editing a record. But that's just how the field rules work and would generally be true regardless of how you specify the dates.

The code you are referring to might have been better to leave out for this example. It is only there as a semi-CYA routine. If I create a record with an xbasic script and forget to add the creation date, this routine will add a "creation date" the first time a USER edits the table from a form or browse. Be careful of what this means. If the record never gets edited by a user, the creation date will never be added. Or, it might not be added until years after it was actually created. All this does is IF the record gets changed and the creation date is blank, it will add the creation date so that the record will finally appear in certain filters. Up to that point, it wouldn't show up at all in a filter that was based on a date range - unless, of course, the date range starts with a 'blank' date.

Using this method to "add" a creation date has to be done with some care. In some cases it could cause a real reporting problem to add a creation date later on. In other cases it may be prudent to use some other logic to determine that date. Perhaps use another date in the table or the lesser of two dates. Or, check the date on the previous/next record and use that date. Or, perhaps just add a warning and allow the user to enter a date. Each situation may be different. Better yet, don't forget it in the first place and there won't be a need for CYA actions.

FWIW, the only reason that code was shown here is that I was lazy and used my keyboard shortcut to type that script section. I just pressed "jjchgdt" and the macro program replaced it with all 8 lines of code. (Let's see, do I want to type 7 characters or 171 characters? I'll have to think about that one for a couple milliseconds.)

[Beltronics]

Re: Method to track Record update?

I feel like I'm in a class and I'm lovin it! In my particular case, I was thinking of one process I use. When parts (in inventory) are marked to be ordered and the user pushes the "Post Orders" button, I write (append) new records to an "orders" table from an action script. It appends the p/n, qty etc into the "Orders" table. That's where I'm creating new records and need to also append the create_dtf and the change_dtf. I know what you mean about forgetting to do that and then, later, having a date entered which is way after the actual date but, shame on me if I forget that in the script. A bigger problem is the thousands of records now in the tables that never had those two fields and I am now adding them. I guess we will have a starting point and realize that not all these records were created on 22 June 2008. I will have to have a disclaimer that, if the create or change date is 22 June 2008 (for example) then the actual date is earlier and this info is not accurate. I have to enter "something" in all those records that will now have two new added fields. When I upgrade the business from A5V8 to A5V9, they will have a lot more changes than that to deal with!! I'm trying to get this one "right" before I install it and convert everything. I know it will require me to empty all A5V9 tables and append all A5V8 tables they are now using into the restructured V9 tables. I might find some dates in the V8 tables I can use????

[Keith Hubert]

Re: Method to track Record update?

Hi Cal,

Thanks for that bit of code, its amazing what you can find just by reading through this forum. I always say, you find the things you've been looking for, when you are not looking for them.

Just as a matter of interest, I put this in the CanSave event of the Form and it works just fine.

[georgefurnel]

Re: Method to track Record update?

Cal,

I use the CanSaveRecord event to add "Addin.run("audit_trail").

Which should be first, the audit command or the code to register the change date etc?

Kind regards

George