GetNthRecord

[Digital Life]

Here's a link from Filemaker's support knowledge base:

http://help.filemaker.com/app/answers/detail/a_id/8389/related/1/session/L2F2LzEvdGltZS8xNDYwMDU0Mjg1L3NpZC9GSm80R3JObQ%3D%3D

I have this calculation below in an 'Opening Balance' field but it doesn't work after 29 records. Is there any work around for this?

If( Get ( RecordNumber )=1;combinedRecords_GL Accounts_AccountExport::Bal Fwd;GetNthRecord ( Closing Balance ; Get ( RecordNumber ) - 1 ))

[comment]

Couldn't you use a running total summary field?

[Digital Life]

I can't get summary fields to do what they should either and I believe from Filemaker's site that this function is the correct one for what I want to do. I've attached a file as an example of the issue. It does exactly what it should until the 72nd record then '?' marks start to appear. If you click in the question mark the correct value appears at record 73 but at record 74 you can't even click in the fields. I think this is a bug. Any one have any ideas?

Test.fmp12.zip

[bruceR]

Summary fields are a standard, robust FileMaker feature.

If you can't get them to work - better to learn how to use summary fields correctly.

GetNth is the wrong approach.

Test_MODBFR.fmp12.zip

[Digital Life]

Two things. Get nth record should be a robust calculation as well and has the advantage that it can refer to the field being accumulated AND to other fields as well. So it has strengths. 

 

I've been in touch with filemaker and they have concluded that this is a bug and they will be working on it for a solution. 

Now as for summary fields I've attached a file. I hope someone will straighten me out here.

 

The numbers make no sense to me. Thanks in advance.

Summary Test.fmp12.zip

[bruceR]

No surprise. You created a self-referencing calc. Don't.

TestMOD2BFR.fmp12.zip

[Digital Life]

Bruce that solved the getnth record question marks at record 72 as well. Many thanks.

 

The last issue is that I need there to be a category and I need the calculation to start fresh with each change in the category. So in your solution the PriorLineRT does not do this. I think the calculation needs to figure out if it is the first record in a category grouping. Can you think of a way to do this? Or a way to start the PriorLineRT as zero in the first record of each grouping?

[Digital Life]

For others who may read this thread and find it helpful, which is what it's all about, here's a 3rd version of your file with the calculations in it that figure out what record in each group a record is, either the 1st or anything else and calculates to 0 if it is the first.

TestMOD3BFR.fmp12.zip

Edited April 22, 2016 by Ocean West
context

[bruceR]

Yes, running totals (and running counts) with restart are the standard sub summary feature I was referring to.

Note that the "1" field is not necessary.

TestMOD4BFR.fmp12.zip