Triggering Modification Timestamp?

[Wickerman]

I have a Layout with a Portal on it. When a new record is added to the portal, I would like the Record Modification timestamp for the main Layout's table to reflect that this parent record has been modified -- but technically it hasn't been, since no data in this table has actually been changed.

I figure the way to do this is some sort of Script Trigger "ON OBJECT MODIFY" being applied to all the fields in the portal. Yes?

But I'm not sure what action would be most appropriate for that.

Suggestions?

[comment]

What is your purpose here? You could easily have an unstored calculation returning the maximum of modification time of the parent and all of its children - without forcing any fake modifications upon the parent.

[Wickerman]

Well, the purpose is just that the client would consider the addition of a child record as a substantive "modification" of the parent.

The Parent is a Contacts table, the child is a "mailings" table. The recording of a mailing is something they would like to reflect on the status of the Contact record. So, for instance, if 15 clients are sent samples and that fact is reflected in the use of the portal, they'd like to see that those 15 client records were 'modified'.

So, you;re suggesting that the Modification field on the Parent could actually be a calculation based on a standard timestamp field in the parent table and the child time tamp, right? That might work okay. . . it seems more economical than having triggers fire ever time they make a move in the portal...

[Vaughan]

If you want to track mailings then track the date and time the mailings were sent. Don;t confuse this with record modification.

Somebody does a replace on a field and updates a field every record. How accurate is the mailing date now?

[Wickerman]

Thanks for the perspectives. I think this is a case where I just tell the client "you don't really want to do that" -- which I have to remember is something I can do from time to time!

[Vaughan]

Give the client what they need, not what they want.

[LaRetta]

As Michael suggests, a simple calculation in your parent table (result is timestamp) with:

Max (Parent::ModificationTimestamp ; Child::ModificationTimestamp )

it seems more economical than having triggers fire ever time they make a move in the portal...

Absolutely.

[LaRetta]

Somebody does a replace on a field and updates a field every record. How accurate is the mailing date now?

... the client would consider the addition of a child record as a substantive "modification" of the parent.

Hi Vaughan!!

You mention the potential problems in using a record's modification timestamp in this way. I agree that, if solution is designed to allow modification of a field which shouldn't have been modified, that what you say could be an issue.

Since the client only cares about when a new child record is added, you can use the child's creation timestamp instead (since the mailing record IS the child) or even the mailing date itself.

Thank you for adding depth to the considerations.

Edited May 8, 2012 by LaRetta

[comment]

As Michael suggests, a simple calculation in your parent table (result is timestamp) with:

Max (Parent::ModificationTimestamp ; Child::ModificationTimestamp )

Ahm... actually, it needs to be =

Max ( Parent::ModificationTimestamp ; Max ( Child::ModificationTimestamp ) )

[LaRetta]

Wonderful!! Hi there, Michael!

I am not sure I agree/understand. If from Parent, we issue Max ( Child::ModificationTimestamp ) this gives the maximum of ALL children (for that parent), wouldn't it? And just as we can use Max ( field1 ; field 2 ; field3 ) ... it doesn't matter where those values come from - they are compared.

Including the parent timestamp within the same Max() calc should just include it in the comparison, shouldn't it? I suppose I could test it but I would rather just ask today and I hope that is alright.

[LaRetta]

Never mind - I will test it. I didn't mean to be lazy.

UPDATE:

I rarely produce an answer without testing and I did not test this time so you shook me good. But I just tested and the calculation works as I would have expected.

It is therefore Highly likely that I am missing something in understanding the needs of this post.

Edited May 11, 2012 by LaRetta

[comment]

The Max() function, like all aggregate functions, has several different syntaxes. The one you used - Max ( value1 ; value2 ; ... valueN ) - returns the highest among the listed values. The problem is that the values listed are the parent value and the first child value.

[comment]

Here's a test file that (hopefully) shows the difference clearly.

OverallMax.fp7.zip

[LaRetta]

Hi Michael,

I realized what you were saying about the first child record but I even tested it last night and it worked liked I would expect! Honestly it did! Okay, it was late, but it really did produce the largest child value (third child and larger than parent value) and now I created a new test file again (thinking maybe it only worked correctly when file is first created) but no, it didn't work the second time. Thank you for the file. Lesson: never use test results from a single test (this has caught me before).

If Max() will produce the TRUE largest child value if only a child field is within it (and it does) but then only see the FIRST child if you add a parent field within it then it still feels inconsistent and illogical. I now see in FM Help the words which supposedly (FM thinks) makes it clear (in red). Here is FM Help on Max():

1. a field in matching related records specified by (table::field), whether or not these records appear in a portal.
   
2. several non-repeating fields in a record (field1;field2;field3...).
   
3. corresponding repetitions of repeating fields in a record (repeatingField1;repeatingField2;repeatingField3), if the result is returned in a repeating field with at least the same number of repeats.
   
4. several fields in the first matching record specified by (table::field1;table::field2;...). You can include fields from different tables (table 1::field A;table 2::field B...).
   

So these are four different syntaxes and they cannot mix and Max() changes its behavior according to the one used?

Luckily I have never had an instance where I've used Max() in a mixed configuration such as this. I am SOOOOOOO glad this happened and you spoke up because it could have bitten Wickerman or someone else reading this thread. There is already an abundance of incorrect information floating around without my adding to it.

Thank you for the correction! :laugh:

[hbrendel]

Maybe you had the relation descendingly sorted on the field, so the first value is the highest.

[LaRetta]

Ah, Henk, thanks for trying to make me feel better but no, it was brand new file and I hadn't sorted or anything yet. I created the file, fields, then added records, realized I hadn't created the relationship, created it then viewed the results and ran with it because the result was 6 (the third related child) and the parent had 3. I've tried re-creating my exact steps to see if I can replicate but nada because I don't remember my exact steps. That is probably another ingredient to testing ... write down the steps and sequence ...

Because I cannot replicate, I'm approx. 89% 97% sure it was simply my mind misbehaving. :idot:

Edited May 12, 2012 by LaRetta

[DeathRobot]

This is extremely helpful. Thanks!