AnsweredAssumed Answered

Modification Timestamp - Update to Child Reflected in Parent Timestamp

Question asked by lloyddd on Feb 3, 2010
Latest reply on Feb 3, 2010 by mrvodka

Like • Show 0 Likes0
Comment • 8

Title

Post

Howdy Howdy,

I'm thinking this has to be a simple solution, but I can't seem to locate one. What is the cleanest way to have the timestamp on a parent record update when there is an addition/change to a related child?

For example, let's take a contact management database. The parent table has the list of individual names, the related table contains addresses. If I add a related record to the address table I want the timestamp on the parent table (individual names) to reflect this addition.

Thanks in advance,

Dave

Filemaker 10 Pro Advanced

Mac OS 10.6

Windows XP Pro

mrvodka Feb 3, 2010 9:42 AM

Correct Answer

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 ) )

See the reply in context

No one else had this question

Outcomes

mrvodka Feb 3, 2010 8:12 AM

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.

Actions

lloyddd Feb 3, 2010 8:49 AM

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.

Actions

mrvodka Feb 3, 2010 9:14 AM

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.

Actions

lloyddd Feb 3, 2010 9:37 AM

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

PARENT TABLE

- parent_name_first

- parent_name_last

- parent_mod_time

- mod_time

ADDRESS TABLE

- address_city

- address_state

- address_mod_time

- 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!

Actions

lloyddd Feb 3, 2010 9:38 AM

correction "not as accurate"

it's not accurate at all!

Actions

mrvodka Feb 3, 2010 9:42 AM

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 ) )

Actions

lloyddd Feb 3, 2010 10:02 AM

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 )

Actions

mrvodka Feb 3, 2010 12:03 PM

You could use a script trigger to update a timestamp field each time that a record in the child table and parent is committed.

Actions

8 Replies

mrvodka Feb 3, 2010 8:12 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
lloyddd Feb 3, 2010 8:49 AM
Mark Correct
Correct Answer

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.
Like • Show 0 Likes0
Actions
mrvodka Feb 3, 2010 9:14 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
lloyddd Feb 3, 2010 9:37 AM
Mark Correct
Correct Answer

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

PARENT TABLE
- parent_name_first
- parent_name_last
- parent_mod_time
- mod_time

ADDRESS TABLE
- address_city
- address_state
- address_mod_time

- 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!
Like • Show 0 Likes0
Actions
lloyddd Feb 3, 2010 9:38 AM
Mark Correct
Correct Answer

correction "not as accurate"

it's not accurate at all!
Like • Show 0 Likes0
Actions
mrvodka Feb 3, 2010 9:42 AM
Unmark Correct
Correct Answer

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 ) )
Like • Show 0 Likes0
Actions
lloyddd Feb 3, 2010 10:02 AM
Mark Correct
Correct Answer

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 )
Like • Show 0 Likes0
Actions
mrvodka Feb 3, 2010 12:03 PM
Mark Correct
Correct Answer
You could use a script trigger to update a timestamp field each time that a record in the child table and parent is committed.
Like • Show 0 Likes0
Actions

Modification Timestamp - Update to Child Reflected in Parent Timestamp

Title

Post

Outcomes

Related Content

Recommended Content