2 Replies Latest reply on Jan 17, 2012 5:36 AM by RayCologon

    Portal View of a Person-to-Person Join Table While Filtering Out the Parent Record


      I've got a table called "Beings" that includes both people and organizations, each with a unique sequence number called "BeSeq".


      I've got another table called "BBLinx" that contains 4 fields of significance:

      • BBLSeq, the unique sequence number for each "BBLinx" record

      • BeSeq A, the code that matches Being A in "Beings"

      • BeSeq B, the code that matches Being B in "Beings"

      • relationship


      Thus, to show that Lynn Lewis (BeSeq 1234) is the choir director at 1st Baptist Church (BeSeq 1235), there would be a record in "BBLinx" (let's say BBLSeq 101) containing the values 1234, 1235, and "choir director", respectively. Similarly, to show that Lynn is married to Kim Smith (BeSeq 1236), I'd have a "BBLinx" record (BBLSeq 102) containing the values 1236, 1234, and "spouse", respectively.


      Notice that BBLSeq 102 has Lynn under BeSeq B, whereas BBLSeq 101 had Lynn under BeSeq A. This is a consequence of the way "BBLinx" records are created, since the "Beings" record from which I begin always goes into BeSeq A, while the one I'm clicking on in the picker portal always goes into BeSeq B. Nonetheless, it's possible for Lynn's "Beings" record to have a portal showing ALL the records in "BBLinx" to which Lynn is connected, whether via BeSeq A or BeSeq B.


      My current frustration is that the only way I've been able to display this portal within Lynn's "Beings" record is to show ALL the data it's linked to:

      • 101, showing Lynn, the church, and "choir director"

      • 102, showing Kim, Lynn, and "spouse"


      Well, I don't really NEED to see the info for Lynn in the portal, since I'm looking straight at much more detail in the "Beings" record itself. All I really need to see are:

      • 101, church and "choir director"

      • 102, Kim and "spouse"


      Similarly, from Kim's record, what I want to be able to see in the portal is:

      • 102, Lynn and "spouse"


      In short, I want to be able to filter out the redundant parent-record data from the child portal. But I'm danged if I can figure out how to do it. Any ideas?


      For extra credit, I want to be able to sort the portal on the non-parent-record being.

        • 1. Re: Portal View of a Person-to-Person Join Table While Filtering Out the Parent Record

          I think I am following your question.  But let me ask you a question first.  Have you tried creating another field which takes the contents of BBLinx and filters out the parent record data, and then use that field for the display in the portal?


          See, for example, http://www.briandunning.com/cf/922

          • 2. Re: Portal View of a Person-to-Person Join Table While Filtering Out the Parent Record

            Hi Richard,


            First, I recommend that you consider using a multi-line key in your BBLinx table as the match field for the relationship to Beings. That way, a single BBLinx record will link beings in both directions - eg between Kim and Lynn and also between Lynn and Kim.


            Next, I recommend that you create a calculation field in Beings using the List( ) function to retrieve the contents of the multi-line key from all related records in BBLinx and use it as the basis for a self-join from Beings to itself (eg to a TO named LinkedBeings), then base your portal on the LinkedBeings TO. However you should define the relationship between Beings and LinkedBeings as a multi-predicate relationship, with the first predicate being an equi-join between the List( ) calculation field to the BeSeq field, and the second predicate using the "not equal to" operator and between the BeSeq field and itself. Like this (where cBeSeq_Linx is the calc field mentioned above):




            With the portal using this relationship, you'll find that it shows all the related beings in the portal, but doesn't show the parent being, and it will also be a simple matter to apply a sort to the relationship between Beings and LinkedBeings (or to the portal itself) to have the portal sort the displayed records according to data in the non-parent-record being record(s).





            R J Cologon, Ph.D.

            FileMaker Certified Developer

            Author, FileMaker Pro 10 Bible

            NightWing Enterprises, Melbourne, Australia