6 Replies Latest reply on Apr 25, 2010 6:11 PM by NancyFJentzsch

    Script won't run as expected

    NancyFJentzsch

      Title

      Script won't run as expected

      Post

      I'm building a database in FM Pro 10. I have 2 tables: Family and Student. I've identified each family 'unit' with a Family ID. I have entered all the students in the Student Table and all the Families in the Family Table. My script correctly copies the student information from the Student table to the Family table for the *1st* child in the family. The script does NOT work for the 2nd or 3rd child. Obviously I'm overlooking something. I hope someone can give me an idea why it doesn't work. I use to be fairly expert at FM 5, but haven't had a job that required me to flex my FM skills in about 10 years. Im scrambling to catch up.

       

      Here's the script:

      Go to Related Record [ From table: “Family”; Using layout: “Family” (Family)

      If [ Family::Child in Family1 = Student::First Name & " " & Student::Last Name ]
           Exit Script [ ]

         Else If [ Family::Child in Family1 = "" ]
            Set Field [ Family::Child in Family1; Student::First Name & " " & Student::Last Name ]
            Set Field [ Family::Child1 Gender; Student::Gender ]
            Set Field [ Family::Child1 DOB; Student::DOB ]
            Set Field [ Family::Child1 Current Sch/Occ; "McClelland" ]
        Else If [ Family::Child in Family2 = "" ]

            Set Field [ Family::Child in Family2; Student::First Name & " " & Student::Last Name ]
            Set Field [ Family::Child2 Gender; Student::Gender ]
            Set Field [ Family::Child2 DOB; Student::DOB ]
            Set Field [ Family::Child2 Current Sch/Occ; "McClelland" ]
        Else If [ Family::Child in Family3 = "" and not Family::Child in Family1 = Student::First Name &" " & Student::Last Name and not Family::Child in Family2 = Student::First Name &" " & Student::Last Name ]
            Set Field [ Family::Child in Family3; Student::First Name & " " & Student::Last Name ]
            Set Field [ Family::Child3 Gender = Student::Gender ]

            Set Field [ Family::Child3 DOB = Student::DOB ]

            Set Field [ Family::Child3 Current Sch/Occ; "McClelland" ]
      End If

       

      It fails at the 2nd Else if.

       

      Thanks in advance for any help.

       

      NJentzsch






        • 1. Re: Script won't run as expected
          LaRetta_1

          Hi Nancy,

           

          "The script does NOT work for the 2nd or 3rd child."

           

          When standing in the Family table, the family record can only *see* the first related Student and cannot see the second or third so your script will always only evaluate the first child.  Are Family and Student joined by FamilyID?  I would think not since you are looking to the Student names.

           

          There shouldn't be need for multiple 'child' fields in Family.  If they are related on FamilyID then a portal should do the trick (and allow for more children).   So you should have a FamilyID in your Student table and relate them that way.   I'm also unsure why you have the Go To Related at the beginning; can you explain where you are starting when you fire this script (indicating layout and table occurrence)?

           

          Can you describe what you are doing and the purpose?   Are you currently trying to 'relate' them?  That might help us offer additional suggestions.  :smileyhappy:

          • 2. Re: Script won't run as expected
            LaRetta_1

            I think you might need three tables:

             

            Family (or actually parents)

            Children

            Classes

             

            Children will have a unique auto-enter serial called ChildID and their DOB, gender etc.

             

            Then Classes would hold the ChildID and contain 'current school', class(?), grade etc.

            Classes would also know the FamilyID through the Children table

            • 3. Re: Script won't run as expected
              NancyFJentzsch

              Hi Retta,

              I have a separate record for each student. When I switch from a particular student record (makes that record active as I understand it), I am doing it with a "Go to Related Record" using the Family ID. I want multiple 'child' fields in Family so that the users at the school can look at a 'Family' record and see all the children in that family. Rather than enter the info twice: once in a Student Record, and then again in the Family Record, the script is designed to fill-in the Student information from the active Student record. I'm coming from the Student layout that uses the Student table to the Family layout that uses the Family table. The 1st If statement is designed to trap the information if the secretary or whoever goes to the Family layout from the Student layout and the Student information has already populated the Child in Family section.

               

              If that doesn't make sense, let me know and I'll try to explain.

               

              Thanks for your input.

              • 4. Re: Script won't run as expected
                NancyFJentzsch

                I don't see why the Student record can't contain the Classes info as this is a small private elementary school. What am I not anticipating??

                • 5. Re: Script won't run as expected
                  LaRetta_1

                  "I want multiple 'child' fields in Family so that the users at the school can look at a 'Family' record and see all the children in that family."

                   

                  Since the Student record has the FamilyID in it,  they should be related as:

                   

                  Families:: FamilyID = Students::FamilyID

                   

                  Then just place a portal from Students directly on your Family layout so that Users can see all the children in that family.

                   

                  "I don't see why the Student record can't contain the Classes info as this is a small private elementary school."

                   

                  And if the student attends next year?  You would have to repeat the Student's DOB, Gender, FamilyID again and then you couldn't really count your Students ... or ... you would have to add fields for the next year. 

                   

                  The purpose of normalizing data is so that you can handle as many possibilities with as little physical effort as possible.  One student can attend many classes?  What if you are asked whether Mary attended last year if you don't keep a record of each year's class (or each class)?  Could you, if you keep the class in the Student (child) record, produce a report of how many classes Mary has attended?   :smileyhappy:

                   

                   

                  • 6. Re: Script won't run as expected
                    NancyFJentzsch

                    I'll give it a try. My only problem is that many families don't have *all* their children at our school and I had hoped to have just one section for the children in the family.

                     

                    I'll study on that! I still don't understand why my Else if statements don't work as expected.

                     

                    Thanks,