4 Replies Latest reply on May 15, 2011 11:52 PM by LaRetta_1

Selecting a sample of every nth record

Title

Selecting a sample of every nth record

Post

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!

• 1. Re: Selecting a sample of every nth record

There are a few ways to approach this.  If you use the record number as your count, you can't omit all records as you go that are not the every 29th record because the record number depends upon what record number it is in the found count.  Whew, sounds strange so I hope that made sense.

Neither will you want to mark the records.  Marking means that it would be the same for every user so it wouldn't work in multi-user mode because you always want to design so that Users don't trip over each other.

In this instance, I might script it this way (assuming your number global holding how many nth records you want is called gNth):

Set Variable [ \$kount ; Div ( Get ( FoundCount ) ; table::gNth ) ]
Go To Record/Request/Page [ First ]
Loop
Exit Loop If [ \$kount  ≥ Get ( FoundCount ) ]
Omit Multiple [ no dialog ; by calculation ; table::gNth - 1 ]
Go To Record/Request/Page [ next ]
End Loop

In this way there is no need for a calculation - script handles it all and you do not interfer with other Users; neither does it require an additonal relationship (which some might suggest writing the found IDs to global and then GTRR).

Oh.  And it means that you will end up with a found set of every 29th record.  If you wanted to preserve your original found set, just open a new window at the beginning of this script.  After you do your work on these isolated nth-record records, simply close the window and you will be back at your original found set.

• 2. Re: Selecting a sample of every nth record

It just works!  Thank you very much indeed, LaRetta.  I owe you a whole fresh Arctic Char!

• 3. Re: Selecting a sample of every nth record

Erik said, "I owe you a whole fresh Arctic Char!"

I had to look that one up ...

"The arctic char is a member of the Salmonidae family and is similar in taste to its cousins trout and salmon. It also bears resemblance to the salmon, ..."

... not a normal show of thanks in my part of the woods but the thought is most appreciated!!

• 4. Re: Selecting a sample of every nth record

BTW ...

"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."

Since we solved this issue via script instead of calculation it doesn't apply here but I didn't want you to walk away with an idea that a calculation can't be searched just because it can't be stored.  It only means that the search won't be indexed so it won't be as fast.  And it would mean that the calculation couldn't be used in the right (child) side of a relationship key, except for Cartesian Product join which can use unstored for the right side as well.