8 Replies Latest reply on May 24, 2013 8:34 PM by NeilBarman

    Viewing all unrelated records in a many-many relationship

    wladdy

      Title

      Viewing all unrelated records in a many-many relationship

      Post

      At a seminar, STUDENTS can either attend or not attend any one of the SESSIONS offered. Their ATTENDANCE is recorded in a table linking the other two in a many-to-many relationship. I want to view the sessions with the students who attended them. I use the following relationship structure:

      SESSIONS    1=∞     ATTENDANCE     ∞=1     STUDENTS

      I then create a layout based on SESSIONS with a portal showing related records from ATTENDANCE. The rows of that portal contain fields from STUDENTS. This setup lets me list every session with all the students who attended. My question is: how can I create a similar setup listing every session but this time with all the all the students who did not attend.

      I tried to use the following relationship structure:

      SESSIONS    ∞≠∞     ATTENDANCE     ∞=1     STUDENTS

      but then I get a list of all the students who attended all the other sessions, which is irrelevant. Is there a way to achieve what I need ? I feel that I need to somehow reorganize the data and the tables, but I am not experienced enough to know even what direction to explore - any suggestion would be much appreciated.

      More generally, what are the techniques to get all the unrelated records on the other side of a many-many relationship ?

      Many thanks,

      W.

        • 1. Re: Viewing all unrelated records in a many-many relationship
          philmodjunk

          What an interesting challenge!

          Sessions----<attendance>-----Students

          Try a new occurrence of Students with this relationship

          Sessions----X----<Allstudents------<AttendanceAllStudents

          Sessions::anyField X AllStudents::anyField

          AllStudents::StudentID = AttendanceAllStudents::StudentID

          with this filter expression:

          IsEmpty ( List ( AttendanceAllStudents::SessionID ) ; Sessions::SessionID ) )

          • 2. Re: Viewing all unrelated records in a many-many relationship
            mgores

            Phil helped me with something similar for a scout troop and events.  On my event layout I had a portal of all scouts in the troop and a portal of scouts that attended that particular event.  I set up the name field of the All scouts portal to create the joint table record which caused them to then show up in the Attended portal.  A filter expressing Phil came up with would then remove them from the All scout portal.  This way it can be used to take roll call at the event. Effectively it moved them from one portal to the other leaving me with two lists, Attended and Did Not Attend.

            http://forums.filemaker.com/posts/48201f516c

            • 3. Re: Viewing all unrelated records in a many-many relationship
              wladdy

              Thank you for you replies Phil and Mark.

              Unfortunately, despite experimenting with the x relationship and with the ‘List’ function, I still can’t seem to put your explanations into practice.

              I assume that the layout should be showing records from ‘Sessions’, but I am not sure about the portal. Also, I don’t fully understand the alternate table structure that Phil suggested.

              Thank you very much for the help.

              W.

              • 4. Re: Viewing all unrelated records in a many-many relationship
                philmodjunk

                I don’t fully understand the alternate table structure that Phil suggested.

                Do you know how to make table occurrences in Manage | Database | relationships?

                Click the Students "box" (what we call a table occurrence) and make a new copy by clicking the duplicate button (two green plus signs). This does not make a duplicate of the table, just creates a new box on the relaltionships chart that refers to the same Students table. You can double click this new box to open up a dialog where you can rename it "AllStudents".

                Drag from any field in AllStudents to any field in sessions to create a relationship. This will have the = operator as the default so we need to change it to the cartesian join operator X. Double click the relationship line to open a dialog where you can change the operator from = to X. This operator means that any record in Sessions will match to all records in Students.

                Now make a new occurrence of Attendance and link it to All students by the StudentID fields. YOu'll use this added occurrence with a portal to AllStudents so that student names can be listed in the portal.

                From here, you use a Layout based on Sessions and a Portal to AllStudents to set this up.

                Can you get that far with this set up?

                If so, report back and we'll take the next step. If not, let me know where things get hung up and I'll clarify.

                • 5. Re: Viewing all unrelated records in a many-many relationship
                  wladdy

                  Phil: so far, so good.

                  I have created the two duplicate tables 'AllStudents' and 'AttendanceAllStudents' and I have linked them to the original 'Sessions' table.

                  Sessions >---X---< AllStudents ---=---< AttendanceAllStudents

                  I then created a layout (let's call it 'Absence chart') based on 'Sessions'. In this layout, I put a portal showing related records from 'AllStudents'.

                  As I understand it, 'Absence chart' will let browse through all the sessions while viewing in the portal all the students who did not attend the session on display. This is where I block...

                  Thank you for all your help.

                  • 6. Re: Viewing all unrelated records in a many-many relationship
                    LaRetta_1

                    I hate to interject late but I would approach this a bit differently.  There should only be need for one additional table occurrence of Students and one calculation in Sessions called cAttending (result is text) which would be: 

                    Case ( IsEmpty (  List ( Attendance::StudentID ) ) ; 0 ;  List ( Attendance::StudentID ) )

                    Then join:  Sessions::cAttending  ≠ Students~notAttending::StudentID

                    The same 'not equal' relationship is then also the basis for the dwindling value list.  It refreshes without the dreaded Refresh Window[flush cache].

                    It is just something else to consider which might be simpler overall.  The file can be downloaded from here:

                    http://www.directlinesolutions.com/downloads/Students.zip

                    HTH

                    • 7. Re: Viewing all unrelated records in a many-many relationship
                      LaRetta_1

                      BTW, I would not use filtered portals on this situation because I would suspect there will be a large number of Attendance records AND because dwindling value lists do not operate via filtered portals.

                      • 8. Re: Viewing all unrelated records in a many-many relationship
                        NeilBarman

                             Hi LaRetta,

                              

                             I loved the solution example you demonstrated at (http://www.directlinesolutions.com/downloads/Students.zip) and had a question. Is there a way, once you are showing Attendance and "Not Attending" where you can simply click on the name for anyone on the "Not attending" list, and trigger off a script or something that would move them over into the "Attending" area? Obviously, it would have to build the right related records to represent this relationship correctly...

                              

                             Thanks!