Creating Random Numbers in FileMaker

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
1. multiply by `Random`
1. hack off the integer value of the result
1. add the result to the lowest number in our range
1. 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.

Leave a Comment

32 thoughts on “Creating Random Numbers in FileMaker”

  1. 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’.

  2. …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.

  3. @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!

  4. 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 🙂

  5. 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.

  6. 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.

  7. 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?

  8. 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

  9. @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

  11. @Thomas Seidler

    Your solution to avoid recursion is actually mathematically incorrect. Rather than discarding the result if a Random=1.0, it treats that result as the same as Random=0.9999999999999999999. So you have effectively just doubled the likelihood of Random producing 0.9999999999999999. Most of the time this is a tiny increase in likelihood in the final integer result, but its still mathematically incorrect.

  12. hi thx, (rather like O Homan above)
    i am looking for an elegant way of randomly dealing 10 cards from a pack of 21 (all different) one at a time with no repetitions; ie the second card is randomly picked from 20 cards(original 21 less card choice 1, and so forth)

    i can do a ‘clunky’ fix involving 10 container fields that mirror 10 text fields and then randomly draw from. for the first card 21, then from 20 less card choice 1 etc)

    but just before i head towards scripting “if text 1 contains “x” or y or z etc, could you piont me to a more elegant solution?

    many thx

  13. further, your random number function works well for card 1, thx, but then i need to “create” a new list for each subsequent card draw – a list that diminishes by the previous chosen random numbers…

    sry for the wordy explanation 🙂

  14. sry just looked again! your random letter example perhaps – where if “x” was my first card drawn then my middle letter list would then be made up of all letters, less x, and the maximum number less 1, and so on….
    thx again

  16. It is worth noting that the official FileMaker 10 Training Series manual states, on page 5-11, that Random returns a random decimal number between 0 and 1. In the questions section, page 5-14, questions 5.2.4 and 5.2.5 ask the reader to solve the following:

    Ceiling ( Random * 6 )
    Floor ( Random * 10 )

    The answers provided on page 5-63 are:

    A random integer from 1 to 6
    A random integer from 0 to 9

    These answers match the training text but not the FM KB article. If we are to follow the information stated in the FM KB article, the answers would be:

    A random integer from 0 to 6
    A random integer from 0 to 10

    Once again, we have conflicting information being provided by FileMaker…..

  18. LOL. As FM are giving two different answers, and since we can’t prove that a 1 or 0 is an impossible result, our only course is to prove that you CAN. Any takers? ; )

    The irritating thing is, I’m going to be booking my FM 10 exam soon. I can imagine the scene now, me furious with righteous indignation that my answer to the question on the Random function is correct whilst the examiner patiently quotes from the gospel that is the FM 10 training manual.

    Now THAT is exiting : )

  19. Jonathan:

    When we first wrote this, Jesse and I joked about setting up a machine to look for 1 from Random:

    Loop
   Exit Loop If [Random = 1]
End Loop

    I suspect it would still be running 🙂

    But in our defense, I went straight to the horse’s mouth, so to speak. I happen to know who wrote the core calculation engine in FileMaker 7, so I asked him, and he kindly responded that Random can, indeed, return 1. I believe him.

    Geoff

  21. How can you incorporate this for a found set of records. So I have a found set of 179 records and I want to randomly assign a value to each record, so basically 1 to 179 without duplication.

    Can this custom function work with this or is that something out of the scope?

  22. Garry:

    That is a slightly different problem because you want to remove the number from the available set once it has been used. One way to do it would be to set up a list of number from 1-179 like this:

    Set Variable [ $i, 1 ]
Loop
   Set Variable [ $choices, $choices & $i & ¶ ]
   Set Variable [ $i, $i + 1 ]
   Exit Loop If [ $i > Get(FoundCount) ]
End Loop

    Then randomly select one and remove it from the list:

    Set Variable [ $rand, RandomNumber(1, ValueCount($choices)) ]
Set Variable [ $one, GetValue($choices, $rand) ]
Set Variable [ $choices, LeftValues($choices, $rand - 1) & MiddleValues($choices, $rand + 1, ValueCount($choices))

    Do this in a loop and you should get them randomly assigned.

    This is all untested code.

    Geoff

  23. I ran into the same question Johnathan had about Random and the same problem. Below is what I found addressed in FKB. To include 1 or 0 or not that appears to be the on going question. Is the test based on v3 or 10.0.

    FileMaker Pro 10.0v3 addressed an issue where the “Random” calculation function could generate non-random values on certain machines. The “Random” calculation will now generate values greater than or equal to 0 and less than 1. The value will never be 1.

    In versions prior to FileMaker Pro 10.0v3, the “Random” function returns “a random number between zero and one” including “zero and one”.

    patricia

  27. Hmmm…

    Let(
    Num = Int ( Random * ( Maximum – Minimum + 1 ) ) + Minimum;
    If( Num == Minimum ; Num ; RandomNumber( Minimum, Maximum ) )
    )

    Does not work. Its throwing an error….

    what is MAX and MIN, those are fields ?

    Miles.

  29. Rather old post, but interesting… Check out this random sequence script, easily adaptable to any way or form. Super random creates numbers like this:

    723883A1
    619590H5
    205985B6
    635182T1
    627171U9

    I took out the letters “O” and “I” as these confuse with 0 and 1.

    Here it is…

    Left ( Random * 100000 ; 3 ) & Left ( Random * 100000 ; 3 ) & Middle ( “ABCDEFGHJKLMNPQRSTUVWXYZ” ; ( Round ( Random * 23 ; 0 ) + 1 ) ; 1 ) & Left ( Random * 100000 ; 1 )

  30. Slight correction… Had an instance where the period (.) showed from the Random command (16.158J5), so I added a ‘0’ to the * 100000.

    Left ( Random * 1000000 ; 3 ) & Left ( Random * 1000000 ; 3 ) & Middle ( “ABCDEFGHJKLMNPQRSTUVWXYZ” ; ( Round ( Random * 23 ; 0 ) + 1 ) ; 1 ) & Left ( Random * 100000 ; 1 )