Automated Sports League Fixtures through a Script

[hutcheedingo]

Hi All,

You'll have to forgive me if I have posted this in the wrong section, still getting a handle on some of the terms used in the forums.

 

Onto my Scripting Issue...

I am aiming to build a Database for my local Field Hockey Association, that will generate the Match Schedule (Fixtures) Automatically, based on the particular details of each League, such as Game Day, Weeks of Play, Number of Teams, etc.

 

We have a total of 11 League broken up according to Age Group, Gender, etc.

 

Some League have 12 teams, others only 5, so there is a Bye week for the Leagues with Odd Numbers of Teams.

 

The most simple rule is that the Teams can't play more than once each Game Day

 

Depending on the number of teams some leagues will only have one game against every other opponent, while other may play them three or more times, in which case the Home/Away designation would need to change.

 

I want the match schedule to deliver something like this as an end product for each round of matches

 

Home:Team A vs Away:Team B; 6:15pm on Jan 31, 2013

Home:Team C vs Away:Team D; 7:45pm on Jan 31, 2013

Home: Team E vs Away:BYE; No Game

 

For a little more information, this site should fill in the gaps

 

http://en.wikipedia.org/wiki/Round-robin_tournament

 

I have tried writing this script based on Google searches, but I keep coming up blank... If anyone could help out, it would be much appreciated 

 

 

 

 

[eos]

Looking at the desired outcome will help you to define what is necessary to get there. In your example: to model a matchday, you must define entities like league, team, matchday, fixture etc., and their relationships.

 

• League: League 1, League 2 etc.

• Teams: Team A …

• LeagueInSeason: League 1 in 2013, League 2 in 2013 ….; knows its number of teams (TeamsInLeagueInSeason) and the number of rounds (as defined by you)

• TeamsInLeagueInSeason: the teams used for creating the fixtures, displaying the current standings etc.

• MatchdaysInLeague: e.g. Matchday 1 for LeagueA in 2013, date, time; if there are varying dates, store a range or start date, and put the actual date into each fixture

• FixturesInMatchday: that would be a line from your examples

• TeamsInFixture: TeamE, Team F …

 

So in your examples, each line is a Fixture (where you store date and time), which belongs to a MatchdayInLeague (expressed by a record in FixturesInMatchday) and has two TeamsInFixture (where each is either away or home). A bye would also be a fixture, but with only one TeamInFixture, no home/away status, and no score.

 

A script could collect all TeamsInLeague, then build the correct number of MatchdaysInLeague and the corresponding FixturesInMatchday, according to the number of teams and desired rounds in LeagueInSeason. All that's left to do then is to assign a date to the new records in MatchdaysInLeague.

 

At this point, you have all the information to express something like: League 2 in 2014, Matchday 13, date xx. Fixtures: Team A (home, score 3) vs Team B (away, score 2); Team F: bye; Team C …

 

I hope this gives you some ideas on how to start.

[hutcheedingo]

Thanks for that EOS...

 

I have all those pieces you mentioned in place.  What I am seeking now is a script(s) that will create the Schedule of Matches and populate it with the appropriate Home/Away/Round/MatchDate information.

 

I just want it too spit out the correct number of Match Records, then enter the Match Details for one round of fixtures (i.e. 1 game against all other teams) then if the condition of Confrontation Cycles (i.e. how many times they face off against the other teams is more than once) is set higher than one it reverses the Home/Away teams.

 

I also need it too set the times for each match so that there is an even distribution of games across the time slots (e.g. 6:30, 7:30, 8:30, 9:30) available for the competition.

 

I was hoping someone might be able to suggest a script or scripts that could create the match records, then go about filling in the information required.

[eos]

Thanks for that EOS...

 

I have all those pieces you mentioned in place.  What I am seeking now is a script(s) that will create the Schedule of Matches and populate it with the appropriate Home/Away/Round/MatchDate information.

 

I guess that's why they call it database PROGRAMMING 

 

Anyways, try this one. Obviously, there are some shortcuts; in a real system, you'd assign teams to a season and use a list of those team IDs, plus you'd wouldn't create a matchlist text field, but instead use IDs and create individual fixtures in another table (and yet another TeamInFixture table) ; then you can go and fill in that info… but I didn't want to rob you of all the fun.

 

No, don't, it doesn't work properly   Creating a starting set of fixtures and them simply shifting the position works too symmetrical and causes the same fixtures to get repeated within the same cycle – obviously that's not what we want. Let me look into that.

[eos]

