Selecting a sample of every nth record
Lets say I have a table with about 1,000 records. I would like FileMaker to give me a sample of records, i.e., a found set of every 29th record from this table. I can do this in a series of steps using Excel, but I'm trying to do the same in FileMaker.
I created a calculation field called "sample", result as number:
Mod ( Get ( RecordNumber ) ; 29 )
When viewing all the records in table view, sorted by date, the 'sample' column shows a sequence of values from 0 through 28 (0, 1, 2, 3…28)… good. This field tells me that every record with a value "0" in the 'sample' column represents every 29th record in the population of records. So, of the 1,000 records in the population, I'll get a sample of about 34 records (i.e, 1,000 / 29 = 34).
When I do the above exercise in Excel, I first have to copy the 'sample' column and Paste, Values, so that the results in the 'sample' column will remain static. I then do a filter on the 'sample' column of all rows that have "0" as the value in the 'sample' column. I'm left with a list of rows giving me every 29th record. I'm trying to duplicate this in FileMaker.
How do I tell FileMaker to give me a found set of every 29th record? I would need to make the results of the 'sample' calculation field static so I can do a Find on values equal to "0". If I don't make the values static first, the found set gives undesired results.
Any ideas? Bye the way, I actually have the "29" above set to a global field in another layout. So, setting the "sample" calculation field as a stored field will not work, according to FM.