3 Replies Latest reply on May 21, 2015 1:17 PM by philmodjunk

    Lookups within a range of a repetitive fields

    miw
    miw May 16, 2015 11:10 AM

      Title

      Lookups within a range of a repetitive fields

      Post

      I have two tables:

      Type and Item

      Type has the fields T_Year, T_Grade and T_Value.  For both Grade and Value fields, there are 10 repetitions; say that the data in these two repetitive fields for T_Year "2015" are as follows:

      T_Grade: A; B; C; D; E; F; G; H; I; J

      T_Value: 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 

      Item also has three fields I_Year, I_Grade and I_Value.  I want an Item record to lookup a value (I_Value) in the Type table for an input I_Year and I_Grade.  So if I have an Item with I_Year "2015" and a I_Grade of "D", the I_Value lookup from the Type table would then be "4".

      I created the relationship between the Type and Item table as follows:

      T_Year >---- = ----< I_Year and

      T_Grade >---- = ----< I_Grade

      I was hoping that the I-Value would then be looked up among the repetitions within T_Grade (in this case it would be the 4th repetition for the I_Grade value of "D") and lead to the equivalent T_Value repetition (the 4th repetition in T_Value being "4" in this example).  However, when there is a match on the Year field, only the first Type repetition for T_Value is given.  Is there a way to due such a lookup within repetitive tables? 

       

       

       

      • 63 Views
      • Tags:
        • 1. Re: Lookups within a range of a repetitive fields
          TKnTexas
          TKnTexas May 16, 2015 12:53 PM (in response to miw)

          Repetitive Fields are VERY old technology, pre-Version 3 before there were relationships between files.  I seem to remember though that searches were hampered by being tied to first repetition only.  I started using with version 3 and I have only used Parent Child record relationships.  I think you have hit the wall that is inherent with repetitive fields.

           

          • 2. Re: Lookups within a range of a repetitive fields
            miw
            miw May 21, 2015 12:34 PM (in response to miw)

            That's a bummer.  It would be a powerful ability to utilize repetitive fields in the way I would like to.  I wouldn't need to create a new record for each single iteration and I could have a simple layout showing the two fields and their repetitive inputs displayed in full.  Creating one at time can be confusing and time consuming.

            • 3. Re: Lookups within a range of a repetitive fields
              philmodjunk
              philmodjunk May 21, 2015 1:17 PM (in response to miw)

              It really shouldn't be that much more effort to set up a table of related records using a portal or even a "horizontal portal". You still have to enter the individual values either way and you can still: :...have a simple layout showing the two fields and their repetitive inputs displayed in full."--that's what a portal is for after all.

              And you gain a great deal more flexibility with the related records approach that was never possible with repeating fields.

              But just to be clear, it is possible to do what you want with repeating fields, we just don't recommend that you do it this way due to the many limitations.

              A looping script can loop through the repetitions so you can perform a find or use a relationship to get to the year record and then loop to look up the value from the correct repetition.

              That's just a lot more complex than a relationship that could take you to the year and the value all in one query of your data.