I feel that I should know the answer to this but just can't work it out...

Fields:

date

numberA

numberB

calculationA (= "numberB / number A", stored)

Objective:

To create a new calculation field (calculationB) that contains the cumulative product of calculationA in descending date order:

date (most recent first), calculationA, calculationB (= result)

date 1, cA1, cB1 = 1 (most recent record to always return 1)

date 2, cA2, cB2 = 1 x cA1

date 3, cA3, cB3 = 1 x cA1 x cA2 (i.e.: cB2 x cA2)

date 4, cA4, cB4 = 1 x cA1 x cA2 x cA3 (i.e.: cB3 x cA3)

date 5, cA5, cB5 = 1 x cA1 x cA2 x cA3 x cA4 (i.e.: cB4 x cA4)

etc

So, in essence, calculationB is always equal to the product of calculationA and itself (both from the previous record).

I can make this work using a calculation based on a relationship:

tableDATA

tableDATANXT (contains all records with a date greater than the current record, sorted by ascending date)

tableDATA::calculationB = Case ( tableDATA::Date = tableDATA::FirstDate ; 1 ; ( tableDATANXT::calculationA * tableDATANXT::calculationB ) )

BUT... only some calculate with the remainer displaying a "?". The actual number that calculate is variable - perhaps the first 177 out of 769 records but appears to be dependent on whether the results are displayed in a table or a portal.

Clearly the calculation works, just not enough times.

Does anyone know of a better way to calculate a running balance as the product (rather than the sum) of a given field in a specified (date) order?

Thanks

CP

I am not sure I follow your description fully. Perhaps this might help?

https://fmdev.filemaker.com/message/104748#104748