Small School DB Relationship Confusion

[lpm]

We need for a teacher to be able to enter the attendance (absent) date and absent reason for all of the students in her different classes. Each teacher has 4 classes and there are only 5 to 8 students in each class.

I have the following 4 tables with the following IDs:

- Students (includes StudentID_kp, TeacherID_kf)

- Teachers (includes TeacherID_kp, StudentID_kf)

- Classes (includes ClassID_kp, TeacherID_kf, StudentID_kf)

- Attendance (includes AttendanceID_kp, ClassID_kf, TeacherID_kf, StudentID_kf)

I managed to put together a process using a listview and some scripting but it failed in a multiuser environment. I think I need to do this with a portal. Ideally the portal would be sorted by class and show all of the classes and all students in each of her classes. For each absent student, she would then click in a calendar pull-down to note the current date and then click in another pull-down to select the reason for the absence.

What is the best way to set up the relationships so that the portal will show all of the students and allow the teacher to enter the date and absent reason? I’m lost at this point and not sure how to connect the relationships. I’ve tried so many different ways and I’m missing something.

Also, is there a good example file somewhere for this type of relationship/portal?

Thank you for any help you can give me. lpm

I'm using FileMaker Adv 9 and Windows XP

[bcooney]

I think that you're missing a table, the join between Students and Classes, "Enrollment", since it's a many-to-many just like Attendance. I swear that a similar Student/Teacher/Class, etc., systems has been mentioned before here on the forums. Try a Search. Maybe someone remembers the thread? Comment?

[lpm]

I had an EnrollmentsTO in another TOG so I copied and renamed it and added it to this TOG.

- Enrollments (includes EnrollmentsID_kp, ClassID_kf, TeacherID_kf, StudentID_kf)

Still not sure:

- How should the chain of TOs be organized in the Rel Graph? (I’m thinking Teachers>Classes>Students>Attendance)

- On what TO should the layout be based? (I’m thinking Teachers)

- On what TO should the portal be based? (I’m thinking either Students or Attendance)

- How should the keys be connected and relationship dialogs be organized?

Still struggling with this. Thanks, lpm

I tried a search and couldn't find a similar education thread about relationships like this. Are there any good example files I could look at?

[bcooney]

I've been so busy lately, I've had every intention to post a demo file for you. I'll try over the weekend (all are invited to help!).

Had a few minutes this morning. Here's a start.

School.fp7.zip

Edited September 18, 2009 by Guest
demo

[comment]

I believe that if you search the forums for "Enrollment" you'll find plenty of material. Limiting the author to "Ender" might be a good idea.

[lpm]

Just logged in. Will look this over and get back to you. Thank you so much for responding! lpm

[lpm]

Hi bcooney,

I’m sorry, but I’m a little confused. I thought I might need only five TOs based on their individual tables (Students, Teachers, Enrollments, Classes, and Attendance). Not sure of the other tables & TOs you include and how they work. Also, I attempted to add records but not sure if I did it correctly since they did not populate the portals like I thought they might.

[bcooney]

You are mistaking the Relationship Graph for an Entity Diagram. It is not.

Part of learning FM is understanding how to use the RG. I use an approach that is called the Anchor-Buoy method. You can read more about it in the FM Dev Standards doc here. FM Help: FM Dev Standards

[lpm]

Thanks, I'm becoming more familiar with the anchor-bouy approach. From your rel graph, it looks like the bottom TOG is the one where attendance is entered. Is there a way to have a portal show only the students for a specific teacher, and, from that portal, enter an absent date for specific students? The result would then appear in the Attendance table by adding a new attendance record for the specific student on the selected absent date. Is this possible with your rel graph, or do I need to make some alterations? I'm still learning...

[bcooney]

"From your rel graph, it looks like the bottom TOG is the one where attendance is entered."

I have Attendance records created in a portal on the Class form Layout.

"Is there a way to have a portal show only the students for a specific teacher"

Students have multiple teachers, don't they? Their attendance is class-specific, not teacher specific.

[lpm]

>> “Students have multiple teachers, don't they? Their attendance is class-specific, not teacher specific.”

I'm sorry, bcooney. I should have explained this better. In this instance, it is necessary for teachers to see a list (portal?) of all students in their classes from which they can indicate that a student is absent on a given date.

This is a school for special needs kids (major emphasis on individual instruction) and, although a teacher can only have up to 7 or 8 students in a class (most classes only have 4), there are instances when a student is in a class with, for example, three other students yet that student is listed in a different designated class. For example, students Joe, Sally, Ben and Mark can be in Ms Jones 1st Period Reading class, but Joe, Sally and Mark are listed as 1st period class= Reading A, while Ben is listed as 1st period class= Reading B.

Therefore, a teacher might have 4 “classes” (i.e., groupings of students in the same period) with 4 different students in each, yet, following the above example, the teacher could have 8 "classes" --

Period 1: 3 students in Reading A, 1 Student in Reading B.

Period 2: 2 Students in Reading A, 2 students in Reading B.

Period 3: 1 Student in Writing A, 3 Students in Writing B.

Period 4: 2 Students in Study Skills A, 2 students in Study Skills B.

This teacher has 8 different “classes”:

Per 1 Reading A

Per 1 Reading B

Per 2 Reading A

Per 2 Reading B

Per 3 Writing A

Per 3 Writing B

Per 4 Study Skills A

Per 4 Study Skills B

A few teachers have classes (groups of students during the same period) with 4 students in each and each of the students are listed in different classes (e.g., John, Reading A; Sally, Reading B; Jimmy, Reading C; and Paul, Reading D). It hasn’t happened yet, but potentially, a teacher could have 16 students in 16 different classes. I know this is confusing to someone on the outside, but this the method used by this school and it works great.

So, I hope you can see why it is important for teachers to be able to pull up a list/portal of all of their own students and mark them as absent in such a way that the absent date and reason is recorded in the Attendance table. The Attendance table is where all attendance reports are generated. Ideally the list/portal row would include: Absent Date, Student Name, Period, ClassName, TeacherName, Absent (checkbox), Absent Reason (pull down).

Sorry for the long explanation. Please don’t give up on me.

[bcooney]

Not giving up, but need some time to review your post. The straightforward approach would be to enter Attendance in the table directly, with value lists on every field. Give me some time.

Of course, other "comments" (lol) are always welcome!

Edited September 24, 2009 by Guest

[lpm]

Tried redesign for entering Attendance directly in the Attendance table, but requires too many steps and is too confusing for users. I hope you have another suggestion for me. Thank you!

[lpm]

Dear bcooney:

I’m sorry to bother you, and I understand if you do not reply because I know you must be very busy. You were helping me with a problem I’m having with a DB I’m working on for a small school.

Topic: Small School DB Relationship Confusion (Topic#211142)

I’m hoping you will not give up on me as I don’t know where to turn next to solve this relationship issue. I have tried and tried to figure it out, but I guess my skill level won’t allow it and I don’t have time or resources to go to FM training sessions or begin my FM education over again. On the other hand, this problem might not have a solution in which case perhaps you can direct me to some resources that might help me? Thank you, lpm

[bcooney]

Please upload a copy of your system, if you can. Keep just a couple of records. I will try to review what you have this weekend.