I have been reading about the random function in filemaker and am trying to determine how to use it to generate random phone numbers. The phone numbers will all be in the same area code, but in different prefixes. There are 21 different prefixes for the area. So I need a way to randomly choose one of the 21 prefixes, and then generate a random 4 digit number as the end of it.

It seems the random function returns a random number between 0 and 1.

I then found this step by step process:

http://sixfriedrice.com/wp/creating-random-numbers-in-filemaker-pro/

The only problems is there are 1111 numbers that are between 0000 and 0999. So this being the case the above function would not return any numbers in the above range with a 0 in front of them.

Anyone have suggestions on how to make a function that would include the 0000-0999 numbers, or will I have to take numbers generated like 10 and add a 00 onto them manually?

Thanks.

Numbers with leading zeros are text. Try:

SerialIncrement ( "0000" ; Int ( Random * 1000 ) )

This will draw a random "number" from the range between "0000" and "0999" - and I believe there are only 1,000 of them.

what about the other 9000 numbers that need to be included and be random?

Did you mean "0000 - 0999" (a zero and three nines) or "0000 - 9999" (four nines) in the original post?

well technically I meant 0000-9999, but the random script I have seen would generate numbers below 1000 with just 3,2, or 1 digits, when a phone number's last portion needs to be 4 digits long.

Ah, that's because Comment answered the question as posted: 0000 - 0999 which is 3 digits..

For 4 digits, use

SerialIncrement ( "0000" ; Int ( Random * 10000 ) )

Thanks guys. Well now I need to figure out how to get it to randomly select prefixes in this area. There are 20 of them, so I figure I can set it to create a random numbe rbetween 1 - 20 and then assign each numebr to a prefix.

You could use the Choose() function, along with a random number between 0 and 19...

Choose(

Int( Random * 19 ) ;

prefix00 ;

prefix01 ;

...

prefix19

)

Ahem. It should be Int( Random * 20 ), I think.

Comment wrote: "Ahem. It should be Int( Random * 20 ), I think."

Are you sure? :

The first result in Choose() is zero, so the argument needs to return 0, while the last result will be 19, which totals 20 values.

Random returns a number between 0 and 1, so ( Random * 19 ) will be between 0 and 19.

Random should never return 1.

Whether Filemaker's Random function CAN return 1 is another issue. In any case, if it does, it's a bug and it would occur EXTREMELY rarely. If you want a uniform distribution, you need to assume that the max value returned by Int ( Random * 19 ) is 18.

Well, FMI's help states that Random returns "a random number between zero and one."

From reading this, if returning one is a bug and highly unlikely, then returning zero should be equally a bug and highly unlikely.

So I just made a quick database that generated ~500,000 random numbers, then did the calculation Int( random * 20 ) and made a summary report to show the distribution... see the attached file.

It appears that the random function never returns zero or one...

0 < Random < 1

However the expression Int( Random * 20 ) returns numbers fairly evenly distributed between zero and 19...

0 <= Int( Random * 20 ) <= 19

I tried to attach the test file but it barfed, was too big. Instead see the pdf of the report...

random19.pdf

The smallest random number it generated was:

0.000000033993273989294317539

and the largest was...

0.9999997247941790901

... so they get *close* to zero and one.

if returning one is a bug and highly unlikely, then returning zero should be equally a bug and highly unlikely.

No. The correct behavior is between 0 (including) and 1 (excluding). It must be so, or you couldn't get uniform distribution even on a Boolean value. See:

http://fmforums.com/forum/showpost.php?post/292093/

I just made a quick database that generated ~500,000 random numbers ... It appears that the random function never returns zero or one

It appears the Random function did not return zero or one in your test - that's all I would conclude from it. Given that Random has a resolution of 20 decimal places or so, you would expect to find 0 and 1 once in each group of 10^20 results, on average (those are the odds for ANY arbitrary result, not just 0 or 1).

Very illuminating Comment, thanks.

I wonder, though, just what's meant by "returning 1".

What if the function returned 0.99999999999999999999 (twenty nines), does that equal 1?

Edited August 8, 200817 yr by Guest

OK, I'm trying to get my head around this...

So does the Random function work by getting a decimal point and putting 20 digits to the right of it? If the digits are 0-9, then the resulting range would be...

0 <= Random < 1

... assuming that 0.9999...9 is defined as being < 1.

This thread reminded me that I forgot to post on the previous random thread by comment. I have added the results from talking to the FMI Engineers about it.

http://fmforums.com/forum/showtopic.php?fid/233/tid/195627

assuming that 0.9999...9 is defined as being < 1.

There's no assumption about it: 0.9999...9 *IS* less than 1 (and therefore does NOT equal 1). For our current purpose the important part is this:

Int ( 0.9999...9 ) = 0;

Int ( 1 ) = 1

So does the Random function work by getting a decimal point and putting 20 digits to the right of it?

I have no idea how the Filemaker Random function works - all I know is that it doesn't work the way it should.

As a side rant:

I am getting tired of having to investigate Filemaker's behavior, as it were some alien creature in a lab. We should be getting this information from FMI. Compare what you know about Filemaker's Random to this (as an example):

http://support.microsoft.com/kb/828795

Even if they fix this, all we'll know is that they told us they fixed it. We'll still know nothing about how the random generator is seeded, and what are the chances of the pseudo-random sequence repeating itself.

And of course, that's just one example out of many. FMI's knowledge base is a sad joke. Most of what we know about Filemaker's behavior comes from testing and the occasional tidbits leaked by engineers at DevCon. I am getting sick of this 'don't you worry your pretty little head about it' attitude. That's MY data going in there, and I want to know what happens to it on its way out.

How would I get the random prefix generator to combine with the random 4 digit generator, and then to compare the numbers with all other random generated phone numbers so there is not a repeat?

It would check the random number it just generated and then compare it to all other records and make sure it is not a duplicate, and if it is create a new random number until it gets one that is unique.

I am using the random number generator to do a telephone survey/poll, so I do not want to call people twice.

Also what if I wanted to create a no call list and have it compare the random generated number to a list of do not call numbers and if it matches one it would kick it back out and create another number.

God I hope I made sense right there.

"I am using the random number generator to do a telephone survey/poll, so I do not want to call people twice."

Sheesh, then why muck around with random numbers.

Create a table with *every* phone number in it. Randomly select a record. Delete it when it's been used.

I wouldn't. That's 200,000 records that do not represent anything.

Yeah that is what I am saying. Right now the only way I have to make sure there are not duplicates is to generate records and then manually check for dupes. How do you generate a ton of records at once? There must be a way to do this.

the only way I have to make sure there are not duplicates is to generate records and then manually check for dupes.

How about generate records and then check for dupes automatically? For example, you could put the generated number into a global field, and define a relationship matching the global with the "real" field. If there is no related record, proceed to create a new record with the newly generated number, otherwise get a new one.