8 Replies Latest reply on Mar 22, 2013 12:29 PM by JamesCarr

    Lookup using repeating fields

    JamesCarr

      Title

      Lookup using repeating fields

      Post

           Hello

           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.

        • 1. Re: Lookup using repeating fields
          philmodjunk

               . 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.

          • 2. Re: Lookup using repeating fields
            JamesCarr

                 Phil

                 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.

            • 3. Re: Lookup using repeating fields
              philmodjunk
                   

                        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:

                   Method 1

                   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.

                   Method 2

                   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 )

              • 4. Re: Lookup using repeating fields
                JamesCarr

                     Phil

                     Thank you

                     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

                     Jim

                • 5. Re: Lookup using repeating fields
                  philmodjunk

                       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.

                  • 6. Re: Lookup using repeating fields
                    JamesCarr

                         Phil

                         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.

                         Thanks again

                         Jim

                    • 7. Re: Lookup using repeating fields
                      philmodjunk

                           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.

                      • 8. Re: Lookup using repeating fields
                        JamesCarr

                             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.

                             Thanks