6 Replies Latest reply on Oct 24, 2013 7:48 AM by DavidJondreau

    Bingo! Generate random numbers for Bingo cards.

    flybynight

      I've been tasked with a fun variable data print project. A client wants to produce 3000 custom "Bingo" cards with random number configurations.

       

      My first thought is to turn to FileMaker to generate the numbers - the layout will be in InDesign and we can just "mail merge" the numbers together with the layout.

       

      So, our standard Bingo rules apply - we need to fill a 5x5 grid with numbers (sans the "free space" in the middle).

      Each column will have a range of possible numbers. B = 1-15, I = 16-30, N = 31-45, G = 46-60, O = 61-75.

      The trick is that there can be no duplicate numbers in each record. You can't have 2 B-7's, on one card, for example.

       

      Is it as easy as having my fields be calculations (or auto-enter calcs) and can I then have some sort of validation that says the field B1 ≠ B2, B3, B4 or B5?

       

      Or do I have to build each record with a script, have it go to the first field (B1) set it to a random number with the formula:

      1 + (Int(Random * 15)

      …then move to field B2, set it with the same formula, but then use a loop to check if B2 = B1, re-generate the number for B2 until it is unique.

      …repeat for B3, B4 and B5.

      Then move on to I1 and do the same, except change the formula to:

      16 + (Int(Random * 15)

       

      If it could all be done with field-level validation, then all I would have to do is create 3000 records and… Bingo, I'm done. If not, then I'll have to do it with the script method.

       

      According to Wikipedia, there are 552 Septillion (that's 24 zeros!) possibilities, so I'm assuming FMP's random should be good enough to not generate any duplicate records over 3,000. I guess it would be easy enough to have a concatenated field with unique validation, but probably not necessary.

       

      Or if anyone has a better idea, I'm all ears.

      I did some searching on the net and this forum and didn't find the same thing. Several sites that can generate Bingo cards, but I'm just looking for the raw numbers to put into my own layout. If that does exist already, why re-invent the wheel.

       

      Laters,

      -Shawn

        • 1. Re: Bingo! Generate random numbers for Bingo cards.
          mcurley

          I suggest starting with this: http://sixfriedrice.com/wp/creating-random-numbers-in-filemaker-pro/

          That works great for B1, but B2 needs to check for duplicating B1.  So, you can modify this custom function and add the validation:

           

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

           

          Field Definitions

          B1 = Auto Enter: RandomNumber ( 1,15,0,0,0,0 )

          B2 = RandomNumber ( 1,15,B1,0,0,0 )

          B3 = RandomNumber ( 1,15,B1,B2,0,0 )

          B4 = RandomNumber ( 1,15,B1,B2,B3,0 )

          B5 = RandomNumber ( 1,15,B1,B2,B3,B4 )

          I1 = RandomNumber ( 16,30,0,0,0,0 )...

           

          Then create 3,000 records.  I would add a field Duplicate = B1&" "&B2&" "&B3... with all your fields in order, where you can do a quick search for any duplicate cards (Duplicate = !).  I didn't verify this, so good luck.

          • 2. Re: Bingo! Generate random numbers for Bingo cards.
            jbante

            The way I typically create lists of random values that can't include duplicates is to shuffle the list of possible values, and then just use the first several values from the shuffled list:

             

            Loop

                 Exit Loop If [Let ( $i = $i + 1 ; $i > 15 and Let ( $i = "" ; True ) )]

                 Set Variable [$bList ; List ( $bList ; $i )]

            End Loop

            Set Variable [$bList ; ValueShuffle ( $bList )    // Using the ValueShuffle custom function]

            Loop

                 Exit Loop If [Let ( $i = $i + 1 ; $i > 5 and Let ( $i = "" ; True ) )]

                 Set Variable [$b[$i] ; GetValue ( $bList ; $i )]

            End Loop

            # etc.

            • 3. Re: Bingo! Generate random numbers for Bingo cards.
              Malcolm

              Don't over-think the problem. Randomising 75 numbers is pretty easy. The attached file uses a sorted relationship to randomise a set of numbers. It also uses the numbers to generate a string which can be used as a test for uniqueness.

               

              Whoops. That file had a bad algorithm. I've corrected the mistake and updated the file.

               

              Message was edited by: Malcolm removed file

              • 4. Re: Bingo! Generate random numbers for Bingo cards.
                flybynight

                Malcolm, we mustn't under-think the problem, either. I'm guessing you weren't dragged to as many Catholic fundraisers in your childhood as I was.   

                You're forgetting one of the rules of Bingo, as stated in the OP: The entire grid is not available for all 75 numbers. The "B" column can only have numbers in the range of 1-15, The "I" column can only contain 16-30, etc.

                 

                This rule actually simplifies the problem, in a way. We only have to worry about 15 numbers… we just have to do it 5 times, adding 15 each time (or increasing the range minimum and maximum by 15, depending on the method).

                 

                mcurley, that sixfriedrice custom function is basically the same as the more simple formula I had. Theirs is just more flexible with the ability to set the min and max values, where mine is hard-coded into the formula. Plus, theirs tests for a very rare possibility of random returning "1" - and FileMaker fixed that in 10.0v3, according to the string of comments. Interesting nonetheless.

                Interesting method you added for checking for duplicate records. OK for this situation, but cumbersome to scale, if for instance, we actually did want to get a random string of 75 numbers without duplicates.

                 

                I know, I know… I go back and forth between "works for what I need" and "it's not modular enough!" Sorry about that. Anyway, that's why I was wondering if simple field-level validation could do the duplicate checking. That way, it wouldn't even need a custom function. Not opposed to using them… just like to avoid it when possible.

                 

                Thanks everyone for the suggestions. That's what I love about TechNet. Always several ways to skin the proverbial cat!

                 

                Learning more every day,

                -Shawn

                • 5. Re: Bingo! Generate random numbers for Bingo cards.
                  Malcolm

                  The US game is completely different to the game played in Commonwealth countries.

                   

                  The modified file takes into account those limitations.

                   

                  Edited file to use

                  • 6. Re: Bingo! Generate random numbers for Bingo cards.
                    DavidJondreau

                    My first reaction was to have a set of 15 records numbered 1-15. Get a random number based on the found count using a custom function or build your own (I'd use Mod() for mine). Grab that number, and omit the record with that value. Repeat 5 x. Repeat that 5 times, adding 15 to the result for each successive letter.