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.
Do you happen to have an example of what you've described?
It sounds a little bit like what I'm trying to build
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.
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.
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.
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) ] # Loop set variable [ $currenthome; Waarde:GetValue ( $hometeamIDs ; $count_outer ) ] Variabele instellen [ $count_inner; Waarde:1 ] # Loop 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