A user on another forum asked about how to create a random list of integers within the range of 1 to "N" .

One way of doing this is to create the following fields:

a "constant" field (indexed calculation of type Number with the value of 1)

an indexed number field, "random number"

a global number field for the user's "N" input ("g_desired record count")

a global number field to temporarily hold the random integer ("gn1")

and one relationship:

"gn1" on the left side, "random number" on the right side

The script then looks like this:

New Record/Request

Set Field [ random number, Int(Random* g_desired record count + 1) ]

Loop

Exit Loop If [ Status(CurrentRecordCount) = g_desired record count ]

New Record/Request

Loop

Set Field [ gn1, Int(Random* g_desired record count + 1) ]

Exit Loop If [ not IsValid(self gn1 | random number::constant) and gn1 <> 0 ]

End Loop

Set Field [ random number, gn1 ]

End Loop

The inner loop tests if the current value of gn1 has already been assigned or is "0" (zero), and will continue looping until it produces a number that has not been assigned and is not "0" (zero).

The script takes about 1/2 second to create 25 records, and about 7 seconds to create 1,000 records on a Powerbook G4-667, OS X.2.6 and FM 6.0.4

A demo file is attached.

Note: I edited the demo file to include Bob Weaver's solution (see the next post), and replaced the original attachment. Kudos, Bob!

Random Numbered Records.zip

The following method is quicker because you don't have a loop within a loop:

# I'm assuming that you are starting with no records.

# Otherwise include the following 2 steps:

Show all records

Show omitted ; or Delete all records

# Main routine starts here

Loop

Hi Bob ...

Excellent solution. Much more efficient than mine.

Thanks!

A similar question: I am developing a database solution for a membership based organization - they want to create a unique, random 7 digit membership number for each member. I am a novice at this...

Method 1:

You need to create a random 7 digit number and then check to make sure it doesn't exist. If it does then repeat the process until you get a unique one.

New Record

Loop