AnsweredAssumed Answered

Lookups within a range of a repetitive fields

Question asked by miw on May 16, 2015
Latest reply on May 21, 2015 by philmodjunk

Like • Show 0 Likes0
Comment • 3

Title

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?

No one else has this question

Outcomes

TKnTexas May 16, 2015 12:53 PM

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.

Actions

miw May 21, 2015 12:34 PM

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.

Actions

philmodjunk May 21, 2015 1:17 PM

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.

Actions

3 Replies

TKnTexas May 16, 2015 12:53 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
miw May 21, 2015 12:34 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
philmodjunk May 21, 2015 1:17 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions

Lookups within a range of a repetitive fields

Title

Post

Outcomes

Related Content

Recommended Content