12 Replies Latest reply on May 16, 2017 1:15 PM by philmodjunk

    Understanding GetNthRecord


      I want to understand how to use List() and GetNthRecord() with related records to access data from fields two tables down the child relationship.  It seems that nothing seems to make sense using GetNthRecord with these.




      Flights -< FlightLegs -< AirportTerminal_ArrivalAirport


      The only way that I can use GetNthRecord () or List()  to display the related records properly is by creating a calculation field in the first child table "FlightLegs" and then using these functions on the calculation field instead.


      Why can't I access the records from the grandchild table directly with these functions?

        • 1. Re: Understanding GetNthRecord

          it is implemented that way - it requires you to rethink your design - there might be performance reasons and also implementation constraints ..


          some  behavior you need to ask FMI why it is as it is. Hope someone underthehood savvy can answer.


          The getNthRecord is very powerful it allows if used in Custom Functions call by reference so you can pass a field name as a parameter.


          Works super well for child tables. So I understand your wish for grand-child tables.

          • 2. Re: Understanding GetNthRecord

            Yes I agree it's very powerful


            I find it cumbersome to have to create a calculation field in the child table to achieve this.  I'd like to be able to calculate this data from the uppermost table only when I want it to calculate.  By creating a calculation field in the child table it will then have to calculate this everytime a record is loaded (I think)  That extra processing seems unnecessary.


            I wish that there was native function in filemaker that did this.  Something like


            GetNthGrandChildRecord ( ChildTable ; RecordNumber ;  GrandChildTable ;  GrandChildRecordNumber)

            • 3. Re: Understanding GetNthRecord

              I'm sure there's a way to build a custom function for this but I have no idea where to start.

              • 4. Re: Understanding GetNthRecord

                I figured out another way to do this using a script and putting the data in a Global variable with repetitions.  Not sure if a custom function would be faster or possible.


                Go To Layout [FlightLegs]

                Set Variable [$Count ; 1]


                Set Variable[$FBODepartureAirports[$Count]; GetNthRecord[FBODepartureAirport; $Count]

                Set Variable[$FBOArrivalAirports[$Count]; GetNthRecord[FBODepartureAirport; $Count]]

                Set Variable [$Count; $Count+1]

                Go To Record[next; exit after last]

                End Loop

                Go To Layout [original]

                • 5. Re: Understanding GetNthRecord

                  What are you actually trying to accomplish?

                  • 6. Re: Understanding GetNthRecord

                    You can see how it works making portal of grandchild table in parent layout. It lists up "related records" in grandchild.

                    Most difference than which you want, it gets only once for each record of grandchild table, even though many of child records relate to one grandchild record.

                    • 7. Re: Understanding GetNthRecord

                      I am interested in being able to access data from from the grandchild table directly from the Parent table without having to go to the child table to pull the data.  I plan on scaling my solution and I'm trying to institute best practices so that I don't see performance hits as the database size increases.


                      As I understand going to another layout will in turn cause Filemaker to load the data from all of the fields on that layout and trigger all associated calculations.  Stepping through each record will intern add to that.    Accessing the child data through GetNthRecord avoids that by only pulling the targeted data (as far as I'm aware).  

                      I know that I can create a separate layout that has just these fields on it to lighten the load but I'd rather not go through all of that if possible.


                      I want to target data in the grandchild table by specifying which record in the child to pull from. 


                      In my solution I need to send an email to a client that lists the details for their flight.


                      Flights --< FlightLegs --<  AirportTerminals (That they arrive from and arrive into)


                      The AirportTerminal data  is in a Grandchild related table.

                      • 8. Re: Understanding GetNthRecord

                        I think the List function or ExecuteSQL should do the job for you.

                        • 9. Re: Understanding GetNthRecord

                          List()  works the same way that GetNthRecord() does in this circumstance i.e. pulling from the Parent table.


                          ExecutiveSQL is a good Idea.  I'll need to brush on on my SQL commands though.

                          • 10. Re: Understanding GetNthRecord

                            The List function can pull related table data. For example:




                            This formula might return:





                            • 11. Re: Understanding GetNthRecord

                              The List function can pull related table data. For example:

                              Yes but I'm looking to pull data from the Child's Child FROM the Parent.  In this capacity the List() function will pull the records from every record in the child table.  I want to be able to specify which record in the Child table to pull the Grandchild data from. 

                              • 12. Re: Understanding GetNthRecord

                                I want to be able to specify which record in the Child table to pull the Grandchild data from.


                                Since you do not have a relationship specific to  the "grandchildren" for just one "child", functions like list and getNthrecord that rely solely on the relationship to control what records are accessible, cannot work for what you want. ExecuteSQL gets around that as you can specify the child in the query--you are building the needed relationship and criteria inside the SQL query itself.


                                But you can also set up a relationship that matches to the grandchildren for a specific child to use with functions such as List and GetNthRecord as well. We did it that way long before ExecuteSQL was possible.


                                You add a field--often a global field, to parent and set up a relationship between it and the _fkChildID field in grandchildren. Then you put a button inside the portal to Children that sets this field to the __pkChildID field of the current Child record. You've now selected a specific child record and set up a relational link to all its grand children in a single mouse click.