1 2 Previous Next 17 Replies Latest reply on May 5, 2015 8:12 AM by MorkAfur

    Nth Record Bug



      Nth Record Bug


      Apparently, this bug is well known, but I just ran into it.

      Here's how to reproduce it:

      GetNthRecord function bug

      (borrowed the steps below from a posting on macupdate.com)

      1. Open up FileMaker Pro (current 13 version, Windows or Mac).
      2. Create a New Database.
      3. Create a new table.
      4. Create two fields called Start and Field in the table you have created.
      5. Make Start a global number field.
      6. Make Field a calculation field, and enter the following calculation: If ( Get (RecordNumber) > 1; GetNthRecord ( Field ; Get (RecordNumber) - 1 ) + Start; test::Start )
      7. Place Start and Field on the layout.
      8. Choose List View.
      9. Enter the number 1 in Start.
      10. Press the Command N keys to create a new record. Keep pressing until you reach record number 157.

      Then you see the infamous "?" from that point forward.

      This bug apparently has already been reported.

      Anybody have any ideas when it might be fixed?



        • 1. Re: Nth Record Bug

          Filemaker does not release that type of information.  I would suggest finding a work around.   I tested on Windows 8.1 FMPA 13 and the "?" didn't appear until after record 269. 

          • 2. Re: Nth Record Bug

            This isn't so much a bug as a case of the calculation hitting a system resources limit. I will be surprised to say any change in this any time soon.

            This type of calculation "chain" eats up more and more system resources the more links (records) in the chain exist in the current found set. When there are no more resources to support this "chain" an error occurs and you start seeing ? results. Exactly how soon you hit that limit will vary from system to system. FM GO systems will hit this limit much more quickly than FM Pro systems due to the fact that they run on a system with tighter limits to begin with.

            Think of it this way. These are unstored calculation. In order for this field to evaluate for record 1, it merely needs to evaluate once. Add a second record, and you get 3 evaluations, one for record 1, and 2 for record 2 as it has to first re-evaluate record 1 to get the value it uses to compute a value for record 2, For record 3, you now have 6 calculations and this is a geometric growth curve that will outstrip any systems available resources if you have enough records in your chain.

            There are two work arounds:

            One: Keep your getNthRecord calclation, but put it into an auto-enter calculation instead of an unstored calculation field. This avoids hitting this limit, but now the calculation only evaluates when the record is first committed and any time a field in the same record and referenced by this calculation is updated. This might be manageable in some situations if the records are seldom in need of re-evaluation with some scripted support to do the updating.

            Two: Replace start with an added "starting balance" record. Us the running total summary field to compute this value instead of the getNthRecord function in a calculation field.

            • 3. Re: Nth Record Bug

              Cool, thanks,

              - m

              • 4. Re: Nth Record Bug

                Phil, That was my though on the issue as I didn't see the ? until after 269 records.    Your explanation is much more precise.

                • 5. Re: Nth Record Bug

                  Mork Afur:

                  Thank you for your post.

                  I am able to replicate the issue under Mac OS X 10.10.3 (156 records) and Windows 7 (269 records).  I don't see any previous report on this.  Do you know approximately when it was originally reported to FileMaker?

                  Regardless, I have forwarded your post along with my findings to our Development and Testing departments for review.  When more information becomes available, I will post again.

                  FileMaker, Inc.

                  • 6. Re: Nth Record Bug

                    Hi TSGal!

                    I had assumed these bugs were known based on the posting I found here:  http://www.macupdate.com/app/mac/8364/filemaker-pro

                    This poster also had this bug (I copy and pasted his bug report from the macupdate.com site). I haven't tried to reproduce the one below.

                    (Below text copy and pasted directly from http://www.macupdate.com/app/mac/8364/filemaker-pro)

                    The Data Viewer bug
                    Please note that you must have FileMaker Pro Advanced 13.0v9 to see this bug.

                    1. Launch FileMaker Pro Advanced 13.0v9.
                    2. Open any password-protected FileMaker Pro database. Do not enter the Admin password. Any other password (or no password if the database automatically enters a default account username and password) account is fine.
                    3. Since you are technically not able to peek at the tables, fields and the contents of those fields because of your non-admin account, try selecting Tools-->Data Viewer.
                    4. Click the Watch tab.
                    5. Click the "Add Expression "+" button.

                    Viola! You can now look at all the fields in any table. Heck, you might as well not bother putting in an Admin password on the database since technically you already are in the admin account of the database now that you can peek at anything you want. So why not recreate the tables and fields you see in a new database? And with a little common sense and looking at the type of data stored in the fields, you can even figure out how to link the tables to form the relationships. Not hard is it? So yes, it is true. You can reverse engineer any FileMaker Pro database. Even if you are not inclined to steal other people's intellectual property, nothing can stop you from peeking at the contents of every field, so no data can be hidden or made absolutely secure (you will now have to use variable fields and hope no one can figure out the name of the variable fields, which incidentally you can also monitor using the Data Viewer). The data is all exposed for the world to see. Good one FileMaker! So just imagine how easy you can figure out any database created with FileMaker Pro? Very easy. hence the reason why Apple prefers you to use your custom-made databases for personal use or within an organisation. Never attempt to sell the databases commercially (even those you make into Runtime solutions) to consumers where you could potentially compete woth Apple's own OS X free apps (e.g., contacts.app, iTunes.app, etc). Should anyone be surprised from a company like Apple?

                    NOTE: This Data Viewer bug never existed in FileMaker Pro Advanced 10 or earlier.

                    NOTE: FileMaker, Inc. have been notified of these bugs for more than 2 years by several developers. The question is, how long will it take for the company to finally quash these bugs? There is already a bet on at the moment that the company probably never will.

                    Despite this, I still give FileMaker Pro 13.0v9 a generous 3 stars for at least finding the time to make the software more stable after version 13.0v5.

                    • 7. Re: Nth Record Bug

                      Mork Afur:

                      macupdate.com is not a web site owned by FileMaker.

                      Thank you for the information about the Data Viewer.  Our Development and Testing teams are aware of this issue.  I have attached your post to the original report.  When more information becomes available, I will post again.

                      FileMaker, Inc.

                      • 8. Re: Nth Record Bug

                        The limits of GetNthRecord have been discussed here in this forum a number of times previously.

                        I will also point out that GetNthRecord is not alone. Recursive custom functions and recursive calculations can hit this same kind of "resource limit" if they receive input that generates sufficient "loops" in the evaluations needed to produce an answer.

                        • 9. Re: Nth Record Bug


                          Yes, I know macupdate.com is not owned by FileMaker. I was just telling you where I saw the bug reports.


                          - m

                          • 10. Re: Nth Record Bug


                            Perhaps FileMaker needs a different internal implementation for GetNthRecord.

                            Whenever you need to do recursion, especially when the recursive depth can be an issue, a while loop can (usually) be substituted.

                            In my actual programming, I never use recursion when I can use a while loop to do the same thing with no recursion depth limitations. So, FM just possibly needs to change the "behind-the-scenes" implementation for the functions like GetNthRecord since they really shouldn't break.

                            Yes, the Geometric Sequence "explains" it. But, the problem shouldn't happen in the first place.

                            Therefore, I would still call this GetnNthRecord behavior a bug.

                            You might disagree.


                            - m

                            • 11. Re: Nth Record Bug

                              Thanks for sharing the solution. Your links are so useful. I am new here. 

                              • 12. Re: Nth Record Bug

                                A Geometric Sequence (or growth curve from one) is of the form (SUMMATION FROM 1 to n) of A**n, where A is constant and n is a number.

                                So, a geometric sequence, if the GetNthRecord calculation growth represented one, could be something like:

                                2 .. 4...8...16 , .... (for 2 **N) or

                                3...9...27,....  (for 3 **N)


                                In a Geometric Sequence, the division of any two successive terms is constant (the same).


                                From your example progression above, the progression sounds closer to a Fibonacci sequence.


                                Regardless, I hope they implement a better behind-the-scenes function so this issue doesn't happen in the first place.

                                Just a quick follow up.

                                -- m

                                • 13. Re: Nth Record Bug

                                  Yes, I was being sloppy with my terminology, but the fact still holds that using GetNthRecord will outstrip available system resources rapidly in a non linear fashion as a function of the number of records in your found set.

                                  I am not privy to how FIleMaker is designed "under the hood", but given how unstored calculation fields evaluate, I doubt that you are going to see any change here soon.

                                  Whenever you need to do recursion, especially when the recursive depth can be an issue, a while loop can (usually) be substituted.

                                  Not in a custom function and there is a reason for using recursion in computer code in general. It produces very simple algorithms that are often a fraction of the complexity of the looping code equivalents. Instead of rejecting recursion altogether a programmer needs to understand and respect the limits of the method just as he/she would with many other programming tools and features.

                                  • 14. Re: Nth Record Bug

                                    And I forgot to point out that Report an Issue is the appropriate place to report possible bugs. TSGal and other techs regularly monitor posts there while their participation here is pretty hit or miss.

                                    1 2 Previous Next