Auto Populate when a Calc Field change the value of another field

[4Justme2]

I want to auto populate a field (LastModActiveStatus) with the current date everytime a calculation (Active_StatusSum.c) changes value from "0" to "1". If it weren't a calcualtion field I could use a trigger script but because it's a calc field I don't how to accomplish this... is it even doable? See attached zip example.

LastModifiedAutoPopulation.zip

[mr_vodka]

What is the purpose of this. No an unstored calculation does not update a field but the same criteria can be used to set via a script trigger. Another option is to put the current date on the layout and then use conditional formatting to display it, but again I dont know what the purpose is here.

[4Justme2]

When I new record is added, we consider that person to be active. Anytime a person signs up to attend an event (for example) we also consider them to be active. For 18 mos we consider them active--after which, if they no longer attend events we consider them inactive. The calculations I use automatically flag the active field with a check based on either scenario (if a new record is created or if they attend an event or other function that I did not include in this example such as participate in a collaboration, etc).

I am told there are times when the endusers need to know when that active field was last modified. You mentioned the possibility of doing this with a script trigger? I tried using a script trigger earlier but it only seemed to work when I manually entered the field... and in this scenario I'm not entering field because it auto populates. Any ideas?

[comment]

See if this helps:

http://fmforums.com/forum/topic/84019-triggering-modification-timestamp/

[4Justme2]

This would definitely solve my problem if I can somehow incorporate child fields from multiple tables. How would I modify the calculation to include child values from multiple tables rather than a single table?

[mr_vodka]

Max ( Parent::ModificationTimestamp ; Max ( Child1::ModificationTimestamp ); Max ( Child2::ModificationTimestamp ) ) ?

[4Justme2]

Many BIG thanks, John. That'll do it! Talk to you later... mp

[MHAriete64]

This was very helpful for a situation I'm faced with. But, I need to take it one step further. Can I populate my Parent field (e.g., parent::currentStatus) with data from another field in the record associated with the "Max" timestamp.

For example, I have a group of gang members who are seeing case managers. As they progress through the system I want to leave a bread trail of their activity so I have set up a table for their "status" and the dates reached. But I need to know their current status for use in reporting and for use on the user's layout. So, I can get a field to populate with Max (child::timestamp), but I would like the field to instead get their status from the record attached to that timestamp (e.g., child::status).

Can this be done? Thanks.

[Vaughan]

Surely their current status will be the last related Status record? Use the Last() function.

[MHAriete64]

Vaughan, I appreciate your offering. But, I don't see this as a fool-proof solution. If they go back to the table of entries and edit an older entry it becomes the "Last" record. Anyway to embed "Last" or "Max" into a Calculation so that it "Gets" child::status of the "Max" record?

[comment]

If they go back to the table of entries and edit an older entry it becomes the "Last" record.

Not necessarily. Last() gets the data from the last (non-empty) related record, according to the sort order of the relationship. If your records are entered in chronological order and the relationship has no overriding sort order, or if your records have a Date field and the relationship is sorted by this field, ascending, Last() will retrieve the current status.

Otherwise have a look at:

http://www.briandunning.com/cf/908