Jump to content

I'm having an issue with the getnthrecord function

Recommended Posts

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

Screenshot 2021-03-17 at 15.13.29.png

Link to post
Share on other sites

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 by comment
Link to post
Share on other sites

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.

Link to post
Share on other sites
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.


Link to post
Share on other sites
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.


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.