In table Planting, I am evaluating a Calculation field, Quantity_Display, which has 15 repetitions from a Number field, Quantity, which has 100 repetitions, to . Which repetitions are evaluated from which depends on two values in a related table. I use the following calculation:

Let (         [ $i = Get ( CalculationRepetitionNumber );           $j = Property::Year_Curr - Property::Year_Start - 14;           $k = $j + $i         ];

Quantity[$k]     )

(I know I don't need both $j and $k but they helped me debug)

The first repetition works fine evaluating Quantity_Display[1] to the value of Quantity[5]. This is what I want, because Property::Year_Curr - Property::Year_Start solves to 18. All subsequent evaluations fail and I have determined that is because both Property::Year_Curr and Property::Year_Start are null for repetitions 2-15.

Why is this so and how do I deal with that?

Thanks, Paul

Not sure what exactly you are doing and for what purpose (although I must say it seems there ought to be a less convoluted way).

In general, each repetition of a repeating calculation field retrieves the value from the corresponding repetition of a referenced field. If the referenced field is not a repeating field, you need to use the Extend() function to make its value available to all repetitions of the repeating calculation field. 

 

Edited May 13, 20205 yr by comment

Ah, that works. Because without extend, I presume, FMP tried to use a second, third, (and so on) repetition of Property::Year_Curr and Property::Year_Start.

Thank you. As for what I am doing, this is a record of blooms in a hybridizers' development garden. The 100 repetition field is the quantity of blooms each year starting with Property::Year_Start. The 15 repetition field is the quantity of blooms in this year and each of the past 14 years. There is no year-end data manipulation necessary, just the changing of Property::Year_Curr.

2 hours ago, Paul Hunt said:

Because without extend, I presume, FMP tried to use a second, third, (and so on) repetition of Property::Year_Curr and Property::Year_Start.

Either that or it knows that the fields are not defined to have repetitions and doesn't even try. In either case, the result is empty.

 

2 hours ago, Paul Hunt said:

The 15 repetition field is the quantity of blooms in this year and each of the past 14 years.

Wouldn't it be much simpler to show this in rows instead of columns? A report summarized by year should accomplish such thing with minimal effort. But this won't work if your original data is kept in a Quantity field with 100 repetitions - which I presume is the original sin here.

 

Edited May 13, 20205 yr by comment

6 hours ago, comment said:

Wouldn't it be much simpler to show this in rows instead of columns?

 

Actually, no. Columns are years, rows are varieties or plantings. That way it shows the history of how a plant or variety has thrived (or not) and a comparison of varieties. Plus this way there is no preparation of data by scripting necessary except for the find and sort. And it works the same way in portals, just fewer columns (years of history).