Student <-> Cource relation

[wimvanrooij]

Hi Filemaker Forums!

I’m building a big database for an elementery school. They want a database that can hold all of the grades from all students for all classes.

Now the main relation is pretty simple:

STUDENT: StudentID, Name Student, Group name, Name teacher linked to COURCE: StudentID, Grade1, Grade2.

Both of the StudentID’s are linked togetter.

Now the main problem is dat I get to see all of the students in a table of a random cource. But all of the groups of students have different cources. So in the table “math” I only want the see the children with the “group name” 8a, 8b, 8c. And not everyone.

Is this Possible in FM Pro 10?

Or are there any better ways to built a relation like this?

*I want to have all the children in the same database/table because they shift group every year.

[Søren Dyhr]

Now the main relation is pretty simple:

STUDENT: StudentID, Name Student, Group name, Name teacher linked to COURCE: StudentID, Grade1, Grade2.

No it's wrong it should be StudentID, Grade, CourseID ...and no more for that table. Have a record for each grade! (First Normal Form) in a table called grades, then should you have a Course table, producing a classic many2many structure.

The scripting could be done this way, with the suggested structure (shown above), utilising hops to several relations away - and the match found set feature of GTRR:

Go to Layout [ “Course” (Course) ] 

Enter Find Mode [  ] 

Set Field [ Course::CourseName; "math" ] 

Perform Find [  ] 

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

[ Show only related records; Match found set ] 

Enter Find Mode [  ] 

Set Field [ Student::GroupName; "8a" ] 

New Record/Request 

Set Field [ Student::GroupName; "8b" ] 

New Record/Request 

Set Field [ Student::GroupName; "8c" ] 

Constrain Found Set [  ] 

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

[ Show only related records; Match found set ] 

The continuation from here is a sub summary report, dealing the finds in the join table (Grades)

--sd