One Relation Too Far

[falkaholic]

I have a relation that doesnt seem to work right. I can't see to get my head around what is wrong.

 

 

It for a school - tracking absences. Each class is once a week and has a weekday (courseDay).  Each class has the tipical many-to-many of courses and student enrolled in it (Course_Students).

 

I have made a dashboard that takes the a day of the week to show the students that should be in the school for all classes on that date.

 

The relation is:

Dashboard::date_of_week -> Courses::courseDay -> Course_Students::courseID -> Students::studentID

 

 

When listed in a portal, all the course names are the same and there isnt the correct students. I have tried re-arranging the relations but still the same.

 

Feels like it something really simple but am I doing something like this correctly?

 

Thanks for any pointers.

[Rick Whitelaw]

You must have a record in your join table to relate the data I your left and right tables. Hope this helps.

[Kris M]

-dashboard

day of week selector

 

-courses table

pk course id

course day

 

-course_student table

pk course student id

fk course id

fk student id

 

-students table

pk student id

 

can you have more that one course per day of the week? If yes then another join table is probably needed because course and day are many to many.

If i failed to understand then please post the file

[falkaholic]

You're correct with you're takes. Here's a shot of the relation (have tried other ways).

 

There can be more than one course per day but I dont see how another join table could fit in isn't relating to a day of the week to courses::day act as one ?

 

Thanks. ,

[comment]

When listed in a portal, all the course names are the same...

Assuming your portal shows records from the CourseStudents join table, you will need another TO of Courses that sits behind the join table (from the point-of-view of Dashboard) to show the relevant course name in each portal row. Either that or have an unstored calculation field in CourseStudents to pull the course name and place that field in the portal. Otherwise all rows of the portal will show the first related course (again, from the point-of-view of Dashboard).

 

...and there isnt the correct students.

I don't think we have enough information to help you with that. Note, however, that if the portal indeed shows records from the join table and a student takes two courses on the same day of week, that student will be listed twice. If, OTOH, the portal points to the Students table, it makes no sense to have it show a course name field.

[falkaholic]

Thanks! That helped.

 

Got it working, rebuild the whole chain and its works now.