There's also a little bit of a safety issue as well, so that if a field is accidentally erased (such as 'Purchase Date'), I could go to the previous version of that record and get that date, which would be the same no matter when or where the record exists.
I suggest that you examine the structure of your tables and relationships. Data such as "purchase date" should not be recorded in multiple records for the same item. That can make correcting data entry errors a real problem for you.
You can set up an Items table related to a locations table such that moving an item from one location to another creates a new record in the locations table. This table then preserves all past locations for a given item in your inventory. If you define sort order for your relation that sorts by a serial number field in descending order, the most recent location will always be listed first in any poral and any direct reference to the fields in this related table from the context of a given item record will automatically reference the most recent location record for that item.
Let me make sure I follow you. You suggest that I create a new table that would store these "old" locations. This table would just consist of the primary key (or Serial Number) and the location, and probably a timestamp? Any other data could be referenced in from the current record using the primary key, if needed?
What method would you suggest for actually creating this new location? "On Modify" run a script that creates a new record in the location table, and then use "Set Field" to define the several fields I want/need?
That makes sense to me, I just want to be sure I understand. Thanks!
I suggest that you create a new table that stores all locations, past and present.
Otherwise, yes, ;you are correct.
Yes an OnModify trigger on a value list formatted field where you select the location could be used. If using a drop down list, however, I recommend onObectSave instead of OnObjectModify.