3 Replies Latest reply on Apr 21, 2014 6:47 AM by philmodjunk

    Many to many display in a portal



      Many to many display in a portal


           I have a classic Many to Many DB issue.  Two primary tables (music and concert) and a join table with the music Id  and the concert Id as well as fields for the sequence in the concert and the soloist do this number in the concert.

           my problem is that when I build a display of the concert (displaying the music in a portal), the extra fields in the join table don't seem to be visible.   In other DB systems, I'd need to build a query as a pseudo table joining the "join table" to the other primary table.  Is thus the right FileMaker solution or am I just off base?

           (totally off list, I do know relational technology, with 30+ years of IT, so this is about how to use your system, not how to design a good relational system.)

        • 1. Re: Many to many display in a portal

               If you have these relationships (but with your field names in place of mine):

               Concerts::__pkConcertID = Join::_fkConcertID
               Music::__pkMusicID = Join::_fkMusicID

               Then, on a layout based on Concerts, a portal to Join can include fields from Music if needed. This is a frequent method used for building the list of Join table records to link the desired Music records to the current Concert. But if you just want to display records from Music, you can also use a portal to Music instead of Join, but then you can't add fields from Join (for sequence and soloists) without an added table occurrence (psuedo table) of the join table on the other side of Music.

               Another option is to pull up a found set of Join table records for a given concert on a a list view layout based on the Join table. Such a layout can include all needed fields from both Music and Concerts. This can be a more flexible reporting layout option in many cases.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Many to many display in a portal

                 Selecting the join table as the portal master works, thanks for that comment.  But, I am getting other entries within the portal that shouldn't be there.  If I can construct a sample below, maybe the problem will be clear, if not tell me what you'd like me to attach that will clarify the issue.



                 concert.  Seq.  join table concert

                 26.           1.          26.  (As expected)

                 26.            2.          24.   (Excuse me?)

                 26.           3.         26.     (Back to correct again!)


                 This problem has been solved.  Rather than just edit the portal parameters, I had to delete the portal, rebuild it, and it now works correctly.


            • 3. Re: Many to many display in a portal

                   For future reference it sounds like you had fields inside the portal row that referred to the wrong table occurrence and thus you didn't see the expected results. Deleting the portal row and starting over would correct that though there were other less drastic methods that would have done the same.