Level: Intermediate, Version: FM 18 or later

Fixed in 18: List + GetNthRecord Bug

Recently I was working in FM 17 and ran into an unexpected bug. My use case was more complex than this, but the following bare bones demo illustrates the problem.

2019-07-14_134233.png

Demo file: List+GetNthRecordBug.zip

Given two tables joined via a standard primary-key-to-foreign-key equijoin relationship…

2019-07-14_131955.png

This works as expected.

2019-07-14_132152.png

You might think you could get rid of the “& ¶ &” and use List() instead, however, try doing so prior to FM 18 and here’s what you get.

2019-07-14_132754

I tested in 15/16/17 and all were as above… but good news, it’s fixed in 18.

2019-07-14_132546.png

Interestingly, attempting to work around the problem by declaring standard function variables (a.k.a. “Let variables”) and then pointing List at the variables doesn’t help… pre-18 that is — the following would work fine in 18.

2019-07-14_133142.png

Finally, I discussed this with several colleagues, and Geoff Gerhard pointed out that you can work around the bug by using $vars instead of vars…

2019-07-14_134018.png

…just remember to clear the $vars out when you’re done as per the second Let statement in the above example.

9 thoughts on “Fixed in 18: List + GetNthRecord Bug”

  1. I’m not understanding why you used getnthrecord(), and why you didn’t use

    list ( child::name )

    instead. It would have done what you require?

    1. Hi Peter,

      Because my actual use case was more complicated than what I showed in the demo. My point was not to discuss the complexity of what I was trying to do, but simply to draw attention to the bug, and the fact that it has been fixed.

      Kevin

    2. I think Peter may be suggesting something like this, that works in both versions:

      let(
      lyst = list( child::name );

      List(
      getvalue ( lyst ; 1 );
      getvalue ( lyst ; 2 );
      getvalue ( lyst ; 3 )
      )

      )

  2. Hi Kevin,

    thanks for pointing this out! … We all get stung by this one at some point, so it is very interesting to know that it is fixed in FM18.

    The fact that setting calculation-variables still suffers the same problem in pre-18 is new to me … and makes me even more inquisitive than before to know what the technical/internal implementation is which causes this bug…

    It seems to indicate that FileMaker uses ‘lazy evaluation’ – only evaluating expressions when they are really ‘used’ for the first time – which would cause the GetNthRecord functions to still be evaluated *within* the List function whether they are literally within the function or ‘pointed to’ via the calc-vars.

    Warm regards across the ether!

    MrWatson

  3. Thanks Kevin. I never knew there was a workaround or even that it was a bug. I assumed that GetNthRecord just didn’t work with child records.

  4. Here’s some more info about this bug. It is not completely gone, unfortunately.

    Prior to version 18, it affected the GetNthRecord() function AND the Last() function.

    It occurs any time either of those functions are directly inserted into any aggregate function without further transformation (not just List(), but also Min(), Max(), etc.

    In 18, the bug in GetNthRecord was fixed but Last() still has the identical problem.

    Any operation performed on the result of Last() will fix the problem, such as appending an empty string or wrapping in GetAsNumber() (or whatever the appropriate data type is) or even using TextStyleAdd()!

    More info & sample file:
    https://community.filemaker.com/en/s/question/0D50H00006ezMGcSAM/last-does-not-work-inside-an-aggregate-function

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.