lookup on field repetitions not working

[julesw]

I am having a problem with a calculation that is not working on the repetitions of a repeating field.

it is to track a magazine subscription info. it works like this:

1. a date is entered in the 'invoice paid date' field

2. a field called 'first sub issue' then looks up the next value in another table which has all the dates for every issue published. [ie, if invoice was paid on 10/5/2004 and the next issue was published on 20/5/2004 and this is issue number 4, this will be the calculation result.]

3. a field called 'last sub issue' simply calculates 'first sub issue'+9.

This all works perfectly in the first repetition of the fields, but not in any of the others - even if i use exactly the same date that worked in the first. it just doesnt lookup the issue numbers.

all of the fields in question are repeating, and i even tried setting the fields in the lookup table to be repeating, in case that would fix it, but it made no difference.

any help much appreciated! is there a better way i should do this?

[bruceR]

Yes.

Get rid of the repeating fields.

[julesw]

any other suggestions? i need the repeating fields to keep track of subscriptions over multiple years.

[MarkWilson]

Repeating fields calculate data in a straight line. Field_A [5] (Field_A [repetition number 5]) can be used in a calculation for Field_B [5]. If the 'first sub issue' field is not a repeating field (first sub issue [1]), it cannot be used in calculation for Field_B [5]. To allow 'first sub issue' [1] to be used in other repetitions use the Extend ( non-repeatingField ) function.

Extend ( first sub issue ) + 9

[julesw]

all of the fields in question are repeating.

i think it's the lookup that's failing, rather than the subsequent (first sub issue + 9) calculation.

i did try extending the fields in the table being looked up, but then the lookup didnt work at all! (ie, not even in the first repetition)

[julesw]

ive got it to work by making the lookup fields repeating and just copying the values from the first repetition into all the others. seems like a cumbersome solution, but at least it works!

[bruceR]

"Any other suggestions? i need the repeating fields to keep track of subscriptions over multiple years."

No, you do not need repeats for this purpose. The difficulty you are experiencing comes with the territory when using repeats. Time to learn more about how to use relational design.