Creating Random Numbers in FileMaker : SFR FileMaker Blog

Creating Random Numbers in FileMaker

A better random function

Here’s the custom function that can calculate a random number in any range you want. You just pass in the smallest number you’re willing to accept, and the largest.

Name: RandomNumber
Parameters: Minimum, Maximum
Formula: 
  Let(
    Num = Int ( Random * ( Maximum - Minimum + 1 ) ) + Minimum;
    If( Num =< Maximum and Num >= Minimum ; Num ; RandomNumber( Minimum, Maximum ) )
  )

Here are some example uses:

/* roll the dice */
RandomNumber(1, 6)

/* a random letter */
Middle("abcdefghijklmnopqrstuvwxyz", RandomNumber(1,26), 1)

If you are one of those trusting souls or are in some kind of hurry, feel free to take that snipet of code, paste it into your custom function, and run.

Why so complicated?

For all those who stayed, here is a little more in-depth look at the FileMaker random number creation process. Let’s say we want our number to be from 5 to 15. Well, to produce this number we are going to require a little bit of math. The math can be broken down to the following steps:

calculate total number of values we want to produce
multiply by Random
hack off the integer value of the result
add the result to the lowest number in our range
determine if we we have a number within our range.

Here is the formula for calculating the range:

Maximum = highest number
Minimum = lowest number
( Maximum - Minimum ) + 1
ex. ( 15 - 5 ) + 1

I know, I know… You don’t really need to include the parenthesis but I like to because it makes it a little more obvious what exactly your trying to do. Why does this function add one? Doesn’t that make the range 11 instead of 10?? Well yea, and that’s actually how many different values we want to produce… It confused me a little at first but after you count them you see why:

5,6,7,8,9,10,11,12,13,14,15

Now that we have our total number of values the rest is extremely simple. Just multiply by Random like so: Random * ( ( 15 - 5 ) + 1 ). So we’re multiplying the number 11 by our random decimal and the result is a number between 0 and 10 with a whole ton of extra decimal places which we really couldn’t care less about.

To get rid of the decimal places, we use another number function called Int(), which just chops off all the decimal points from our number. Continuing with our example you have Int ( Random * ( ( 15 - 5 ) + 1 ) ) or generically:

Int ( Random * ( ( Maximum - Minimum ) + 1 ) )

Phew… we are almost there. We now have integers from 0 to 10, and we just need to get back to our range 5-15. To do this we just add the minimum value to the our newly chopped off integer — in this case 5 — and we get: Int ( Random * ( ( 15 - 5 ) + 1 ) ) + 5.

That formula looks just about right. But we’re not quite there. The next step seems a little strange but trust me, it’s necessary. Random produces a value from zero to one right? Well, our function handles all values except one. If you actually get a 1 back, the function will result in 16 (try it yourself). The odds of getting exactly 1 from the Random function are approximately 1 in 100,000,000,000,000,000,000. Suffice it to say… unlikely. But we at Six Fried Rice like to be perfect, so we check to make sure this unlikely occurrence did not, in fact, occur. If the result of our calculation is outside the range, we pull a little recursion and go through the whole rigmarole again.

Let(
   Num = Int ( Random * ( ( 15 - 5 ) + 1 ) ) + 5;
   If( Num =< 15 and Num >= 5 ; Num ; RandomNumber( 5, 10 ) )
)

Note: You may be thinking, “Wait! That might loop forever.” Well, if you think getting a 1 from Random is unlikely, imagine getting it twice in a row. This function will loop until Random returns something other than 1. In almost every case, that means it won’t loop at all. On the off chance it does, though, it will certainly get a better result the second time through.

That, my friend, is the exact calculation for creating a random number from 5 up to and including 15. And at the top of this post, you see it generalized as a custom function.

Trackback URL

10 Comments

David Head

7-16-2007

I don’t believe that the Random function will ever return zero or one. This is common for all random number generators which, by the way, are not really random at all.

