### Title

Calculating tennis player ratings from game results

### Post

It should be clear that I am not too astute regarding understanding Filemaker Pro, nor am I diligent in developing solutions (its been over a year).

I had posted elements of my database creation Help with tennis database set up and calculations and Script to use a find result as source for new search. I am able to track who is playing with whom, the players, and sort the ratio of number of times they have played to the number of times they have brought balls. Not pretty, but so far, so good.

Now, I would like to calculate an Elo type of rating for each player. A little more detail. We play doubles and change partners after every set. For the Elo rating, I plan to calculate the expentency of winning for each team by calculating a team average and do the calculations from that.

I have collected game results in a table with each player's results.

I did not know how to handle this data, so I exported a fraction of this data and imported it into Excel. In Excel, I changed the data so each set was repeated for each match (set_1, set_2, and set_3) and the scores for each set. I then manually calculated the Expected result (1/(1+10^(opponent rating-home rating)/400). Initially, each teams rating is equal, so each team has a 1/2 or 50% change of winning. From that result, the winners receive a new rating (old rating = 32*(1-expected). This means I averaged each teams rating, and subtracted rating_1 from rating_2.

Excel let me verify the rating system and suggested how I might alter the calculations. Accomplishing this same calculation in Filemaker is a completely different task. Can the calculation be carried out with the table I collect the data from or should it be transferred to a more efficient table? (I changed the table structure for the calculations in Excel.) In any case, I have no idea about how to change the table structure or to get Filemaker to do the calculations.

I didn't know how to get Excel to do it either and so I just manually selected which cells to use in the calculations. I rather suspect that I will not understand the jargon used to do the calculations. In one of my earlier posts, what I discovered was the jargon describing how to use the result from one seach to use in another search. I found how someone had succeeded in doing this and modified their script. (It is a lot easier to modify a *working* script than write one on my own.) What I am hoping is someone might understand the basics of how the data must be manipulated to suggest where I may find a solution or how to search for a solution. Players may be widgets and scores may be costs and sets may be orders or ??? Suggestions?

Let me add to and be more specific about this problem. I realize the match result table is best handled with a single table and different set numbers (thanks Phil). So I want to convert the set scores from the match table (above) to a set table (below).

Looking at some examples leads me to, how do I accomplish the following? From the Match table, I should be able to lookup who played from a MatchID. (I found an example in which address data was looked up from a customer ID.) In this instance, I want to lookup and create four records for each MatchID. How do I do that?

Secondly, I want to look up and create an additional set of records for each SetID. However, the SetID is obtained from a different field in the Match table. I need to reread the Match table to get the same MatchID, hence PlayerIDs, and create four more records with a different SetID.

I hope, that despite all of the tennis discussions, someone can suggest how to answer the underlined questions (or how I might repost the underlined as separate questions)?