Mapping Repetitions to New Field

[Paul Hunt]

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

[comment]

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, 2020 by comment

[Paul Hunt]

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.

[comment]

  On 5/13/2020 at 7:09 AM, 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.

Expand  

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.

 

  On 5/13/2020 at 7:09 AM, Paul Hunt said:

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

Expand  

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, 2020 by comment

[Paul Hunt]

  On 5/13/2020 at 9:37 AM, comment said:

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

 

Expand  

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