Hi guys,

I'm trying to make a simple system to calculate golf handicaps. The basic calculation is to take the best 10 scores from the last 20 scores of an individual.

i would like to do this with relationships & calculations if possible, instead of using a script.

I would have a table which holds the scores and a separate table which holds the players. How to find the last 20 games (scores) that a player played, and then how to select the 10 lowest scores from that list without using a script?

Sort the relationship by "score" in ascending order. Easy.

Nothing's ever that easy. He wants the lowest 10 scores from the last 20 scores.

What comes to mind is to sort the relationship by date descending and use a custom function to grab the 10 lowest.

I think you're going to need a script somewhere. I would suggest using a script to enter any new score for a player (rather than allowing multiple scores to be input into a portal). Once a score is submitted a script would run to flag the last 20 scores and pick the lowest 10. Then sum the lowest scores, divide by number of scores used and multiply by .96.

Also take into account the course rating (by tee box played) and slope rating. The lowest number of strokes does not always equate to the lowest "score". You should be using the differential not the actual strokes. (you can see an example in the attached image which shows a round where 87 strokes were played resulting in a lower "score" than from courses where less strokes were taken).

see http://www.ehow.com/how_15107_calculate-golf-handicap.html for starters.

"Nothing's ever that easy. He wants the lowest 10 scores from the last 20 scores."

Sort the relationship by date descending. Use the List() function to pull out the scores (which will now be newest to oldest) and cut off the list after 20 values.

Get this 20 value list then sort it by score ascending, truncate it at 10 values.

This could all be done in a calculation field.

Hi sbg2 - I'm trying to avoid using a script if I can. Yes, I understand about the course rating, differentials etc. Thanks

Vaughan, I looked at the list() function but I don't see how to cut off the list after 20 values? Nor how to take the list of 20 and truncate it to 10? I too thought it was easy until i tried to do it !

Check out the LeftValues function, then a Custom Function at Brian Dunning's site that will sort a list for you and then LeftValues again.

Yes, LeftValues() function and SortValues() Custom Function should do the trick.

Note that the SortValues() CF uses the MinValue() and MaxValue() CFs as well, and you'll need FMP Advanced to add the CFs to your files.

OK guys, I have decided to go with a script as I'm intimidated by Custom Functions !

Its now running well with a script.

thanks