No, don't, it doesn't work properly   Creating a starting set of fixtures and them simply shifting the position works too symmetrical and causes the same fixtures to get repeated within the same cycle – obviously that's not what we want. Let me look into that.

 

This was really bugging me! Found an algorithm on the Web, alas, it's only working for an even-numbered count of teams. Check it out.

LeagueManagement_eos2_evenNumberedLeagueOnly.fmp12.zip

[MartieH]

since you have already gone to so much work, would it be possible to ask you for a little more? ie - put this into 11? I have a database for a tennis league that tracks memberships and scores and develops standings but at the moment, the scheduling is done by hand because it's just too much for my little brain. I would love to see your solution to see if I can use it for my tennis chums. thanking in advance,

Martie

[hutcheedingo]

EOS this has worked an absolute treat.

 

Your concern about the scripting, etc. only working on an even number of teams is easily overcome by creating one team as a 'Bye Team' if there is a an uneven number of teams in the league.

EOS your help has been nothing short of fantastic.

 

Thank You Very Much, Much Appreciated

[eos]

EOS this has worked an absolute treat.

 

Glad you like it, but … 

 

 

Your concern about […] only working on an even number of teams is easily overcome by creating one team as a 'Bye Team' if there is a an uneven number of teams in the league.

 

… you gotta be f… kidding me!    (no offense!)

 

The point is: the Bye team needs to switch every matchday, and you can't designate it manually, because then you'd be back where you started – managing the whole affair by hand.

If you want the Fixture Schedule to be created automatically, the script must find a new Bye team every week - and you can't just use the even algorithm, because in the odd scenario there's something peculiar about the last matchday's team distribution.

 

Thank You Very Much, Much Appreciated

 

Why, you're welcome. Just wanted to let you know that in the meantime I have it working for even and odd team numbers. You of course don't need that …  but maybe the Australian Field Hockey Association would be interested in marketing a nation-wide (make that continent-wide!) solution…!? 

 

Cheers to Down Under

[eos]

since you have already gone to so much work, would it be possible to ask you for a little more? ie - put this into 11? I have a database for a tennis league that tracks memberships and scores and develops standings but at the moment, the scheduling is done by hand because it's just too much for my little brain. I would love to see your solution to see if I can use it for my tennis chums. thanking in advance,

Martie

 

How can I resist if someone asks so nicely?  Go schedule your chums

LeagueManagement_slimmedDownAndEvenOnly_eos.fp7.zip

[MartieH]

my goodness - you are the christmas brownie! and here I thought I was too old....

 

you are the BEST!

[hutcheedingo]

Ok...

What i meant to say was, say you have 7 teams, you create a fixture with 8 teams, and when you name the teams, assign one with a name of BYE.  Then when it comes time to publish the fixtures, the BYE is essentially a team in the fixture, but there's no need to manually change things 

 

That being said, I would love to see what changes you've made to the file too allow an uneven number of teams.

 

Since I'm still a Newbie with Filemaker, I am looking forward to learning more about scripting, and the entire Filemaker program in general 

 

I'm sure the Australian Hockey Association would be interested in something like this, but I am more focussed on helping out my local Association, which is quite small and very isolated, to save some poor person (ME) from losing my mind  trying to create all the schedules manually.

[eos]

What i meant to say was, say you have 7 teams, you create a fixture with 8 teams, and when you name the teams, assign one with a name of BYE.  Then when it comes time to publish the fixtures, the BYE is essentially a team in the fixture, but there's no need to manually change things 

 

All I have to say are two things : 1) sounds like cheating   , 2) sounds mighty clever!  I guess you have a point there, and my approach, though it works, was caused by occupational blindness.

 

Thus you've earned the right to look at this overly complicated method of mine.

LeagueManagement_forOddAndEven_eos3.fmp12.zip

[hutcheedingo]

All I have to say are two things : 1) sounds like cheating   , 2) sounds mighty clever!  I guess you have a point there, and my approach, though it works, was caused by occupational blindness.

 

Thus you've earned the right to look at this overly complicated method of mine.

 

I prefer to think of it more like Ninja Sneaky 

Thank You... 

[hutcheedingo]

Hi EOS... 

I've just gotten through inserting your solution into my own broader solution, and it is working perfectly.

 

Unfortunately, I am still a relative newbie with Filemaker, particularly with the scripting side of things, and I am having trouble determining how the script you created calculates and then enters the correct field data into the Fixtures Table.  I am assuming it's not by magic   

 

