1 Reply Latest reply on Nov 10, 2011 8:26 AM by philmodjunk

    Creating a relationship that enables me to auto enter data

    CharlesHueber

      Title

      Creating a relationship that enables me to auto enter data

      Post

      I am attempting to create a database for student discipline that allows me to enter information on a particular incident (incident report) that could possibly list several students involved with one incident. I want to be able to enter the data in one table as student1 student2 and so on and have it atomically populate another table with a list of students. My goal (and I am not sure how to do this) is to be able to run several reports. Looking up a particular student and seeing how many cases he/she has been involved in, looking at each case, and looking at each type of violation and see what students have been involved.

        • 1. Re: Creating a relationship that enables me to auto enter data
          philmodjunk

          You have a many to many relationship here. A student record can be linked to many incident records and an incident record can be linked to many students. This is typically handled in a relational database such as FileMaker by using a third "join" table to link them:

          Students----<Student_Incident>--------Incidents

          Students::StudentID = Student_Incident::StudentID
          Incidents::IncidentID = Student_Incident::IncidentID

          Student name, grade, gender, etc are recorded in the Students table.

          Incident date, location, general description are recorded in the Incidents table.

          Details specific to one student's involvement in that incident would be recorded in Student_Incident.

          A portal to Student_Incident can be used to list students involved on the Incidents layout and a portal to student_incident on the Students layout can be used to list all the incidents with which that student was involved. List type summary reports can be generated, combing info from all three tables on a layout basd on Student_Incident.

          Here's a demo file illustrating the basic set up for such a many to many relationship as well as several more sophisticated interfaces that can be used to work with them. If you think of the "contacts" table in this database as "students" and the "events" table as "incidents" you'll have the same set up you describe in your post:  http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html