Creating a random number with FileMaker is a seemingly impossible task. Fortunately for us, FileMaker provides us with a very useful calculation function to help us along the way: Random. Alas, this function isn't quite what you think.

Random doesn't just produce any old random number (if it did you definitely wouldn't be reading this post). Random produces a number from 0 to 1 as a 20 digit decimal value! I know this sounds a little ridiculous and completely useless, but with a little finagling we can produce a usable random number from this giant decimal.

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:

  1. calculate total number of values we want to produce
  2. multiply by Random
  3. hack off the integer value of the result
  4. add the result to the lowest number in our range
  5. 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.