Define a field in the student family for recording the ID of the selecte Host Family record. It can be formatted as a drop down list of family records or a script can be used to update this field to specify the current host family. (It can depend on your interface design as to which works best here.)
A calculation field in the Host Families field can be:
If ( Student::HostFamilyID ; "Host" ; "Canceled" ) to show which record is the selected "Host Family". You can even format this field as a button to perform a script that changes the status for a given family record by modifying this field in the related Student record.
Note: It would seem very likely that a family might host more than one student or might cancel for one student, then be selected as host for another. This may require defining a join table so you can link a specific student to a specific host record by creating a record in this join table. If so, your "status" calculation field would be defined in this join table instead of the host family table.
If this is what you need, and don't know how to implement such a join table, let me know and I'll respond with more detail and a demo file that illustrates the concept.
Thank you very much for your suggestion! I am afraid I didn't make myself clear. The field Host Family Status in each record has three values: previous, canceled and current and this is in Host Family occurance. I cannot allow Current to repeat. So when I enter a new family for a certain, by definition, it should be "Current" status and meanwhile, I should change the family he had before as "Previous".
The ID of the currently selected Host Family records should still be specified in the Student record.
Having three status values, does complicate this process. Add a second field to student to store the ID of the previous host. Please note that both of these fields need not be present on the layout.
Now your calculation field in Families might be:
case ( Student::HostFamilyID = familyID; "Current" ;
Studnet::PreviousHostID = FamilyID ; "Previous" ;
As I previously stated, if the same host family record might be linked to more than one student record, you need a join table and this calculation should be defined in the join table.
PS. Forgot this detail:
An OnObjectEnter script trigger on the student::HostFamilyID field can perform a script to store the current value into a global field such as $$PrevHostID
An OnObjectSave trigger can perform a script that compares the current value of this field with the variable and if they are different, copies the previous ID into the Student::PreviousHostID field.