4 Replies Latest reply on Aug 16, 2011 3:13 PM by philmodjunk

    How do I validate a field by keeing a certain value from repeating in a found set?

    hgscn

      Title

      How do I validate a field by keeing a certain value from repeating in a found set?

      Post

      Hello friends,

      I need your help:

      I have a student table (one) related to a host families table(many). There is a Host Family Status field in the Host Families table, with "Current" and "Canceled" as its vaules. For each student, there can be several Canceled host families, but there should always be ONLY ONE "Current" host family. SO when I enter a new "Current" host family for a certain student, I want the database to remind me I have a current host family already and so I should mark that as Canceled first.

       

      How could I do this?

       

      I would appreciate it!

        • 1. Re: How do I validate a field by keeing a certain value from repeating in a found set?
          philmodjunk

          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.

          • 2. Re: How do I validate a field by keeing a certain value from repeating in a found set?
            hgscn

            Hello Phil,

            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".

            Any suggestion?

            Thank you!

            • 3. Re: How do I validate a field by keeing a certain value from repeating in a found set?
              philmodjunk

              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" ;
                        "Canceled" )

              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.

              • 4. Re: How do I validate a field by keeing a certain value from repeating in a found set?
                philmodjunk

                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.