Reference value based on parameter
Hi, I'm new to FM, using Pro 12. I'm trying to create a college basketball tournament database. My Games table which is related to itself, has a GameID PK field, with both TopGameID and BotGameID as FKs to represent the two previous round games whose winners will play in the given GameID, as well as a TopTeamID and BotTeamID to represent the teams playing in the given game, and finally a WinGameID to represent the winner of the game.
So my logic is that I want the TopTeamID for the current record to populate with the WinTeamID from the GameID that equals the TopGameID of the current record, and obviously the same with the BotTeamID.
I'm having trouble figuring out the best way to do this. I have some basic prior SQL experience, so I figured I could do something in the TopTeamID field like "SELECT Games2.WinTeamID FROM Games2 WHERE Games.TopGameID = Games2.GameID", but no luck. At least not with the syntax I'm using.
Any help would be greatly appreciated. Thanks in advance.