aroza

Yes!!! By creating the the "TeamName-Position" text fields in both databases and relating them to each other, I am able to do exactly what I want to do. Sweet! Although this technique is highly effective and I will certainly use it a lot, I wonder if it is the most elegant solution to the problem. It seems that one shouldn't have to create additional fields of data that are used only for the purpose of facilitating relational calculations if there was a way to define more complex relationships to begin with. Why can't the relationship definition itself contain a calculation or multiple field matching? Is this a limitation of Filemaker compared to other databases? Is there an advanced technique I'm unaware of? Anyway, thanks a million for the nudge in the right direction.

Thanks for the reply, but unfortunately this is not exactly what I am looking for. I will indeed have the third database you proposed (GameLogs) used for scheduling purposes and storing lineups and scoring info, but I intend to populate this with lookups initiated by a script at the beginning and end of Sunday's NFL games (the idea being that fantasy team managers can manipulate their lineup right up until game time, and then they are set in stone). What I want to know is how to how to define a relationship(s) that produces a subset of records based on more than one field match (in this case, FFLTeam and Position). Then I can use this to display various data to the fantasy team managers, such as "Your roster currently includes 8 quarterbacks, 2 of whom are scheduled to start the next game. This is illegal. Please adjust your roster/lineup accordingly." I am betting that if the answer to this problem were a snake, it would have bit me by now.

The Situation: I am attempting to create an FMP solution to run our Fantasy Football League. There are two main databases: FFLTeams and NFLPlayers. The NFLPlayers database has an "FFLTeam" field that designates which fantasy team he is on (if any), and a "CurrentStatus" field (containing a 1 or a 0) which designates whether he is active or inactive for the upcoming fantasy game. A fantasy team owner will be able to change the CurrentStatus of the players on their roster to designate a starting lineup for the upcoming game. The Problem: I need a way to determine if a team's starting lineup is legal (ie., are too many players active? are there two starting quarterbacks?). Using a relationship called "roster" which relates the "FFLTeam" (team name) field from each database, it was simple to determine the total number of active players per team with a calculation field: ActivePlayersCount = Sum(roster::CurrentStatus). Unfortunately, my brain is about to explode trying to figure out how to calculate the number of active players per team at each position? In other words, how do I make calculation fields for ActiveQBCount, ActiveRBCount, etc.? Essentially, without using scripts or finds, how do you use relationships to show (or count) a subset of a subset (eg., NFLPlayers on Czech Mate's fantasy team who are quarterbacks that are active)?