6 Replies Latest reply on Oct 12, 2010 12:17 PM by d.b

    Create related records

    d.b

      Title

      Create related records

      Post

      Okay, I'm totally new to FM (and relational databases in general). This isn't as simple as I thought it might be.  I'm still getting comfortable with how to set up relationships correctly...

      Anyway, I'm setting up a gradebook.  I have tables:

      StudentInfo
      Enrollment_StudentsAndClasses
      Classes
      Assignments
      Scores

      I'd like to be able to create a class set of records in the scores table after I create an assignment record.  The assignment table has info on which classes are related to the assignment, so I want it to in turn look to the enrollment_StudentsAndClasses table to identify which StudentID 's are enrolled in that class.  Then I want a record created in the scores table for each studentID.

      I assume I need a script to do this unless there is a better set-up of tables or some FM tool that I don't understand.

      Thanks for any help.

      FMRelationships.jpg

        • 1. Re: Create related records
          philmodjunk

          Yep, a script is required. You might also want to add a second table occurrence of Scores to your graph and link it by student ID to Enrollment_Students so you can see total scores etc. for a given student.

          Here's your script:

          From your assignments layout where you've just created a new assignment record, click a button with this script:

          IF [Not IsEmpty ( Enrollment_Student::Student ID ) ]
                 Freeze Window
                 Set Variable [$ClassID ; Value: Assignments::ClassID]
                 Set Variable [$AssignmentID ; Value: Assignments::AssignmentID ]
                 Go To Related Record [Show only related Records ; From Table: Enrollment_Student; using Layout "Enrollment_Student" (Enrollment_Student)]
                 Loop
                         Set variable [$studentID ; value: Enrollment_Student::Student ID ]
                         Go To Layout [Scores(scores)]
                         new Record/Request
                         Set Field [Scores::StudentID ; $StudentID ]
                         Set Field [ Scores::ClassID ; $ClassID ]
                         Set Field [Scores::AssignmentID ; $AssignmentID ]
                         Go To layout [Enrollment_Student]
                         Go To Record/Request/Page [Next ; Exit after last ]
                 End Loop
                 Go To Layout [Original Layout]
          End IF

          This creates a new scores record labeled with appropriate class, student and assignment IDs for every student in the join table for the assignment record's class.

          • 2. Re: Create related records
            d.b

            Thanks PhilModJunk, that worked perfectly!!! Amazing. You were fast in posting your answer too.  I can't thank you enough.

            I noticed that adding a relationship that requires recurrence tables is only one-way.That could get messy as I'll need recurrence to show last names on the score entry screen; then I'll need it the other direction to report individual scores to students.

            Now that I can create those records, I want to make sure that those records get deleted if I delete the assignment.  Can that be done by a script trigger upon deletion of a record?  If not, can I disable the "delete record" option so that it can only be deleted by a script?  Does this script do the job correctly?

            Go to Related Record [Show only related records; Match found set; From table: "Scores"; Using layout: "Scores" (Scores)]
            Delete All Records []
            Go To Layout [Original Layout]
            Delete Record/Request []

            • 3. Re: Create related records
              philmodjunk

              Go To Related Record in this script can be extremely dangerous to your data. If you don't first confirm that there really are related records to delete, You Delete all records could delete all the records from a completely different table and this is a catastrophy you can do without!

              In this case, I don't quite follow by whaty you mean by "a relationship that requires recurrence tables is only one way". I'm not sure what you mean by a "recurrence table". Relationships in FileMaker are two way unless the field in the parent record is unstored.

              Now that I can create those records, I want to make sure that those records get deleted if I delete the assignment.

              In manage | Database | Relationships, you can double click the relationship line linking the two tables to bring up a dialog for selecting more options for the relationship. One option you can select is "Delete Records in this table when a related record is deleted in the other table". Select this option for the scores table and deleting an assignment record will automatically delete the related scores records. Make sure you do not select this option for Assignments or deleting a scores records will automatically delete the matching assignment records and this could be a very undesirable result.

              • 4. Re: Create related records
                d.b

                When you told me,
                "You might also want to add a second table occurrence of Scores to your graph and link it by student ID to Enrollment_Students so you can see total scores etc. for a given student."
                Initially I created the relationship so that a second occurrence of Enrollment_Students was created... the related records did not come up.  I deleted that relationship and the second occurrence of Enrollment_Students and created the relationship to a second occurrence of Scores and then the related records were identified successfully.  I assumed that meant that the relationship was only working in one direction.  Is this normal?  Should I try to recreate this with more explanation?

                Thanks for the info on deleting related records.  That's a lot easier than creating a script.  I'll implement that.

                 

                 

                • 5. Re: Create related records
                  philmodjunk

                  I suggested adding a second occurrence of Scores--not Enrollment_Students. The other approach can work, but then your layout has to be based on the new occurrence of Enrollment_Students instead of the original.

                  Each Show Records From box in Layout setup... and Portal Setup..., the table drop down in a specify field dialog all refer to table-occurrences by the name given them in the relationship graph. FileMaker needs this info to determine what part of your relationship graph will control what related data may be accessed.

                  This detail isn't always obvious as FileMaker automatically creates a table occurrence with exactly the same name as its data source table ( the names listed in the field tab of Manage | Database ).

                  • 6. Re: Create related records
                    d.b

                    Thanks.  You've been REALLY helpful!