Lookup using repeating fields
I have a golf solution that uses repeating fields that stores static data for each course played. I have a related table that builds a scorecard for the course selected. One of these fields contains handicap values for men It usually is used for the whole course.
I have to add a course that has two values for the handicap depending on the tee box played. In the course table I added another repeating field for the second entry and an associated field the matches the tee color for the second entry. I am trying to set up a calculation on the scorecard table that will default to the main handicap table if the tee selected does not match the tee assigned to the second handicap value and use the second handicap value if it does. The tee selection takes place on the Course Info table
I have tried using the Lookup function in the calculation but only get the first entry in the repeating field
The relationship is Course Info::Course Name to Scorecard::Course
The normal lookup on the scorecard is Lookup(Course Info::Handicap)
I think I have to change the Lookup to a calculated value but I cannot get it work properly.
I hope I gave enough information.