12 Replies Latest reply on Jan 18, 2011 10:22 AM by etripoli

    Multi-join relationship - Fun!

    etripoli

      Title

      Multi-join relationship - Fun!

      Post

      This problem has bugged me for almost a year.  Feels like one of those brain-teaser problems that require an off-the-wall solution.

       

      Take 2 tables, that each have 2 fields that form their primary keys.  But, only one of the fields can be used to relate the two tables.  Now, create a 3rd table, with 3 fields that form the primary key, but only 2 of it's fields can be used to relate to the other two tables.

       

      Now, with a layout based on table 1, and a portal based on table 2, try to auto-create records in the 3rd table through fields placed on the portal.  Problem is, the inability to pass a field from the layout's table through the portal table to create the record in the final table.

       

      Permanent Kudos to whoever can get this to work in FM9.  Attached sample DB should get you started.

       

      Three-link DB

        • 1. Re: Multi-join relationship - Fun!
          mrvodka
             What about setting a global with the member_id in the groups table using a script trigger. Then a records TO could directly relate to groups with allow creation of record on suing the global.
          • 2. Re: Multi-join relationship - Fun!
            etripoli
               Tried this previously, and discovered two problems: 1) I need to be able to see all related info for each record in List view.  2) No script triggers in FM9.  I can make a script, attach it to the value field for the 3rd table, but it doesn't work if the user tabs to the field.
            • 3. Re: Multi-join relationship - Fun!
              comment_1
                

              I don't understand your example. There's a many-to-many relationship between members and groups, without a join table - why? Also, "records" belong to both a member and a group - why is this necessary?

               

              I am not sure what you're trying to achieve, but the structure doesn't seem to be good to begin with. IMHO, a member should belong to a sub-group directly (assuming one subgroup per member) and to a group indirectly. Similarly, a "record" should be related to a member by MemberID alone, and to the member's subgroup and group by implication.

              • 4. Re: Multi-join relationship - Fun!
                etripoli
                  

                Maybe an example will help.

                Let's say you have 5 groups of students.  Each group, in turn, has 5 students.  25 total students. (members table)

                And, you have 10 sets of tests, but the tests are assigned 2 per group, not to the individual student. (groups table)

                Now, when you want to enter each student's grades for the tests they took, you need to have 1 record per student and test, a total of 50 records.  (records)

                How would you do that, easily? 

                A layout based on the groups and students, with a portal showing the tests assigned, and a field to enter the student's grade, seems to make sense.

                 

                Edit:  Let's also assume that we don't know the unique identity of the students, only that they are student A, B, C, etc., within their group.  Since we have a multi-field key, we can only see it as a many-to-many in Filemaker.



                • 5. Re: Multi-join relationship - Fun!
                  comment_1
                    

                  I still don't follow your example that well, but one thing seems to be clear: if a student took more than one test, then before you can enter a student's grade, you need to select the correct test (and the correct student, of course).

                   

                  One way to do this would be to have the TestID in a global field in the Students table - then you can create grade records by entering them directly into a related field placed on a list view of Students (using a special occurrence of Grades dedicated for this purpose).

                  • 6. Re: Multi-join relationship - Fun!
                    etripoli
                      

                    Thanks comment, I think you understand the example.  And while a global field in the student table will work, it will be cumbersome for someone to enter grades for each test, especially since they are all done at the same time, and there's a possibility that some groups will be assigned more than two sets of tests.

                     

                    I've updated the linked FM database, to use the naming in the example.

                     

                    Three Link DB

                    • 7. Re: Multi-join relationship - Fun!
                      comment_1
                         OK, I think I understand better what you're after - it's called a left outer join, and Filemaker doesn't do it. I think the only way you'll be able to present the user with an interface like that is by creating all the grade records in advance.
                      • 8. Re: Multi-join relationship - Fun!
                        etripoli
                          

                        No, not a left outer join, a full outer join.  In SQL, the select would need to look like this:

                         

                        SELECT students.student_id, students.group_id, assigned_tests.test_id FROM student FULL OUTER JOIN assigned_tests ON ( student.group_id = assigned_tests.group_id );

                         

                         

                        Besides, FM does do left and right outer joins, by default.  It doesn't do natural joins (without doing finds), or union joins (without creating a temp table).

                        • 9. Re: Multi-join relationship - Fun!
                          comment_1
                            

                          etripoli wrote:

                          No, not a left outer join, a full outer join.


                          I don't think so (there shouldn't be any grades without a test anyway), but it's not important.

                           

                           


                          etripoli wrote:
                          FM does do left and right outer joins, by default.

                          If it does, I have yet to learn about it.



                          • 10. Re: Multi-join relationship - Fun!
                            etripoli
                              

                            I think we're saying the same thing from different perspectives.  The Grades table is basically a full outer join of Students and Assigned Tests, once the grades are entered.  But that's the half the problem, entering a grade for a student, group, and test before the record has been created.  The other half of the problem is displaying those grades, once entered, from a portal.

                             

                            By definition, isn't a left outer join all records from the table on the left, and only the records that match from the right?  (Right outer join would be the opposite).  That seems like what we get when we view a layout based on the left table, and show fields from the right.  Yes / no?

                             

                            In any case, I have somewhat of a solution.  Through a calculated repeating field in Students, pulling in one test per repetition, and a combined ID field in Grades, I am able to link to the Students table directly to Grades, and setup a second portal based on Grades.  I simply placed the two portals side-by-side (Assigned Tests on the left, Grades on the right).  Scrolling is not possible, of course, but at least data entry and viewing is flawless.

                             

                            I am posting three-link-solved for your perusal.

                            • 11. Re: Multi-join relationship - Fun!
                              comment_1
                                

                              Display is less of a issue, because once you have a grade, it can display which test it belongs to (in a portal to Grades, using a field from Tests).

                              Your solution for data entry can work - though as a user I would be annoyed by not being able to enter the data out of order.

                               

                               

                              ---
                              When I speak of left outer join, I always do so in a context of three tables (as in your case). Perhaps I need to find another term for this issue, seeing that the common usage in SQL refers to two tables only.

                              Filemaker 10 made it quite easy to provide such feature, provided you are looking at a single record at a time in form view. But the ultimate solution would be a relationship where a related field can be used as the matchfield further down the stream.

                              • 12. Re: Multi-join relationship - Fun!
                                etripoli

                                So, I've gone back to this issue many times, and the 'solution' I originally provided just didn't work.  I actually came up with a different solution, involving the Get ( ActiveFieldName ) and Get ( ActiveFieldContents) functions.  It's kludgy, requires hard-coding, and performing the 'Refresh Window' command.  I wish there was a better way, and have submitted a feature request to Filemaker, but if anyone has come across a solution that would work with FM9 (no script triggers), you would make my year!

                                PS: I've updated the Three-Link-Solved database.