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?