m-to-n relationship via portal

[MaxB]

Hi,

I'm looking for a way to display and manage an m-to-n relationship in a portal.

My goal is to have the tables Students, Courses and Attendance (the m-to-n table), like in the TO diagram.

In a layout based on the Student table I'd like to display a portal with all courses and and a checkbox field to mark attendance, like in the screenshot. I have the feeling, there should be a pretty clean and easy solution to that, but I haven't found a good one yet.

(Currently the portal displays the Attendance table, and to create records you have to select every course from a popup menu. That's not what I want in the interface because it's confusion for the users.)

If I use a cross product relationship to display all the courses, I didn't find an easy way to display or create the Attendance records. 

I could use a list layout based on Courses, create a global field idStudent and build a "create" relationship from Courses to Attendance on idStudent and idCourse, but that wouldn't work in multiple windows. I couldn't compare two students side-by-side.

Same holds for virtual tables, unless I'd build a complicated "window-based globals" setup.

Happy about any suggestion.

Best,
Max

[bcooney]

I would think that the join table you depict represents enrollment, not attendance. Attendance, a child to enrollment, would capture an enrollment Id and a date and/or set an indicator such as AttendanceConfirmedInd. 

[comment]

3 hours ago, bcooney said:

I would think that the join table you depict represents enrollment, not attendance.

Yes, that is a bit confusing. Seems like these are one-time courses one either attends or not?

 

4 hours ago, MaxB said:

In a layout based on the Student table I'd like to display a portal with all courses and and a checkbox field to mark attendance

A portal showing all courses must be a portal to the Courses table - unless you precreate an empty attendance record for all courses for each student (not a good practice, IMHO). And a record in the Courses table cannot "know" the attendance status of a specific student, unless it has their StudentID in a global field or variable.

4 hours ago, MaxB said:

but that wouldn't work in multiple windows. I couldn't compare two students side-by-side.

True, but you could still get the display you want using a different approach - see the attached demo.

CheckBoxJoin.fmp12

Edited August 1, 2021 by comment

[Amanco]

12 hours ago, MaxB said:

Hi,

I'm looking for a way to display and manage an m-to-n relationship in a portal.

My goal is to have the tables Students, Courses and Attendance (the m-to-n table), like in the TO diagram.

In a layout based on the Student table I'd like to display a portal with all courses and and a checkbox field to mark attendance, like in the screenshot. I have the feeling, there should be a pretty clean and easy solution to that, but I haven't found a good one yet.

(Currently the portal displays the Attendance table, and to create records you have to select every course from a popup menu. That's not what I want in the interface because it's confusion for the users.)

If I use a cross product relationship to display all the courses, I didn't find an easy way to display or create the Attendance records. 

I could use a list layout based on Courses, create a global field idStudent and build a "create" relationship from Courses to Attendance on idStudent and idCourse, but that wouldn't work in multiple windows. I couldn't compare two students side-by-side.

Same holds for virtual tables, unless I'd build a complicated "window-based globals" setup.

Happy about any suggestion.

Best,
Max

Maybe you find this udemy course useful

Filemaker Attendance tracking by Guy Stevens

[MaxB]

18 hours ago, bcooney said:

I would think that the join table you depict represents enrollment, not attendance. Attendance, a child to enrollment, would capture an enrollment Id and a date and/or set an indicator such as AttendanceConfirmedInd. 

 

15 hours ago, comment said:

Yes, that is a bit confusing. Seems like these are one-time courses one either attends or not?

Sorry for the confusion. I just used the Courses relationship in my example file for easy explanation. Actually we're assigning access rights between accounts and entities in the database in a relationship like this, but I didn't want to complicate the basic problem. Your "object attributes" example is probably more to the point.

15 hours ago, comment said:

you could still get the display you want using a different approach - see the attached demo.

CheckBoxJoin.fmp12 176 kB · 5 downloads

Thank you so much for the example file! I think I'll go with this "fake checkbox" idea. In my experience, unstored values in lists or portals can become a little sluggish pretty quickly, but the involved tables are not very large in this case.

Thanks again for the quick replies!

[MaxB]

8 hours ago, Amanco said:

Maybe you find this udemy course useful

Filemaker Attendance tracking by Guy Stevens

Thanks for the suggestion and sorry for the confusion. I just used the attendance scenario in my example file, my actual use case felt harder to explain.

[bcooney]

2 hours ago, MaxB said:

we're assigning access rights between accounts and entities in the database

I would discourage this approach if your intention is to mimic FileMaker’s security model. 

[MaxB]

1 hour ago, bcooney said:

I would discourage this approach if your intention is to mimic FileMaker’s security model. 

We're using these ACL style lists to manage record-level access. The FileMaker privilege set checks the record id against a list of ids in a protected global field for access. This global field is filled by a startup script with the allowed ids from this ACL table.

Would you see this as "mimicking"? We need a way to dynamically control who has access to which records.

[bcooney]

No, that sounds like standard practice.