AnsweredAssumed Answered

Selecting a sample of every nth record

Question asked by eibcga on May 15, 2011
Latest reply on May 15, 2011 by LaRetta_1


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.

Thank you!