Why dont you just use a 1 line portal to your address table on your parent layout. Sort the portal descending by the modification timestamp.
Won't that will only allow me to view the data in the address portal in order of the most currently modified record to the address table?
What I would like to have is an update to the modification timestamp field in the parent table to reflect when a change (modification, creation, deletion, etc.) occured to a related record. So as you look at the parent layout (for this example, contact names), if an address is modified (or phone number from a phone number table or any other related table) the modifcation timestamp on the parent table will update to reflect this change.
But why would you want to update the modification tiemstamp on the parent record? That is not really accurate. If you put the modification timestamp in the portal as I stated, then it would show you when the last time any of the addresses were last modified.
From an audit trail perspective I would agree that it is not as accurate. However, from the user's stance it can be cumbersome to navigate related records to see when a modification occurred.
We think of John Smith as important, his address is secondary, yes?
If the user is looking at Jon Smith's layout and on it is the modification timestamp for Jon Smith that states "5/5/03 at 12:03:24 PM" and elsewhere on that same layout are portals with a varity of modification timestamps, that become's confusing to the user. We would prefer to look at Jon Smith's record and see that "yesterday" any aspect of Jon Smith was modified.
Let's consider the following
- When I create or modify Jon Smith PARENT_MOD_TIME will auto-enter a timestamp "5/5/03 at 12:03:24 PM." In addition ANY_MOD_TIME should auto-enter the timestamp of "5/5/09 at 12:03:24 PM."
- Today I add Jon Smith's home address. ADDRESS_MOD_TIME will auto-enter a timestamp "2/3/10 at 12:03:24 PM."
Here is where I would want MOD_TIME from the parent table to also read "2/3/10 at 12:03:24 PM."
Thank you for your replies!
correction "not as accurate"
it's not accurate at all!
Yes but from a data integrity standpoint, the modification date should not be altered. Instead use a calculation to display what the last overall modification would be:
Max ( Parent::ModificationDate; Max ( Child::ModificationDate ) )
Thanks, I'm halfway there.
MAX( ) - Yep, that's much nicer. I have a tendencey to forget about MAX and MIN.
This will work when I create a new or modify a related record. If I delete a related record this would cause the MOD_TIME calc field to drop back to the record with the most recent modification time. I would want to capture the time the related record was deleted.
Is there a cleaner way other than to add an additional field to the PARENT table that has a timestamp inserted through a script when a related record is deleted and include it in the MOD_TIME calculation, thus:
Max ( Parent::ModificationDate; Max ( Child::ModificationDate ); PARENT:: DELETEDATE )
You could use a script trigger to update a timestamp field each time that a record in the child table and parent is committed.