To start, you need a related table of Zones with one record for each zone. Then calculation fields with aggregate functions such as sum and count can compute total scores and counts for each zone. And your relationship between zones can be sorted by player score so that the first 4 players will be the players with the best scores in that zone.
Thanks, I have set up the table with the zone. I don't have extensive filemaker knowledge. So could you explain the calculation field, as I don't know how I would make a calculation that only calculated the sums for each zone.
I also don't know what you mean by sorting a relationship?
My apologies if these are stupid questions.
I am assuming that you have this relationship:
Zones::__pkZoneID = PlayerScores::_fkZoneID
In Manage | Database | Relationships, you would doubleclick the relationship line to open a relationship details dialog. There, you can click a sort button for PlayerScores to open a dialog where you can specify that the PlayerScores records be sorted in descending order by the Score field.
A calculation field in Zones, defined as Count ( PlayerScores::Score ) can be used to confirm that there are at least 3 players in that zone with a score.
This calculation can be defined in Zones to calculate the sum of the Top 3 scores in a given zone.
Case ( Count ( PlayerScores::Score ) > 2
GetNthRecord ( PlayerScores::Score ; 1 ) + GetNthRecord ( PlayerScores::Score ; 2 ) +GetNthRecord ( PlayerScores::Score ; 3 )
) // case
Each of the functions that I have used may be looked up in FileMaker Help if they are unfamiliar.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Thanks! All works perfectly so far.
I displayed the data in a list layout with the records of the competitors in portals.