I have a table called "Teams" and a table called "Games". The games table has fields like "Home Team", "Away Team", "Date", "Away Score", "Home Score", etc. What I want is in the "Teams" table to have a portal that displays all games the team has played in. I can obviously get it to do that with it showing the home team, away team, and result fields, but what I want is something like this:
Say I was looking at the games for TeamA - I want an "opponent" field that says "@TeamB" if TeamA was away or "vs TeamB" if they were home. It would be great if I could have another field that says "W", "T" or "L" depending on the result. For whatever reason, I'm having issues figuring out how to do that.

Any help would be greatly appreciated!!

case(isEmpty(home team);"vs "& team; isEmpty(away team); "@ " & team)

 

As far as the W, T or L - this would depend on how you have scoring set up - if there is a calculation - scores are entered and calculated together to determine the winner - whatever the criteria is for winning, losing or tying, you can write another case calculation to give the W, T or L values. Or - you could have a drop-down list - W, T, L that would be entered when scores were entered to give the result of the match.

 

hth,

Martie 

I used a variation of that calculation for the "Opponent" field in the Games table:

Case(Away_Team_Name = T26_Games||Teams::Team_Name; "@ " & Home_Team_Name ; Home_Team_Name = T26_Games||Teams::Team_Name ; "vs "& Away_Team_Name)

 

I thought it was working great, until I realized it only worked for one of the teams. So if I go to Team A - I can see everything perfect, but if I go to Team B, it shows the same thing that showed up for Team A. Can't figure out how to fix that.

what is the relationship between the games table and the teams table?

 

you probably need two TOs - one for home teams and one for away teams.

 

M