Storing old value when field is modified...

[Keith O'Hara]

Hello,

 

First post, sorry if I've posted in the wrong area, or if this information can be found elsewhere. Here's what I'm trying to do:

 

I have a table into which I import a status report from a vendor… one of the fields imported is an "estimated completion date". Each week, I'll have a new status report (excel file that I import) from the vendor, with new/updated estimated completion dates for each record (along with other information), that I import using "update matching fields". What I'd like to do is create a separate field that displays the PREVIOUS estimated completion date once the NEW estimated completion date has been imported/changed.

 

I originally created a script that would just "set field", with the "current estimated completion date" being copied to the "previous estimated completion date" field, and then import the excel. The problem with that script is that it would perform this copy for ALL records, even the ones where the date doesn't end up being altered by the new excel import, thus wiping out the old "previous estimated completion date".

 

I'm assuming I need some sort of script trigger that can be used to, before the new value has been saved, copy the old value into my "previous estimated completion date" field, and then go on to save the new value in the "current estimated completion date" field, and only do this on dates that are actually changing. Any thoughts on which would be the right one to use or how that script might look?

 

Thanks,

[comment]

I suspect the best way would be to import the completion date into a holder field. Then constrain the found set to records where the holder field  ≠ completion date, and do the swap on these.

[Keith O'Hara]

Worked, thanks.

[OlgerDiekstra]

You could also use Nightwing's superlog to maintain a history of changes to the record. That way you can track who made the changes (if the import is manual), and add additional fields to track. Superlog uses a single textfield to store all it's info in, which may or may not be suitable.