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.
Demo file: List+GetNthRecordBug.zip
Given two tables joined via a standard primary-key-to-foreign-key equijoin relationship…
This works as expected.
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.
I tested in 15/16/17 and all were as above… but good news, it’s fixed in 18.
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.
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…
…just remember to clear the $vars out when you’re done as per the second Let statement in the above example.
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?
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
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 )
)
)
I wasn’t seeking solutions; just documenting a bug. There are many ways to skin this cat.
Yay. Thank you for posting this!
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
Hi Russell, your “lazy evaluation” interpretation makes sense. Thanks.
Well here we are again …
So the List( GetNthRecord ( Related::Field ; 1 ) ; … ) problem was fixed in 18 ….
…but that broke the GetFieldname( GetNthRecord ( Related::Field ; 1 ) ) function that we use to output XML tags
https://community.claris.com/en/s/question/0D53w00005C0P1rCAF/getfieldname-getnthrecord-field-i-breaks-in-fm18
Happy ( fixing bugs – making bugs ) ^ infinity
P.S. If you put a cat and a swing in a black box with some radioactive decay … is the radioactive decay swinging the cat or the cat swinging the radioactive decay … (or BOTH … until you look?)
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.
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