Random Unique number within a defined range

[yafreax]

Hello all,

long time no post for me. . . either means i got it all figured out or i just haven't done anything real challenging in a while. . . probably the latter.. .. ..

anyway, i need unique numbers randomized between the values 50,000 and 75,000. This will be in a new field, the records have already been created, so i guess i'll actually have to run a script or do like an import into a clone that calculates as it imports. Alright, any suggestions?

thanks.

Jeremy

[tlsparker]

Create a number field (could also be a text field depending on what you are using it for). Use the Replace command and replace with calculated result (50000 + 25000*Random). Once you have updated your existing records, set the field options to auto-enter the same calculated value upon creation of a new record.

Hope this helps!

Tom

[email protected]

http://www.tgparker.com/filemaker

[yafreax]

the only question is will it gaurantee it to be unique in this way? that's a pretty important issue. . .

thanks though.

jeremy

[Steven H. Blackwell]

No.

The only way in FMP to guarantee the uniqueness of the number is to use the auto-enter serial number.

If a range is specified, e.g. 2000 to 10,000 and there are more than 8000 records, there will be a duplicate.

The multiplication method is a clever idea, but if the number is to be used for a primary key, and if that number is the RESULT of a calculation, it can't be longer than 15 digits.

HTH

Old Advance Man

[tlsparker]

Good point about uniqueness. You could, however, set the field to the value 50000+25000*Random as part of a "Create New Record" Script, then create a loop to keep setting the field until you do get a unique value. The script might look something like this:

Create New Record

Set Field "RandomSerial" = (50000+25000*Random)

Loop

If(Count(SelfRelationship::Random)=1)

Exit Script

Else

Set Field "RandomSerial" = (50000+25000*Random)

Loop

[BobWeaver]

One way to ensure uniqueness of a random set of numbers without skewing the randomness is to create a separate file with 25000 records and 2 fields: a number field that contains the set of numbers 50000 to 75000 in sequence, and a sort field that contains a random number (range doesn't matter). Once the file is set up, sort on the random number field. This will randomize the number field. Then, when you need a new unique random number, Just take the number from the first record in the sorted set. Then, omit that record. Next time you need a random number, take the first number in the sorted set and then omit that record. And so on.

It's the Filemaker version of dealing from a shuffled deck of cards.

[ January 16, 2002: Message edited by: BobWeaver ]

[danjacoby]

One rather important addendum to Tom Parker's solution -- round off the calculation (use the "Round" function! Otherwise, you'll have a number with a bunch of decimal places; even if you format the field in the layout to have zero decimal places, the self-relationship check still won't work unless the numbers are exactly the same. Rounding off the calc will make things work properly.