I'm having an issue with the getnthrecord function

[Nigel Strudwick]

I'm having an issue with the getnthrecord function. I am using it in conjunction with the get(recordnumber) function to get a value from field X in the previous record in a sorted list (i.e. getnthrecord (X ; get(recordnumber) -1), and then add or subtract something from it to give the value of field X in the current record.

It's working fine for the first 130 records in the found set (out of about 150) but thereafter it shows a ? in the field--and I cannot click that field to see what value is in there, so it's not obviously a wrong formula or a larger number than the size of the field can contain. I've tried it in various databases/tables and it always seems to happen at about the same record in the found set, regardless of the size of the data.

Can anyone shed any light on why this is happening please, or have another way I can do the same thing? The attachment shows sample data sorted on a time field, and at the left, the getnthrecord is used to add 10 to the value of the same field in the previous record. 

Thanks in advance...

[comment]

As I had the opportunity to remark just recently, Filemaker places a limit on the length of the chain of a cascading calculation. This is undocumented, and I don't know if the limit is intentional or just a mater of running out of memory.

In any case, you need to find another method to do whatever it is you are trying to do. It sounds like a summary field defined as running total might be a good solution? Or perhaps add 10 * Get ( RecordNumber ) to GetNthRecord ( X ;  1 )? It's difficult to advise when the purpose is not known.

 

Edited March 17, 2021 by comment

[Nigel Strudwick]

Thank you. I was thinking it had to be some limit of which I was unaware, and I really appreciate your suggestion of a summary field with a running total, which I hadn't thought of, although I have used them before. My only problem with that sometimes I need to add and sometimes to subtract, but I will see what I can do.

I downloaded the trial version of FMP19 and it worked better in that, but in Filemaker Go 19 it was still giving the ? above.

All suggestions are very gratefully received.

[comment]

2 minutes ago, Nigel Strudwick said:

sometimes I need to add and sometimes to subtract

Shouldn't be a problem: just "add" a negative number.

 

[Nigel Strudwick]

Yes that did occur to me! Just got to adjust the way I think about it. Thanks again.

Edited March 17, 2021 by Nigel Strudwick

[comment]

17 minutes ago, Nigel Strudwick said:

got to adjust the way I think about it

In my experience, that is the key to solving most of the problems presented here - or in general.

 

[Nigel Strudwick]

And I thought about how to achieve it on my walk this afternoon and it is now fixed. Thank you so much for that simple tip! Stay well!