End of Nth record at 250?

[FMPing]

Anyone can explain why Nth record stop calculate an unstore field with the following:

Case(Get ( RecordNumber )=1;GetNthRecord ( num; Get ( RecordNumber ));num+ GetNthRecord ( SumStore; Get ( RecordNumber ) - 1)

)

Thank

[comment]

What is the last record number that does calculate? Frankly, I am surprised that v.8 lets you do a circular definition. Properly, you should be using a custom function for this - and that is limited to 10,000 iterations.

[FMPing]

It stop calculating at record 249, but if you click on 250 it will display the result.

Too bad it doesn't calculate all the way through. it will be a good way to use in place of summary field.

[comment]

I don't quite see the advantage - I believe it would be much slower than a summary, even if it worked. In any case, you're sailing in uncharted waters.

[Søren Dyhr]

I havn't tested if I reach same number, but it's not the easiest task you throw at the calc'engine here, fields depending on the result of another unstored calc. This is I take it - similar to recursive definitions have an upper limit for recursions, simply to prevent you from pulling the entire system down when making a foolish definition.

Perhaps you instead of having all these calcfield relying on each other, should use GetNth in a recursive CF to gather the ID's of the records in the found set, and then make it multilinekey for a selfjoin relation, over which you make a Sum( such a CF can gather approximately 10000 records. This won't give you the figures at each stage as you example shows.

Another idea is to utilize such a ID gathering CF to produce a list of ID's for the found set...

In each line could your field SumStore be defined as this unstored calc'field:

Let(theList=GetValueSet ( RecordID ; 1 );

Evaluate ( Substitute ( Left ( theList; Position ( "¶" & theList & "¶" ; "¶" & RecordID & "¶"; 1 ; 1 ) ) ; "¶" ; " + " )))

Utilizing this CF def. http://www.fmforums.com/forum/showpost.php?post/202738/

--sd

[comment]

And this is better than a summary field, because...?

[FMPing]

Just testing. I got a file that have 500,000 records quite a few of summary fields and the time bar on the window when the field is doing the summarizing is pretty long. Recursion only limit to 10,000. Script doesn't apply in this case. Just thinking out of the box with the FMP new functions.

[comment]

A summary of 500,000 records can take quite a while. Still, it's MUCH faster than your formula - not to mention putting it through a CF AND an Evaluate().

[Søren Dyhr]

And this is better than a summary field, because...?

Better or better... If you should wish local values in each record of a found set based on the previous records sum. It wasn't as clear a task for me as it were for you!

I just thought of a way to move the circular reference issue elsewhere ...if it's faster or slower??

--sd