What is GetNthRecord and how do you use it?

Stuart Gripman’s post on GetNthRecord explains the function better than any other I’ve ever read, and the examples are clear cut and straight forward:

This is exactly as straight forward as it seems. Replace fieldName with the name of a field in the local table and recordNumber with any number (within the total number of records in the found set) and you’ll get the value of that field. Consider this table:Regardless of which record is currently active, GetNthRecord ( someText ; 4 ) will always return “Olema”. If the records were sorted alphabetically by the someText field, GetNthRecord ( someText ; 4 ) would now return “Princeton”. Easy enough, but if the record number that you want is a shifting target, you really need to be able to calculate that number relative to your current position. We can do that with the Get ( RecordNumber ) function and some simple math. Get ( RecordNumber ) merely returns the number of the active record in the found set. In the example above, it happens to be record 3. It’s important to be aware that every time you get a new found set or change your sort order, any given record’s number is subject to change. Get ( RecordNumber ) has nothing to do with serial numbers, creation order. OK got that? So we can figure out neighboring values. In the table above: GetNthRecord ( someText ; Get ( RecordNumber ) – 2 )will return “Akron” GetNthRecord ( someText ; Get ( RecordNumber ) + 3 ) will return “Queenstown”

I just used GetNthRecord yesterday to grab data from a related file (hat tip to Robert Schaub, a developer with whom I work).  In that instance, I wanted to pull data (amount, date, and a text field) from the last related record (the last donation record for a donor) across a relationship in a script.  The results were being stored in a variable and later displayed in a virtual table report.  Here’s one of the actual commands:

GetNthRecord ( APV_don::Amount; count(APV_don::MemID) )

To locate the last record, we used the Count function to count the total number of related foreign keys, which will always return the number of the last record.  Note also the unusual naming convention for the foreign key, memID.  We found it in a 15 + year old FileMaker project we are updating that was developed prior to FileMaker 7.  Things sure have changed a lot in that time.

One last thing:  GetNthRecord is very helpful when marking or eliminating duplicates.  Sort the records by a given field, such as the phone number, and use the GetNthRecord function to look back at the previous record’s phone number to compare.  If it is a match, you can take whatever action is appropriate (delete, mark as dupe, etc.).

More…FileMaker Function of the Week: GetNthRecord.