6 Replies Latest reply on Apr 24, 2012 12:35 PM by Dillik

    GetNthRecord Unreliable in custom function

    BruceR

      Summary

      GetNthRecord Unreliable in custom function

      Product

      FileMaker Pro

      Version

      11

      Operating system version

      OSX 10.6.6

      Description of the issue

      Custom function using GetNthRecord produces unreliable results. Where it should return the actual value of the Nth record, it instead returns the value from the current record.

      See http://www.briandunning.com/cf/1275 for basic concept of custom function and its purpose.

      Note that I rewrote the function as shown below and it is my version which we are discussing. Note also that I changed the function name slightly: ListFound_Conditional

      Case( start <= Get(FoundCount)  ;
      Let([
      thisvalue = Case(  GetNthRecord ( filterField; start )  = filterItem ;  "" & GetNthRecord ( field  ; start ) );
      next = ListFound_Conditional ( Field ; start + 1; filterField; filterItem ) ];

      List(  thisValue; next )
      )
      )

      Steps to reproduce the problem

      Use function defined as shown below. Note absence of empty string before second getNth.

      Case( start <= Get(FoundCount)  ;
      Let([
      thisvalue = Case(  GetNthRecord ( filterField; start )  = filterItem ;  GetNthRecord ( field  ; start ) );
      next = ListFound_Conditional ( Field ; start + 1; filterField; filterItem ) ];

      List(  thisValue; next )
      )
      )

      Expected result

      Given a record set and params passed to function that should return:

      1
      2
      3
      4

      Actual result

      If you are on a record where the value of the specified field of the current record is 57, the result will be:

      57
      57
      57
      57

      Exact text of any error message(s) that appear

      No error message

      Configuration information

      FileMaker 11.0v2, OSX 10.6.6

      Workaround

      concatenate empty string before getNthRecord to ensure it references correct record.

        • 1. Re: GetNthRecord Unreliable in custom function
          BruceR

          I see that the 11.0v3 updater does not solve this problem.

          GetNthRecord does not calculate reliably; whether in a custom function or not.

          For instance try this script with and without the empty-string concatenation. And of course; with fields that are valid for your data.

          Set Variable [ $$r; Value:"" ]

          Set Variable [ $k; Value:1 ]

          Loop

          Exit Loop If [ $k > Get(FoundCount) ]

          Set Variable [ $$r; Value:List( $$r; "" & GetNthRecord( Zips::city; $k)) ]

          Set Variable [ $k; Value:1 + $k ]

          End Loop

          • 2. Re: GetNthRecord Unreliable in custom function

            BruceR:

            Thank you for posting. I do apologize for the late response.

            I implemented the last script that you posted in a database with 10 records. Regardless of what record I was on, $$r always contained a list of the data within that field in the correct order.

            Do you have a sample file where this issue reliably occurs?

            TSBear

            FileMaker, Inc.

            • 3. Re: GetNthRecord Unreliable in custom function
              BruceR

              I  am not sure what you are saying or if you tried the script as I suggested it.

              So let's go over this again: the instructions are to create TWO scripts; or to create a script and run it in one configuration; and edit the script; and run it again.

              One time use this statement:

              Set Variable [ $$r; Value:List( $$r; "" & GetNthRecord( Zips::city; $k)) ]

              In the alternative configuration, use this statement (note absence of concatenated empty value)

              Set Variable [ $$r; Value:List( $$r; GetNthRecord( Zips::city; $k)) ]

              Yes I can provide an example file.

              • 5. Re: GetNthRecord Unreliable in custom function

                BruceR:

                My apologies, it looks like I had initially misunderstood you. With the help of your sample file and follow up post (thank you!), the issue is much more clear and I was able to reproduce the behavior you're experiencing. I've forwarded your post to our Development and Quality Assurance (testing) departments for further investigation.

                TSBear

                FileMaker, Inc.

                • 6. Re: GetNthRecord Unreliable in custom function
                  Dillik

                  BruceR: Thanks very much for posting your workaround, as this issue was frustrating me before I found your post.  Just concatenating the empty value before the GetNthRecord function is enough to make it reference the right record.  Crazy!

                  Anyway, I can confirm that this bug remains in FMP 12.  Also, FYI, it's not just in custom functions.  A simple unstored calculation fails with the following formula:

                   
                  List ( 
                  GetNthRecord ( myfield; 1 );
                  GetNthRecord ( myfield; 2 )
                  )
                   
                  ...but concatenating fixes it.
                   
                  List ( 
                  "" & GetNthRecord ( myfield; 1 );
                  "" & GetNthRecord ( myfield; 2 )
                  )