random records

[wyowater]

I need to select groups of 37 random records without repeating records. The database is about 900 records. Is there any way to do this?

[stanley]

Wyowater:

Welcome to the Forums.

A simple method for this would be to loop through the records and set a numeric field to random, then sort the records by that randomized field & take the first 37 records. The script step to randomize is:

Set Field [table::field; Random]

-Stanley

[wyowater]

Thank you for the quick response. I will do it and let you know how I did. It's my first try at anything besides alphabetising in FM

[CyborgSam]

Attached is a FM5 example of another method. This method's script only loops for as many random records as you want. It omits a record in each iteration and then does a Show Omitted after the loop completes.

Purists could argue that this algorithm may not be quite as random as Stanley's. Years ago I knew the theory of pseudo-random number generators, my experience tells me that any non-randomness will be negligible.

RandomRecs.zip

[stanley]

Sam:

That's a nifty solution, especially as it is much faster than randomizing the entire table. Also, I think it may actually be more random than what I posted, as FMP's random number generator can sometimes produce sequential numbers, and re-randomizing each time through the loop would negate that behavior.

-Stanley

[CyborgSam]

Stanley->

I also made a loop that is more random and guarantees no duplicates. It doesn't loop through all records, but the Replace Contents step does. So it's not fast...

Show All Records

Replace Contents [Flag, 0]

Set Field [NumFlagged, 0]

Set Field [gTotalNumRecords, Status(TotalRecordCount)]

Loop

Set Field [gLoopCounter, 0]

Loop

Set Field [gRandomRecNumber, 1 + Round( Random gTotalNumRecords - 1 ))]

Go To Record [gRandomRecNumber]

Exit Loop If [not Flag]

Set Field [gLoopCounter, gLoopCounter + 1]

If ( gLoopCounter > gTotalNumRecords )

Do something to tell the user it blew up...

Exit Script

End If

End Loop

Set Field [Flag, 1]

Set Field [NumFlagged, NumFlagged + 1]

Exit Loop If [NumFlagged = 37]

End Loop

# A find can be performed to make the found set the random, or a relationship & portal can show them

Enter Find Mode []

Set Field [Flag, 1]

Perform Find

[Raybaudi]

Hi to all friends

I think that this isn't the right place to post this question, expecially because wyowater owns FM5 !

[CyborgSam]

Agreed, but wyowater is new to the forums and probably doesn't know the FileMaker definition of Custom Functions.

wyowater: FileMaker Developer (Advanced in 8) allows the developer to create Custom Functions that will appear in the function list in calculations..

BTW: is wyowater short for Wyoming Water?

[wyowater]

Yes - The Bighorn River in Wyoming. It flows north and eventually into the Missouri River. The Bighorn River from the mouth of the Canyon to Thermopolis is some of the best fly fishing in the country - but don't tell anyone; it's a secret.

[wyowater]

Also, thank you for the information. I'll try this tonight or tomorrow morning. I can't believe the GREAT help!!

[wyowater]

I need some help with your Post#177912

In response to stanley.

In the second line - are "Contents" and "Flag" fields?

In the eighth line - I can't specify the last part ,1 + Round (Random gTotalNumRecords - 1 , 0 )

No matter what I put after the 0, it errors. I have tried ) and )) and 0] and ))]. The error is: A number, Text Constant, field name, or"(" is expected here. I also tried it the way you typed it with the same results.

[CyborgSam]

wyowater->

"Flag" is a global number field. "NumFlagged", "gTotalNumRecords", "gLoopCounter", and "gRandomRecNumber" are also global number fields.

In the Replace Contents step, the Specified field is Flag and Specify... is Replace with calculated result, wtih 0 being the result entered into the dialog.

Oops, I made a couple of typos... The formula that goes in the script step dialog is:

1 + Round( Random* ( Status(CurrentFoundCount) - 1 ), 0 )