Subject Status Updating

[RobV]

I have a database that tracks a Psych. study's follow up interview statuses for the study's subjects and need FileMaker to update a subject's status to "WITHDREW" OR "DECEASED" if the subject drops out of the study or dies. Each subject is assigned an ID number and the way the database is set up is to assign a record for each round of interview follow-ups. So subject XXX99 has related records: XXX99MO06, XXX99MO12, and XXX99MO24.

What I need to happen is if subject XXX99 dies at Month 12 all subsequent follow ups need to show a status of "DECEASED" but not any previous follow ups ie. (Month 6). I don't know if I should run a script (there is already a script running on the status field) or if I should make some change to the status field's calculation so that when it is changed to "WITHDREW" or "DECEASED" it updates any follow up records that exist already or may be added in the future. Any help would be greatly appreciated!

[LiveOak]

How many files do you have and, if more than one, how are the files related? -bd

[RobV]

There is only one file. The person who designed the db setup relationships between fields in the same file. For example, the Subject relationship is "ID=:ID".

Each subject is assigned an ID and then every time a new round of follow ups is due a script is run and a new record is added for each subject. For example, a 2-year follow up is "XXX99MO24". Each record is also assigned an index number according to which position in the subjects series of follow ups it is. For example, a 6-month follow up would be index number 1 and a 12-month follow up would be index number 2, 18 month would be number 3 and so on.