The FileMaker Help says the Random function ‘Returns a random number between zero and one’ which is confusing but means ‘not including zero or one’.
Wenmeekers

7-16-2007

…and the FileMaker Knowledge Base says:

The “Random” function returns “a random number between zero and one” including “zero and one”.

Answer ID : 5464

So it could be that sooner or later there will be a zero or one.
Jesse Antunes

7-16-2007

@David - I thought the exact same thing as you… and I originally built my function accordingly, but then I emailed the guys who wrote the Random function for FileMaker and was told exactly what Wenmeekers confirmed. Random does indeed include zero and 1. It’s REALLY annoying because it adds an extra step to our calculation. If your are ok with your random number being wrong 1 in 100,000,000,000,000,000,000…. then don’t worry about it ;-). Quite frankly that margin of error seems acceptable.

@Wenmeekers - Thanks for clarifying that… I always forget about the Knowledge Base!
Geoff Coffey

7-16-2007

A few clarifications: The rarity of zero is pretty irrelevant since this technique uses the Int function. If your range is 10, for example, then any result from Random between 0.0 and 0.09999999999999999999 will produce the same final output. It is just as likely as any other number.

It is the rarity of one that causes a problem (again because of the use of Int — if we used Ceil instead, we’d have exactly the opposite problem). If you let a 1 result from Random yield your top value, you will almost never get that value. It will be much more unlikely than all the other values, making for a lousy random number generator. So we had to let 1 produce a bogus (ie: out of range) result, check for it, and redo as needed.

You could argue this is overkill since the likelihood of a 1 is so small (it really is absurdly small). But since this is a custom function you write once and tuck away for easy use later, it may as well be as perfect as possible. And it sure was fun to figure out
David Head

7-16-2007

Well that makes the Random function in FileMaker different to every other random function! That’s a bit strange. Oh well thanks for the clarification and information straight from the horse’s mouth.
J Wenmeekers

7-16-2007

Well…you have to thank my 80+ year old father.
As engineer he’s constantly checking my coding (and still hit me on the head when there’s something wrong :-), according to him.

It was his question (…and what if the random is zero (slap), and don’t tell me it will never happen, code around it or your (* 10) will be zero too !(hit/slap)) that made me check every possible source.
(I still not always believe the old man…I better should….)

It seems that some RNG’s include 0 but exclude 1, while others include or exclude both.
And Filemaker includes both….

Meanwhile we keep on FileMaking.
O Homan

6-5-2008

How about some more complicated problem then: how do I generate (let’s say) 10 or 20 random numbers between Minimum and (again, say) Maximum, with the restriction that each random number occurs only once? Or in other words, create 5 random numbers between 1 and 10 and number 3 or 6 or whatever may *not* occur more than once?
AB

9-9-2008

I am trying to creat a phone service for a village where I was born, about 7000 people live in the village I know that, simple devices like ring generetor and cheap phones can creat an intercom and by adding more devices I can expand the band and quantity of phone lines but I don’t know how to give a number to my phones.

First I want to try it on few sets of phones then expand it, Please help me creat numbers for my custom intercoms then I will try to use that example to create 4-6 digit numbers for my planed local phone service.

Thanks
Thomas Seidler

11-8-2008

@AB you’re a funny guy!

How about this (no recursion):

// works for -10 to -1, 1 to 10, -5 to 4 etc
Let(
[
range = stop-start+1;
range_result = Int(Random*range);
result = range_result + start
];

// necessary in event of Random=1
Min(result; stop)
)

The maths works fine. Blessings, t
Thomas Seidler

11-8-2008

Let(
Num = Int ( Random * ( Maximum - Minimum + 1 ) ) + Minimum;
Min(result; stop);
)

would be your tight solution, but i like it clear to my head…

Name	*
E-mail	* (will not be published)
Website

Six Fried Rice