On Dec 29, 2007, at 11:10 AM, Jens Selvig wrote:
Which version of FMP are you using?
Sounds pretty straight forward. My initial thoughts are below.
On Dec 29, 2007, at 8:38 AM, Andrew Kappy wrote:
1. I need to generate 750,000 alpha-numerics, 6 characters long,
excluding certain letters. I have no problem generating as many
random numbers as I want, using the RANDOM function, but getting
down to 6 alpha-numerics without duplicates is the problem. I've
generated 1.5 million random numbers and translated them to
integers. I've also built a numbered table of included alpha
characters. I've tried various calculations to grab 4 numbers from
the larger random number and combine them with two of the letters
via calculations. The result is way too many duplicates.
I'd make a custom function combining the use of the RND function
and the choose function to accomplish this. Build your random
string one character at a time. Probably create a global field to
insert each newly created random string with a relationship back to
your string field. If there is a match throw away the global and
get another random string. If there is not a match then create a
new record and put the global in. Repeat until you hit your 750,000
count.
See, this is a lot funner than the morning crossword.
I made a text field; theKey, and a relationship; theKey=theKey. Then
I made a calc that is Count(theKey2::theKey). A 1 in that calc says
theKey is unique.
The custom function is just as you said:
MakeKey(key)
If(Length ( key ) ≥ 6 ; key;
MakeKey(key & Choose(Round ( Random * 35.5 ; 0 );
"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";
"S";"T";"U";"V";"W";"X";"Y";"Z";"1";"2";"3";"4";"5";"6";"7";"8";"9";"0")
))
You have to call it with a parameter of ""; then it calls itself
recursively with the string it's building.
The script to generate unique records:
Freeze Window
Set Variable [ $x; Value:1 ]
Loop
New Record/Request
Loop
Set Field [ alphanums::theKey; MakeKey ( "" ) ]
Commit Records/Requests[ Skip data entry validation; No dialog ]
Exit Loop If [ alphanums::KeyValueCount = 1 ]
End Loop
Set Variable [ $x; Value:$x + 1 ]
Exit Loop If [ $x > 750000 ]
End Loop
This made almost 350,000 records in 10 minutes on my 2.33 mac laptop
thanks Jens,
geoff
