GetNthRecord Result does not Calculate correctly

[CCBtx]

Arrrgggggg. I love these undocumented features! !!!

I have four fields.

Period = (1-60 months)

BeginningBalance = GetNthRecord ( EndingBalance ;Get( RecordNumber)-1 )

Credits = Say, 100

EndingBalance = BeginningBalance + Credits

So, Here are the Records

Period...BegBal........Credits..........EndBal

1...............0.................100..............100

2............100................100..............200

3............200................100..............300

4............300................100..............400

....and so on (the dots are spaces)

It calculates perfectly in the root table.

However, I have another table that is indexed to the first table using Period as the relationship

When I try to access EndingBalance from a form using the second table, I get....

Period EndBal

1 100

2 100

3 100

4 100

....it ignores the BegBalance (and I did not make a mistake and reference Credits).

If I take out the GetNthRecord calculation and hard code the number, it works just fine.

I suppose I could start at the first record and step through the found set calculating the Beginning Balance from the ending balance of the last record... but that seems to defeat GetNthRecord.

Has anyone seen this before? Help! :hmm:

[dansmith65]

It seems to be calculating as I would expect it to...

I think BeginningBalance field would have to be an unstored calculation since it uses GetNthRecord function - which means it's re-evaluated every time it is referenced. Then, GetNthRecord is context-dependent, which means if you change the found set, or the sort order, it will evaluate differently and that is effectively what you are doing when you are referencing the field from a related table.

I'm not quite sure what your goal is, but another method you could try would be to use a summary field defined to keep a running total.

[CCBtx]

I would probably experiment more... but my FM12Adv client locks up when I change the calculation.

..summary field. Execellent suggestion. Thanks.