Counting (a subset of) related records

[aroza]

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)?

[falkaholic]

If I understand the problem right. You want prevent 2 players on the same team being the same position?

There are a couple ways, one could be to use calculation based relations to filter out active, and position.

A better way would be to make a new database called "Games" or something like that. In there you would store the 2 teams playing and the playerID of each position. That way its more stable then having a position for "next game" and so forth, and you can look back and store game info (like score) in it.

HTH

[aroza]

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.

[falkaholic]

I'm still not sure what your trying to do. But if you want to be able to have a list of more then one field/attribute. You can use the portal filtering method.

For example. To list all the players and the same team that play a certain position, make a calculation and a global text field.

In the calc you'd something like:

TeamID & "-" & PosistionField

and then make the global:

"<teamID wanted>" & "-" & "<position wanted>"

then make a new relation from the global to the calculation, and make a script to change the global (Note: doesn't have to be a global, could be anything from a calculation that combines 2 pop-up menus for example.)

Once you have that, make a portal and it will related to the team and position.

I don't think this will solve you problem, but take this method and run with it and you will go far.

[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.

[Vaughan]

"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 not? I consider this reasonably elegant, and extremely flexible.

I think of it like good carpentry: rather than limiting myself to the shapes and sizes of timber the hardware store stocks, I can fabricate the exact pieces I need, when I need them, with chisel and plane, some glue and nails.

[june_M]

but the new tools take time to make :