. In the course table I added another repeating field for the second entry
Another repeating field?
Which fields do you have that are repeating and which are non-repeating in this set up?
I think that you have a repeating field of handicaps with one repetition for each hole and now you have added a second repeating field for the second Tee box?
It would seem that you need an auto-enter calculation that looks up data from one or the other field depending on the value of a field in score card that identifies the Te-box used:
If ( TeeColor = "Red" ; CourseInfo::Handicap ; CourseInfo::Handicap 2 )
But I would suggest replacing your repeating fields with tables of related records as this can make working with the golf stats more flexible.
Both the fields in Course Info are repeating fields and the Score card field is also a repeating field.
I used repeating fields to reduce the amount of fields used in the score card table. The calculation you suggested only replaces the first entry in the repeating field.
I used repeating fields to reduce the amount of fields used in the score card table.
I am not suggesting that you have 18 fields in one scores record for an 18 hole golf game. I am suggesting that you use one score field in a related table of 9, 18, however many holes you wish to record as a single game. This avoids a number of lookup issues and also can make for more flexible options when it comes to working with these scores.
Since your last post, I have been experimenting with repeating fields. I almost never use them as there is almost always a better alternative.
I've come up with two ways to get this to work with repeating fields:
Define two records in courses, one for each Tee Box option. Use the Tee Box field as an additional match field so that you match to a course record by course ID and by Tee Box.
Define two repeating fields in Scores for each set of handicaps. Use the looked up value field option to copy from a different repeating field in courses for each.
Define a calculation field with 18 repetitions that uses this expression to return the correct set of handicaps:
If ( Extend ( TeeColor ) = "Blue" ; Handicaps I ; Handicaps II )
Actually Method 1 worked out the best. When I add a record in the ScoreCard table I now select the Course associated with the second set of handicap values. I have to work on a better relationship when using other tees. Method 2 worked also but I did the calculation in the CourseInfo table. When I change the Tee the info changes there and the original lookup works. I just have to decide how to go with it There are a lot of other calculations that use this information and I have to be sure they do not get messed up.
Thank you again
I put the fields in scores on the assumption that the handicap values might change in the future and you wouldn't want that change to alter previously entered score records.
The handicaps are values that are assigned to the course when it is rated. Once they are established they wont change unless the course is redesigned. That is primerally why I used repeating fields and the lookup.If they do change it will have to be altered for the whole course. This is the first course I have come accross where there were two values for a particular tee. These values are used to define the difficulty of each hole.
If a course can be redesigned, then it is possible that the handicaps can change.
And you do not need to use repeating fields for this. Frankly they complicate and limit what you can do with these scores.
I do have to look at the way I have designed the whole solution. It is fixed for now and that was my main concern.