11 Replies Latest reply on Jun 3, 2010 8:16 AM by comment_1

    GetNthRecord Problem (Bug? Feature?)



      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.

        • 1. Re: GetNthRecord Problem (Bug? Feature?)

          I can't reproduce this bug on a Windows XP, SP3 system. It's either a Mac only bug or something else is a factor here.


          Hopefully, you've already ruled out this possibility:


          The ? will appear if the field is two narrow to properly display the number. If that's the case, clicking or tabbing into the field will enable you to see the actual correct value and that's a quick test to see if that's the problem here. If so, you need to either reformat the number or resize the field to enable it to properly display.

          • 2. Re: GetNthRecord Problem (Bug? Feature?)

            The test database I constructed has no other factors -- just the single field (ID).  The field displaying the ID is about 3 times wider than the number displayed in the first 167 records so is not a factor either.  The last value displayed is 166 in my test database but I can click into 167, 168, 169, and 170 and see the values temporarily until I exit the field, at which point the field reverts to a ?.  But 170 is the last record I can click into the field ... none of the records past that point in the database can be clicked into, though they continue to display a "?" as the visible field content, all the way through the last record defined.


            If I transfer the test database from my iMac G5 over to my MacBook Pro it is slightly different.  The last record value I can see is 174 before the "?" items take over, and I can click into and reveal temporarily 175, 176, and 177.  Past that point I can't click into any of the ID fields in subsequent records.  Both Macs are running the same version of Mac OS X as well as Filemaker Advanced.


            Interestingly, copying the same database to my Windows XP SP 2 virtual machine running on the MacBook Pro works perfectly ... showing the ID field all the way through the 200th record.  But when I ran my script to add another 200 records, it showed the same behavior ... this time stopping at 220 but allowing me to click into 221 and 222 to temporarily show the result.  All records after that point would not let me click into them.


            I've posted the database online at this address if you want to download it to see what I'm talking about:



            This version has 200 records, so if that's not enough to exhibit the problem just invoke the script Create Records to add another 200.

            • 3. Re: GetNthRecord Problem (Bug? Feature?)



              this doesn't explain the bug but solve the problem.


              1) change your global field ( gStep ) to a number field ( Step ) and set its behavior to "Select entire contents of field on entry"
              2) attach to it an OnObjectExit ( or ObjectModify ) script trigger that fires:


              Replace Field Contents [ No dialog ; Test3::Step ; Current contents ]


              3) attach the same script to OnRecordLoad script trigger

              4) change the calculation for ID to STORED with a calc like this:


              N = Get ( RecordNumber );
              N = 1 ; Step ;
              GetNthRecord ( ID ; N - 1 )  + Step

              • 4. Re: GetNthRecord Problem (Bug? Feature?)

                This (IMHO) is not so much a bug as a protection against stack overflow. In order to calculate the result for record N, there must be N-1 calculations performed beforehand. With a large amount of records, this could get out of hand fairly quickly. I would assume the actual number of calculated records  is given by the available RAM - hence inconsistent  results between systems.

                • 5. Re: GetNthRecord Problem (Bug? Feature?)

                  To "Comment" -- I've been thinking  the same thing, that this problem is running into Filemaker's attempt to track something in a stack.  My suspicion is that Filemaker is attempting to protect itself against "circular logic" where "a" depends on "b" which depends on "a" (in this case the ID field setting itself to an earlier incarnation of itself) ... and so it keeps track of however far back a calculation runs to make sure a field never refers to itself.   And I've been thinking the same thing as you, about being limited by available RAM as the explanation for why the results are inconsistent across machine environments.  Unfortunately, numbers in the "low hundreds" for lookback protection is a severe constraint for a database because it is not unusual to be dealing with thousands of records in even simple databases.  However, if you/we are right then we are dealing with a "design constraint" of Filemaker and not a bug.


                  To "Daniele" -- Thank you for the workaround.  I've implemented it in a revised version of the test database I originally posted and it  does work as you said -- with one oddity; after running my Create Records script I have to put a "1" into one (any will do) of the Step fields and enter the record for all the ID fields to light up with values.  I've added records out to 550 and it continues to work.  I'm new to triggers and you've introduced multiple changes to what I started with so it isn't obvious to me why it works, but it does, so now I'll go back and study it to see if I can use that technique in trying to do the moving averages that set me off on this project (if anyone has a direct solution to or suggestion for THAT problem, I'd be very interested).  The revised database with Daniele's solution is here:




                  • 6. Re: GetNthRecord Problem (Bug? Feature?)

                    The field Step can stay into the Header ( and you can trush the gStep )


                    "after running my Create Records script I have to put a "1" into one (any will do) of the Step..."


                    Set the field Step to auto-enter last visited value


                    " it isn't obvious to me why it works"


                    It works because FileMaker have not to recalculate ( and remember ) the value of each ID

                    • 7. Re: GetNthRecord Problem (Bug? Feature?)

                      I don't see circular logic here. However, the numbers are far from being in the "low hundreds". Let's say we are at record 6: in order to calculate the current value, we need to get the value from record 5. Record 5 does not have the value anywhere at hand (that's what "unstored" is all about), so it needs to calculate it. The request goes out to record 4, and so on - and this is repeated record after record, as they are being  rendered.


                      If I am not mistaken, 20,100 calcuations are required in order to show only 200 records (and that's assuming an optimized approach). I am not a programmer, but I would venture a guess that the requests going "backwards" also require that all the higher-level calcs waiting for the result from the previous record need to be "parked" somewhere.

                      • 8. Re: GetNthRecord Problem (Bug? Feature?)

                        To "Comment" ... ok, I'll buy that explanation (that it requires that every record be reevaluated all the way from the n'th record back to record #1 because the value of "ID" is not being stored) and I'll grant that in that case the number of items to be tracked goes way up very fast (geometrically).  Though you might be understating the case ... if I make that assumption I get over 776,000 items that have to be tracked if you go out to the 167th record, if no effort is made to be efficient about how you do it and just brute-force the solution.


                        Perhaps you can then explain something else.  I would be willing to accept the field value being stored if that's what it would take to get around this problem ... but if I change the value of ID so it is stored, it seems to let me do it at the moment but when I exit the field definition and view the layout, then return to the field definition my change of the setting to "Stored" no longer is true and it reverts to "unstored".  Somehow my change is lost between the time I set it, and the time I look at the field definition again.  I'm referring to the original "Test3" database I posted (which failed around the 167th record point), not the revised one that Daniele suggested that produced the correct values of ID out to 550 records.

                        • 9. Re: GetNthRecord Problem (Bug? Feature?)

                          A calculation field referencing a related field or a global field or an unstored calculation field is forced to unstored.


                          I am not sure how you figure 776,000 or geometric progression. I'd think 167 + 166 + 165 + ... + 1.

                          • 10. Re: GetNthRecord Problem (Bug? Feature?)

                            Regarding the number of items to track ... I was taking a worst-case scenario where the calculation of any record did not assume that any other record's calculation had been retained.  If that were true, then in calculating the 4th record you would need to calculate -- in order -- record #1, #2, and #3.  For the 5th record you would need to REcalculate record #1, #2, #3, and #4.  That's a geometric progression.  If Filemaker could assume that record #2 would not change between the time it calculated record #4 and #5 then Filemaker could collapse it to the linear progression you describe but I'm not certain Filemaker would assume that in its optimization of the calculation, given the complexities introduced by the GetNthRecord function.


                            As for the stored/unstored situation with the lookback field ... you're absolutely right.  I had forgotten that when I created this test database I innocently tried to make it more general by including a global field in the increment of the ID value, not  taking into account the impact on the issue of stored vs unstored calculations.  Once I changed the one calculation so it used a fixed increment rather than a reference to a global field I was able to make the calculation stored, as you said.  Thanks for the insight.


                            In the meantime, I'm going to assume that the issue causing me to submit this discussion item has been resolved.  I believe I now understand why the GetNthRecord function seemed to fail (blowing out some kind of internal memory store tracking the complexity of the calculation), and I can now see the path I'll have to take in working out the logic of creating moving averages ... the results will have to be stored one way or another and not left unstored, given the nature of how moving averages have to work and the impact on Filemaker's performance when any kind of lookback is required.  Thanks for everyone's help.

                            • 11. Re: GetNthRecord Problem (Bug? Feature?)


                              cs3developer wrote:

                              I was taking a worst-case scenario where the calculation of any record did not assume that any other record's calculation had been retained.  If that were true, then in calculating the 4th record you would need to calculate -- in order -- record #1, #2, and #3.  For the 5th record you would need to REcalculate record #1, #2, #3, and #4.  That's a geometric progression. 

                              That's exactly the scenario I have assumed - but that's still not geometric progression. For the 5th record, you need to calculate record #1, #2, #3, and  #4 - altogether 5 calculations.