I am assuming this is a score per hole. Do you have a join table Player-HoleScore?
this would make it easier.
You have to do two things in the process.
Identify the low score
determine if the low score has a duplicate.
is this running in iOS?
I'm not a golfer, so please excuse me if this question is naive. If you have a foursome, do you have a unique record for each hole in a round of golf, with 4 slots for each golfer's respective score, or do you have a unique record for each golfer, with 9 (or 18) slots for that person's score on each hole?
I am using FM12 Pro on win 7. I can either use a a calculation to determine the min value of all the records or use the the value from the first record. It is correct that this a score per hole. I am not familiar with join tables as I have never used one.
If it were me I would have these tables at least:
I am thinking I would use eSQL to get the lowest number per hole and the count of that number on each hole.
Would likely be a Let() calc that would return a 0 or 1 based on the result of both checks. Someone might have a easier way though.
Say you have a table where the data for four players looks like this:
Player | Strokes | Hole
Jim | 4 | 1
John | 6 | 1
Jerry | 5 | 1
Joan | 4 | 1
Jim | 6 | 2
and so forth
You could define a self join relationship:
PlayerScores::Hole = PlayerScores|SameHole::Hole
Then this conditional format expression would be true for any player with the low score on a given hole--even if two or more have the same low score:
PlayerScores::Strokes = Min ( PlayerScores|SameHole::Strokes )
If it's just the strokes field that you want to format, you can use:
Self = Min ( PlayerScores|SameHole::Strokes )
Working on this. Will get back to you
Thanks for all the suggestions. The self join worked great. The final result is this. I created the relationship, defined 3 fields. Field "One" contains the score for the hole. Field "Sum Hole One is a calculation that compares "One" with the Min function of its it's related field and if they are equal the result is a "1". The third field "Count Hole One" is a summary field that counts the results of "Sum Hole One".
The condition is set as Self = Min ( Related "One" ) and Count Hole One = 1. Whe there is a score in hole 1 that has no duplicate score and that score is the lowest, the field changes background colour. otherwise no changes are made. I sure hope I explained this correctly.
Thanks again to everyone