My problem is that I can't seem to figure out a foreign key (fk) to build the relationship for the results tabulation through.

 

So, I was wondering if you could have a look at including the following into the solution.

 

1) Results Entry Layout for each Fixture.  Basically, type in the result from the game.

 

2) League Standings Layout.  This would be tally and rank the teams in order, such as points, wins losses, goal differential.

[eos]

There's no magic involved; actually, even scripting doesn't come into play. All you need are the correct relationships.

 

UIM, you already have all the parts in place:

 

1 ) A Fixture record can look through into the related TeamInFixture - that's where enter the individual scores. Then you can show the score(s) either with the respective team, or create a calculated result displaying only the scores (e.g. to display it underneath the team names).

 

You may want to add a calculation that, based on the own score and the score of the other team belonging to the same fixture, determines if the match was a win, a draw or a loss, and sets points accordingly.; you'll need that for 2)

 

2) The fixture script creates all matchdays; a standing is a function of the matchday. So to show a tabular standing, you need one entry per matchday for each team, in which you summarize all results of the team within the current season up to and including the current matchday.

 

I think you could simply “misuse” the TeamInFixture entry for that purpose: each entry here would look via a self-join into all its own TeamInFixture entries for that season, only considering the current and the older ones, and sum up its points and goal difference (as defined in 1) ). I haven't looked into the script; unless this is already happening, you must extend the script to also enter the LeagueInSeasonID for each TeamInFixture (into a yet to be created field), otherwise this self-join doesn't work.

 

To show the table for a matchday, define a relationship from Matchdays to TeamInFixtures (where matchdayID = matchdayID), sort it by points and goal difference, and display it in a portal.

 

(And for a league with an odd number of teams, you need to suppress the display of the BYE team!) 

 

In addition, you could define a global in the Season table, to select a matchday number from a popup and immediately see the results and standings.

[hutcheedingo]

Thanks a lot... I'll have a tinker and see if I can get the desired outcomes

[eos]

Thanks a lot... I'll have a tinker and see if I can get the desired outcomes

 

You mean something like this?

[hutcheedingo]

You mean something like this?

 

Exactly Like That.

 

Would you mind posting the solution that includes that table?

[eos]

Would you mind posting the solution that includes that table?

 

Naah, it's not that good, and it's not quite ready yet. Why don't you try it yourself (there's an almost complete instruction some posts above …), then we can compare our results?

[hutcheedingo]

Naah, it's not that good, and it's not quite ready yet. Why don't you try it yourself (there's an almost complete instruction some posts above …), then we can compare our results?

 

Sounds like a plan... that being said, my money is on your result being much better than mine 

[hutcheedingo]

There's no magic involved; actually, even scripting doesn't come into play. All you need are the correct relationships.

 

UIM, you already have all the parts in place:

 

1 ) A Fixture record can look through into the related TeamInFixture - that's where enter the individual scores. Then you can show the score(s) either with the respective team, or create a calculated result displaying only the scores (e.g. to display it underneath the team names).

 

You may want to add a calculation that, based on the own score and the score of the other team belonging to the same fixture, determines if the match was a win, a draw or a loss, and sets points accordingly.; you'll need that for 2)

 

Hi EOS,

 

I've just managed to find some time to have a look at this, and was wondering if you meant that I display this information through a portal, or directly into the new layout being called Results_Entry.

 

I'm not really sure on how to make a calculation look through another record (ie. the other half of the Fixture Record) in order to calculate the Win/Draw/Loss query.

 

That being said, it is entirely possible I am headed down the completely wrong path with this so... please correct me if I am wrong

[eos]

That being said, it is entirely possible I am headed down the completely wrong path with this so... please correct me if I am wrong

 

You didn't say which path you're headed, so I can't comment on that. But I've come to the conclusion that it's simpler to post the file, warts and all, than to give some long-winded explanations (you'll find some comments in the Relationship Graph). Just note that there aren't any additional scripts.

 

That being said, don't blame me when kids come crying because you mangled their score sheet!

LeagueManagement_eosv4.fmp12.zip

[hutcheedingo]

EOS,

You're timely expertise and help has been invaluable, and I am very grateful.

 

Safe to say that I bit off more than I could chew on this project    .  Having done this job for a number of years with a notebook and a pile of scrap paper, I thought it may have quickly become second nature too me.

 

I must apologise for any frustration I may have caused

 

Again, thank you for your generous time.

 

Regards,

hutcheedingo

[eos]

You're welcome; hope this works for you.