Random integers within a range

[Jim McKee]

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

[BobWeaver]

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

[Jim McKee]

Hi Bob ...

Excellent solution. Much more efficient than mine.

Thanks!

[greatusahero]

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

[BobWeaver]

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