Jump to content

Relationship between Student Roll Sheet and Teacher


luiscovar

This topic is 3100 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am using FileMaker 12 and I am trying to create a student-teacher database. Basically I want to create a roll sheet showing every student that the is enrolled to the teacher. The way I have it right now is I have a separate teacher table with a unique ID and their name etc. I have a student table with their unique ID and name etc. In that student table i have two fields (Teacher_1 and Teacher_2), since that student will have only 2 teachers. This field is a drop down list of the ID's on the teacher table.

The way I have it now is I have it the relationship of the unique ID from the teacher table to be related to both teacher_1 and teacher_2 but when I go and create the portal it does not show the related students. It only works if I make the unique teacherID related to either teacher_1 or teacher_2 but not both. How can I make this work?

Link to comment
Share on other sites

The best way to handle this would be to have a third table of Enrollments, with fields for TeacherID and StudentID. This would break a many-to-many relationship into two one-to-many relationships:

Teachers -< Enrollments >-  Students

Filemaker allows you to create a many-to-many relationship even without a join table, by using a multi-key field. Thus, you could use a checkbox field in the Students table to select teachers for the student. But this shortcut has its disadvantages too: you cannot add any attributes to a specific join (e.g. date of enrollment) and producing reports, such as students by teacher, becomes difficult.

Link to comment
Share on other sites

I think I know what you are trying to say. In the enrollment table each new record will consist of the the single teacher they are related to. For example, steve will have on record with teacher "Doe" and another record with teacher "Sagget"?

I currently do have it set up using three table but the reason I have it this way is because I have a student table with all the students in the school but this is a special saturday school program so I have the "Enrolled Student" table for students who are actually enrolled in the Saturday school program. 

So my database would be something like this:?

Teachers -< Enrollments >-  Enrolled Students

How would I make it easy for the user to have to enroll the student without having to make a new record when being assigned a teacher?

Screen Shot 2015-10-02 at 9.18.16 AM.png

Link to comment
Share on other sites

I have the "Enrolled Student" table for students who are actually enrolled in the Saturday school program.

Why is this table necessary? What fields does it have, other than StudentID?

 

How would I make it easy for the user to have to enroll the student without having to make a new record when being assigned a teacher?

It would be better to ask "how would I make it easy for the user to make a new record when assigning a teacher to a student (or a student to a teacher, depending on where you prefer to work from)". There are many possible answers to that. For example, you could show a portal of all teachers and have the user select a teacher from there; a script would than grab the clicked record's TeacherID and the current student's StudentID and create a join record with these two.

Link to comment
Share on other sites

I have the regular student table that consists of around 1300 students and students who will actually be enrolled will be about 350 so what I am doing if they return a form that they do want to attend I simply click the "enroll" button and create a record based on the ID to the enrolled students table. 

 

I see how that would work. It will take a long time to have to click twice for all the students but I suppose there is no other way. 

Link to comment
Share on other sites

Why is this table necessary? What fields does it have, other than StudentID?

 

It would be better to ask "how would I make it easy for the user to make a new record when assigning a teacher to a student (or a student to a teacher, depending on where you prefer to work from)". There are many possible answers to that. For example, you could show a portal of all teachers and have the user select a teacher from there; a script would than grab the clicked record's TeacherID and the current student's StudentID and create a join record with these two.

 

I am trying to create the portal that shows all the teachers from the "Enrolled" table so the user can be in the student record and choose the teacher from there but it is not showing all the teachers. How do I make it so the portal shows all the teachers from the teacher table? 

 

Edit: Nevermind. Figured it out

Edited by luiscovar
Update
Link to comment
Share on other sites

I have the regular student table that consists of around 1300 students and students who will actually be enrolled will be about 350 so what I am doing if they return a form that they do want to attend I simply click the "enroll" button and create a record based on the ID to the enrolled students table. 

 

That doesn't answer my question. Do these students enroll into multiple programs? Or can they enroll twice into the same program? If not, why isn't it sufficient to enroll them by filling a field in the Students table?

 

It will take a long time to have to click twice for all the students but I suppose there is no other way. 

Longer than select two teachers from 2 drop-downs?

 

Nevermind. Figured it out

For those that haven't: a relationship using the X relational operator shows all records in the related table.

Link to comment
Share on other sites

That doesn't answer my question. Do these students enroll into multiple programs? Or can they enroll twice into the same program? If not, why isn't it sufficient to enroll them by filling a field in the Students table?

No, they only enroll into the Saturday school program. I don't know why I didn't think of it doing as you said. If done this way I would have to have had a field such as "EnrolledID" and simply copy their ID onto that field, and based the relationship of that field, right?

Link to comment
Share on other sites

I would have to have had a field such as "EnrolledID" and simply copy their ID onto that field, and based the relationship of that field, right?

Actually, you only need an Enrolled field (Number) with a value of 1 if the student is enrolled, empty (or 0) otherwise. You can use the existing StudentID field as the matchfield for the relationship (I assume you mean the relationship to the join table with Teachers?).

Link to comment
Share on other sites

Actually, you only need an Enrolled field (Number) with a value of 1 if the student is enrolled, empty (or 0) otherwise. You can use the existing StudentID field as the matchfield for the relationship (I assume you mean the relationship to the join table with Teachers?).

I'm not sure I completely understand. So if done this way the enrolled field will simply be a Boolean value. Assuming I create a relationship like you suggested at first:

Teachers -< Enrollments >-  Students

When the user is going to enroll students to a teacher they will do a find in the "enrolled" field and search for a 1? Then do the enrolling using the portal from there?

 

Link to comment
Share on other sites

When the user is going to enroll students to a teacher they will do a find in the "enrolled" field and search for a 1? Then do the enrolling using the portal from there?

I presume so (I am not entirely hip to your workflow). If they want to go over the list of enrolled students and assign teachers to each student in turn, then yes, that would be the procedure. It would not be any different if the field contained the student's ID; you would still have to find the relevant records first. The only difference would be that you would search for any value in the field.

Link to comment
Share on other sites

This topic is 3100 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.