6 Replies Latest reply on Jul 14, 2013 7:43 PM by adhutchinson

    Random Pairings of Teams/Individuals



      Random Pairings of Teams/Individuals


           I've been working on a Sports Competition Manager.  There are multiple leagues, with different teams within each league, and I am trying to find a way of automating the match schedule (games, team 1 v team 2, etc) so that I don't have to do it manually.

           I have a script that creates the number of matches automatically.  I would like the Home and Away Team Fields to be automatically populated in a "Random" order, so that every team will play each other team once in a given cycle of fixtures, which would then begin repeating after everyone has been matched up once, if required.

           Also, if the number of teams is odd, I'd need there to be a Bye for one team.

           Any Ideas?

        • 1. Re: Random Pairings of Teams/Individuals

               I've used FileMaker to assign Grand Prix (aka Pinewood  Derby) cars to random heat assignments.

               It's a lot like shuffling cards into random order and then "dealing" out hands for each heat.

               The method I used was to use the Random function to assign a random number to a field for each contestant. When I then sorted the records by this field, my records were arranged in a random order. I could then assign the first 4 records to heat one, the next four to heat 2, etc. Think of "heats" of 2 teams and the same method should work for you.

          • 2. Re: Random Pairings of Teams/Individuals


                 Do you happen to have an example of what you've described?

                 It sounds a little bit like what I'm trying to build

            • 3. Re: Random Pairings of Teams/Individuals

                   It should be very similar, but with groups of two contestants instead of 4 contestant heats. Much depends on how you structure your tables and relationships, however.

              • 4. Re: Random Pairings of Teams/Individuals

                     I'm thinking I might have to create a Table that has all the possible fixture outcomes available, and then duplicate those records via scripting, and the Team ID, Names, Venue, etc. are assigned after the records have been duplicated.

                • 5. Re: Random Pairings of Teams/Individuals

                       I don't think that should be necessary.

                       You should be working from a set of relationships such as:


                       HomeTeams::__pkTeamID = Games::_fkHomeTeamID
                       AwayTeams::__pkTeamID = Games::_fkAwayTeamID

                       Where HomeTeams and AwayTeams are two Tutorial: What are Table Occurrences? of the same data source table.

                       You'd then use Replace FIeld contents to load a number field in the Teams table with a random number, sort the records by that random number and then use a script to loop through them in order to create the needed records in Games. The first team record get's its ID placed in the Home ID field and the second gets put in the Away ID field so each loop assigns two teams to each Games record.

                       Byes then become the next issue as you'll need some Bye dates for cases where you have an odd number of teams, but that can be handled by an additional bit of scripting that detects when you are on the last team record and have just assigned it to the Home ID field.

                  • 6. Re: Random Pairings of Teams/Individuals

                         I received some help from a FileMakerToday Forum...

                         Below is the code that the moderator came up with... Works a treat.


                    set error capture [ on ]
                    #the layout you're on is Leagues
                    set variable [ $hometeamIDs; Waarde:List ( teams | list::ID ) ]
                    set variable [ $awayteamIDs; Waarde:List ( teams | list::ID ) ]
                    set variable [ $leagueID; Waarde:leagues::ID ]
                    set variable [ $numberofteams; Waarde:ValueCount ( $hometeamIDs ) ] Variabele instellen [ $count_outer; Waarde:1 ]
                    Go to lay-out [ “matches” (matches) ]
                          set variable [ $currenthome; Waarde:GetValue ( $hometeamIDs ; $count_outer ) ] Variabele instellen [ $count_inner; Waarde:1 ]
                                 set variable [ $currentaway; Waarde:GetValue ( $awayteamIDs ; $count_inner ) ]
                                 If [ $currentaway ≠ $currenthome ]
                                  Nieuwe record/nieuw verzoek
                                  set field [ matches::hometeamID; $currenthome ]
                                  set field [ matches::AwayteamID; $currentaway ] 
                                  set field  [ matches::leagueID; $leagueID ]
                                End If
                               Exit Loop If [ $count_inner ≥ $numberofteams ]
                               set variable [ $count_inner; Waarde:$count_inner + 1 ]
                               End Loop
                         Exit Loop If [ $count_outer ≥ $numberofteams ]
                         set variable [ $count_outer; Waarde:$count_outer + 1
                    End Loop
                    Go to lay-out [ originele lay-out ]

                         But!!  This script creates a full Home & Away Schedule for all of the games in the League's fixture... Which leaves me with two problems

                         1) I need to automate the dates from a Field called Start Date.  Games are held weekly, and each Team would only be allowed one game per Round.

                         2) Some competitions are too big to host a full Home & Away Schedule, we are limited to between 20-28 weeks of play, inc. playoffs, so the larger competitions would only play every other team once.

                         For an even number of teams (say 16) this would mean an odd number of games (n_total=15), in which case, half the teams would play more than half of their games at home (n_home=8; n_away=7; n_total=15) and vice-versa (n_home=7; n_away=8; n_total=15)

                         Again, all help is greatly appreciated