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.
Option 1 works with all versions of FileMaker:
- Define a calculation field, set to return text in SCHEDULE as: List ( HOME_TEAM; AWAY_TEAM )
- 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.
- Define your relationship to use the X operator to match to all records in Schedule.
- Define a filter expression for the portal as: Teams::ID = Schedule::Home_Team Or Teams::ID = Schedule::Away_Team
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.