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.
Showing us your table occurrences and then indicating the "context" for your ExecuteSQL function call would help fill in some missing details here.
Your SQL references two occurrences of Games, Games and Games2 without defining a join clause to link them in a relationship so that is the most obvious issue that shows in your post.