How to do this!

[Shebco]

Ok, first let me apologize if this is too basic of a question.

I had developed some pretty solid database structure using Helix Express 'back in the day'. Maybe the experience I got from that to Filemaker is the reason I'm stuck now. At least I had database design experience to work from.

I have a very basic application to program. 8 guys playing golf for 4 rounds. After each round I calculate things like handicaps, different payouts for the current round etc. I have set up the database in FM as 18 holes (A1...A18 then B1...B18 for the next round etc) and their name. My thought process is that one record consists of the Golfer and A1..A18.

All my calculations are fairly complex and on the fly. Kind of a brute force approach. In Helix I would post to another database the results but here I can only figure out how to do live calculations that arrive at a report showing who won what, handicaps for the next day etc. The whole thing slows down alot when you start entering the fourth day's scores but I can live with that since it's just a golf outing!

Here's my problem. I'm trying to do a report that looks at all the rounds of golf and determines who had the best score on each hole for all the days. In Helix I would do a 'lookup for x = x" type calculation and it would look through each hole over the four rounds and tell me the lowest score for each hole. I then need the golfer's name that had that low score. It's more than a 'minimum of A1, B1,C1,D1', I need the golfer's name for it.

I have a calculation that gives me that but only when I scroll through the names. This is what I don't understand. I created a separate database (call it Summary) and tried the same lookup and it came back negative. How can i find the data I'm looking for in the 8x4 rounds set of data?

Again, sorry for this but I'm confused as to Filemaker's terminology and structure when it comes to this.

Thanks so much in advance for any help!!!

[mr_vodka]

I know that there have been past post on here regarding golf scoring. You can do a search for +golf and you should come across a few posts.

I think that in your case, there should be 3 tables. A table for players, match, and holes. In the holes table you can have a field that shows which date it belongs to.

So in your case, there are 8 players in a match playing 72 holes of golf each.

[Lee Smith]

I just did a search for +golf +slope +score (use the pluses in your search) and only pulled up one Topic. I know that there are others, but this one sounded really good, until I saw that it included Custom Functions. However, sometimes you can use the calculations from a CF without a problem, so take a look at this topic and see if it helps. Link

HTH

Lee

[Shebco]

Thanks guys but this one is totally custom that I think I can finish it off if I can figure out how to pull the specific info I need out of the database.

[Fenton]

"I think I can finish it off if I can figure out how to pull the specific info I need out of the database."

Famous last words :-] Just kidding. I think that with FileMaker 5 (is that true?), you are going to have to script this, and put the results somewhere.

First you need to add up all of each person's scores for the 4 days, for each hole. That would require a self-relationship in Holes, or a relationship from People, on the person's name (or ID), as well as the tournament ID (which in 5 would require them to be concatenated via a calculation field). Unless your whole database is only for 1 tournament, in which case the name alone will suffice.

That would be 18 calculation fields. Then sort by that field for each hole. They guy at the top is the lowest for that hole. Set his name into the holes file, or (if multiple tournaments in one database) into a separate file/table for the results. Repeat for each hole. I think that would work.

[Or you could script this, via a Loop in people, each iteration doing Find for each person in the holes table, adding up each hole's scores, via a Summary field, returning and setting the result for each hole back into the person's record; brute force, for those with "relationship phobia" only.]

Edited March 1, 2008 by Guest

[Shebco]

Thanks Fenton!

I'm actually trying to find out if there was a 'skin' on each of the holes. If you're not familiar with the concept it's the single lowest score on the hole. In other words if everyone gets a par on the hole for the 4 rounds but one guy gets a birdie, that's a skin and is what I'm looking for. If there are two birdies there are no skins etc.

I had written a calculation in the 'Helix days' that said if count for score=par - 1 is 1, lookup golfer name. The calculation would look through the 4x8 array for that hole (8 golfers playing the hole 4 times) and find the golfer name or return 'no skin'.

I tried the same calculation in FM and it doesn't return the correct info unless you scroll through to the golfer who has a skin on that hole and then it shows it. I just don't understand how FM works in this regard I guess. Why can't I extract one piece of info from a record with a calculated match?

[Fenton]

The short answer is the Min() function. If you had a self-relationship on that Hole-Tournament, and a calculation field of the 4 scores added up for one person for that, then the Min ( self-relationship::_cHoleScoreTotal) would be the lowest score for that hole. If you looped thru the scores until that was true, then marked that one as the lowest, you could Find it.

I was only dealing with 1 game however, not 4 days of games. But I believe the same general principle applies. I suppose it could be done from a more overall view, not scripted, if you had 18 relationships, one per hole. But the people I did this for had other considerations, like the same person could only get 1 1st place "skin" per tournament; something like that. So a script was needed, as it had more flexibility to check for that, skipping that guy if he'd already got one.

It's kind of funny, because I've only played golf once in my life. Scoring was not really a big issue, finding the ball and not hitting anyone were bigger concerns.