Freezing Modification dates during mass updates

[John Chamberlain]

I want to have a modification date remain the same for each record, when I have to do a mass change (e.g. adding a new field). I tried turning off off the auto-enter switch for period of time while I performed the mass change, and that seems to work, but then I have to remember to turn auto-enter back on. This gets to be a hassle when the customer keeps asking for changes and additions.

Does anyone know of a better solution?

TIA

[LaRetta]

Hi John,

Just adding a field to a table does not update the modification timestamp but rather it's when you change or add the data into it and I feel your pain. :^)

Do all your tables have their own unique primary key? They should.

What would work if they do:

• Add the field(s) to your main table.
  
• Create (or use an existing) temp table which holds an ID field (not set to auto-enter). It will hold your main table's primary key.
  
• Find the records you wish to work on and import their primary ID into this temp table.
  
• Create fields in temp table and populate them ... do whatever with these fields.
  
• When ready go to your main table and show all records.
  
• Import using (update matching records in found set, match on the unique ID) but do NOT check perform auto-enter.
  

Cautions:

• If you have specified an auto-enter on the field, it will not run and you will need to re-fire the auto-enter.
  
• This is safer to use when working while served but best to run this when Users aren't in system.
  
• If you plan to include 'add to' during the import remember that other auto-enters will not fire either, including your primary key so you will need to handle that separately.
  

But when working to standardize the data in certain fields it is far easier to do so in a temp developer table. And to keep the mod stamp from changing, it works very well. :^)

ADDED: a temp developer file can also be attached to provide additional views of the data by creating relationships to the main tables. They are very handy.

[John Chamberlain]

Thanks Laretta, your way is a little complicated, but sure looks safer.

[LaRetta]

Hi John,

It just seems complex because I went into so much detail - but it really is very simple; you'll see! I perform all my data corrections in the temp file usually attaching it to main tables so the data that needs to change can be reviewed while I work. Once all records are corrected, it is simple import. By performing all of the work in the temp, you can protect yourself as well from changing real data which might affect other auto-enters in the table (or affect Users viewing the records), so if you make a mistake it is easy to correct in the temp first.

I think I know what I am doing and I usually do but even I can slip up and run Replace Field Contents with an incorrect value, LOL. Better to do data manipulation to the side.

[LaRetta]

There is another option if you really aren't concerned about the safety issue but it requires another field. Create a timestamp called xModificationTS and set it to auto-enter REPLACE with:

Case ( not $$freeze ; ModifyTS ; Self )

When you want to work, set the variable (or use a global instead) with a 1. And use this field for your 'true' modification timestamp. But keep in mind that anything Users do during this period of time will NOT update the timestamp either.

ADDED: Bold red and and remember to set the variable back when done.

[LaRetta]

Another consideration ... You can add an additional protection that it only stops logging modifications when you are in the file AND you have set a variable. In that way, Users won't ever stop it. That feels better to me. But still, working in live file can bite back.

Case ( not $$freeze or Get (AccountPrivilegeSetName ) <> "[full access]" ; ModifyTS ; Self )