ID and ave. the best two golf scores for a record

[kenseye]

I have two tables: Players and Scores. Each player has multiple scores. I need to identiy and average the two best scores for each player while not including players with only one score. I don't know how to average only the two best scores.

FileMaker Version: 7

Platform: Mac OS X Panther

[kenseye]

Come on people...this is not supposed to be that complicated... Please help!

Anywhere from 1-6 scores for 75-80 players. I can manually identify the two best scores but it would be nice to do it automatically and average the two. I then post scoring average for all of the scores for a player and the best 2 average.

HELP!

[BobWeaver]

The simplest way I can think of doing this is to use a related valuelist of scores. The valuelist is automatically sorted in ascending order, so the two best (lowest) scores will be the first two items in the list. You can retrieve them using

LeftWords(Valulistitems(blah blah),1) to get the lowest score and

MiddleWords(Valulistitems(blah blah),2,1) to get the second lowest score.

However, if the two lowest scores are the same, the valuelist will omit one of them. So, you have to go through a couple of hoops to correct for this. Rather than go through the detailed explanation, I have attached a sample file that does the calculation. It's an fp5 file. If you are using FM7, you can convert it.

FileMaker Version: 6

Platform: Mac OS 9

GolfAverage.zip

[RalphL]

You can overcome the problems of duplicate scores by making a calculated field = Score & " " & Serial Number. Make your value list from this field.

[kenseye]

You guys never cease to amaze me.... The clever ways you work the data just blows me away. For people like me numbers are numbers and I only think number fuctions. To you guys it's data and you just know how to manipulate the data to get what you need. That's way outside my box.

Thanks so much...