3 Replies Latest reply on Nov 17, 2010 2:17 PM by KevinForte

    OR Relationship Problem with Lookup Table



      OR Relationship Problem with Lookup Table


      I have an issue I cannot quite find the answer for. I have 2 tables.



      I want to create a Layout for the TEAM and have a portal that shows the schedule for that team in Chronological order but I cannot get the relationship link right. I can obviously link team to home team and the portal shows all home games or link it to away team and it will show all away games. How do I link it to show ALL games?

        • 1. Re: OR Relationship Problem with Lookup Table

          Create a calculation field in the Schedule table, result text, stored (default), with both teams (their IDs, which you have in the Teams table, and should be using in Schedule):

          Home_Team_ID & ¶ & Away_Team_ID

          Return-separated text functions as an "or" field for a relationship. You will not however be able to create new records via this relationship, as the target is a calculation field (which by definiition cannot be modified, hence blocks creation). But otherwise it's fine.

          It will be somewhat awkward to show the names of the "home" and "away" teams in the portal however, as the source team would also show multiple times in the names. But either that is OK, or you could use Conditional Formatting on the fields in the portal to hide or gray out the source team's name.

          • 2. Re: OR Relationship Problem with Lookup Table

            Two options:

            Option 1 works with all versions of FileMaker:

            1. Define a calculation field, set to return text in SCHEDULE as: List ( HOME_TEAM; AWAY_TEAM )
            2. Define your portal's relationship to link to this new calculation field. Return separated values make it possible to have an OR relationship. (Putting these two values in different repetitions of the same repeating field also creates this effect.)


            Option 2 requires FileMaker 11.

            1. Define your relationship to use the X operator to match to all records in Schedule.
            2. Define a filter expression for the portal as: Teams::ID = Schedule::Home_Team Or Teams::ID = Schedule::Away_Team
            • 3. Re: OR Relationship Problem with Lookup Table


              That is an incredible tip.  I am using FMP 11 and I have been pulling my hair out trying to emulate 2 LEFT JOINS for the portal.  Since that is unavailable, this solution worked absolutely perfectly.

              I am sure there are MANY people who have this exact problem and have no idea about that method.  I found about 6 different methods, all of which included indexing a List item which I had trouble ever getting to work.

              GREAT JOB and thank you.