4 Replies Latest reply on Feb 23, 2014 6:27 PM by Jmcl07




      Setting & Ranking Groups


           Hi Everyone,

           I am creating a database for a sporting event.

           All compete in rounds and come out with an overall point score & place. All competitors are members of a zone. At the end of the day I have to pull out the top 4 competitors (Based on overall points) from each zone to make a "team" (Teams are not preselected, it is based only on their scores at the end of the day) . The Zone teams are then ranked on the teams total points (Only the best 3 competitors to count so a team of 3 can compete equally with a team of four). Zones with 2 or less competitors cannot form a team. 

           I then need to be able to display these results on a print out.

           Are there any suggestions on how I might achieve this?


        • 1. Re: Setting & Ranking Groups

               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.

          • 2. Re: Setting & Ranking Groups

                 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.

            • 3. Re: Setting & Ranking Groups

                   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

              • 4. Re: Setting & Ranking Groups

                     Thanks! All works perfectly so far. 

                     I displayed the data in a list layout with the records of the competitors in portals.