AnsweredAssumed Answered

GetNthRecord Problem (Bug? Feature?)

Question asked by csouth3 on Jun 1, 2010
Latest reply on Jun 3, 2010 by comment_1


GetNthRecord Problem (Bug? Feature?)

Description of the issue

FileMaker Product(s) involved = Filemaker Pro Advanced 11.0.v1Operating System(s) involved = Mac OS X 10.5.8 Detailed description of the issueThe GetNthRecord function seems to have an upper limit of how many times it can be invoked before it starts returning "?" questionmarks.  I am designing an investing database which incorporates moving averages, and I attempted the simplest one first as a proof-of-concept ... an Exponential Moving Average, which only requires one value from the immediately preceding record for its calculation.  The EMA calculation retrieves its own value from the previous record, multiplies it times a percentage (a weight) and then combines it with another value in its own record.  In my actual database I begin seeing "?" symbols at the 95th record. After tracking down the nature of the problem I was seeing, I put together a small test database to exhibit the problem and that's what I will describe below.  That one starts showing "?" symbols at the 167th record.Exact steps to reproduce the issue1. Create a database and add one "ID" field.  Make it a calculated field with a numerical result, but leave the calculation definition blank for a moment.2. Use Storage Options to set the ID field to "Do Not Store Calculated Result"3. Reenter the ID field definition via Options and enter this equation:      Let( N = Get(RecordNumber); If( N = 1; 1; GetNthRecord ( ID; N-1 ) + 1 ) )4. Set up a layout to display the ID field in List or Table view5. Add 200 records (I used a script)Expected ResultThe ID values should run 1, 2, 3, ... up to the number of records you entered in the table.  The calculation works by "looking back" one record and picking up the previous record's value for ID, then adding one to it (unless it's the first record, in which case it just sets it to 1).  The concept is similar to what you might do in Excel if you wanted a set of running integers starting from a parameterized first number.Actual ResultWhat I see (your mileage may differ) is that the first 166 records in this database do display just as you would expect, but starting with the 167th record I see nothing but a questionmark in each field.Any workarounds that you have foundI have written a script to temporarily recalculate the EMA (by looping over all records) but that's an unacceptable long-term option (requiring minutes to run with only 4000 records, and only calculating one EMA).  I have explored writing a custom function as well as using a relationship to try to get this answer, both without success but I abandoned both ideas once I discovered the GetNthRecord function because it seemed to be the perfect answer for what I need. Bottom LineThe ability to do moving averages of various types is a showstopper for me if I can't find an efficient way of calculating them, so this is a crucial issue for me and this project is stalled unless and until I find a practical solution.  Hopefully what I'm seeing is not a permanent constraint of Filemaker because it's my preferred development platform.