FM Function of the Week: GetNthRecord

In FileMaker the current record, that is the record you’re currently viewing at any given moment, is the center of the universe. Very nearly everything you can see or derive is in that record or somehow related to it. This is good and sensible. After all, how can you know where to look for something if you don’t know where you are? But it’s somewhat ironic that given the ease with which we can get related data from 2 or 3 tables away, pulling info from a neighboring record in the same table isn’t quite as simple. Indeed, prior to FileMaker version 8 it couldn’t be done without a self-join relationship. While you might not find the need coming up every week, knowing how to access neighboring data it a worthwhile skill to possess. GetNthRecord ( fieldName ; recordNumber ) 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: sample data 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 or creation order. OK got that? So now 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”

GetNthRecord works with related values too. As long as you enter the fully qualified (tableName::fieldName) field names, the function will pull from a specific related record. Use caution when accessing related records, though. GetNthRecord pulls records based on their related order and ignores sort order on portals.

two portals with different sort orders

Given the portals above, GetNthRecord ( relatedTable::numberField ; 3 ) will always return the number 5. If you’ve got to have a sort order to get your desired result, you can set set it in the relationship definition and GetNthRecord will respect that.

Ultimately, GetNthRecord’s utility is pretty narrow; you can get the same results with scripting and relational techniques. But a few times a year, I find that GetNthRecord does exactly what I need, more succinctly and efficiently than the alternate approaches.

This entry was posted in FileMaker 13, Function of the Week. Bookmark the permalink.