1 2 Previous Next 15 Replies Latest reply on Jun 25, 2009 8:00 AM by mrvodka

    Creating prize file database for lottery style cards

    MsJoanne99

      Title

      Creating prize file database for lottery style cards

      Post

      I am trying to determine if Filemaker Pro is the correct program to purchase for creating database files used to created a lottery type game card. An example of what I need to be able to do, and have not figured out yet, is create a data base with a set number of records, say 3752, with a "Prize" field containing "A" 413 times, "B" 338 times, "C" 188 times, "D" 38 times, "E" 28 times, "F" 9 times, "I" 2250 times and "H" 488 times. The A, B, C, D, E, F, I and H needs to be randomize throughout the file. The records need to have a field that is consecutively numbered and I have figured out how to that and I have written a script so that I can create the 3752 files, with the consecutively numbered instantly by running the script, but I can't figure out how to put together a formula or write a script to fill my "Prize" field with "A, B, C, D, E, F, I or H" a specific number of times and randomize them throughout the entire population of the data file, please help!

        • 1. Re: Creating prize file database for lottery style cards
          comment_1
            

          Filemaker can probably do what you want, though not in the way you describe. It's too bad you didn't explain the rules of the game instead.

           

          IIUC, there are 3752 tickets, and assorted prizes. To draw the winners for prize "A", for example, you could show all records, sort them by random order (an unstored calculation field) and omit all but the first 413 records. Then you'd probably want to import these into the Prizes table to keep a permanent record, and continue with the drawing of remaining prizes.

          • 2. Re: Creating prize file database for lottery style cards
            MsJoanne99
               These are scratch off game pieces for a restaurant. Let's say "A" will trigger a Free Beverage to be imaged under the scratch off and "B" will trigger free fries, "C" will trigger free burger etc. I need a specific number of "A's", "B's", C's", etc... per 3752 records. Is there a formula or script that I can set up to fill the "Prize" field with with the specific number of "A's", "B's", "C's", etc... needed through out the population of 3752 records. I am looking for a way to automate this rather than manually creating 413 "A" records, 338 "B" records... 2250 "I" records, etc... I really need a way to automate the placement of these prizes, because sometimes our database file will have over 6 million records and that is way to much to create manually. Any thoughts on how this can be achieved? 
            • 3. Re: Creating prize file database for lottery style cards
              mrvodka
                

              Will every record have a prize or will some not have one. IOW, you said 6 Million records. Do you mean that 3572 have prizes in them out of 6 Million, or do you mean that 3572 is just a sample figure and that really it is 6 million.

              • 4. Re: Creating prize file database for lottery style cards
                MsJoanne99
                   Every record will have a letter representing a prize, even if the prize is "Please try again". This scratch card really has 5 lots of 3752 for a total of 18760 records, but I assume that once I figured out how to do one lot I could apply the same principle to work out the other lots. Often different lots will have different prizes and or different amounts of the prizes. We just did a scratch card for 6 million, but it is not this game. But I need to automate creating the various records because the databases for future games can be quite large. This is why we are hoping to utilize FileMaker instead of Excel as Excel is limited to 65,000 records.
                • 5. Re: Creating prize file database for lottery style cards
                  mrvodka
                    

                  How about a script as such:

                   

                  It take the values from a settings table and generates the number of records in a new table. The settings table has two fields: textuse and numuse. textuser is your letter and numuse is thenumber of times to use it.

                   

                   

                  # Loads settings into variables


                  Go to Layout [ “settings” (settings) ]
                  Show All Records
                  Go to Record/Request/Page [ First ]
                  Loop
                      Set Variable [ $i; Value:Get ( RecordNumber ) ]
                      Set Variable [ $textuse[$i]; Value:settings::textuse ]
                      Set Variable [ $numuse[$i]; Value:settings::numuse ]
                      Go to Record/Request/Page [ Next; Exit after last ]
                  End Loop
                  #

                  # Generates records

                  Go to Layout [ “new” (new) ]

                  Loop

                  Exit Loop If [ $i ≤ 0 ]
                      #

                      Set Variable [ $val; Value:Ceiling ( Random * $i ) ]
                      New Record/Request
                      Set Field [ new::text; $textuse[$val] ]
                      Set Variable [ $numuse[$val]; Value:$numuse[$val] - 1 ]
                      #

                      # If the counter for that item hits zero then move the rest of the reps greater than the zeroed one up

                      If [ $numuse[$val] ≤ 0 ]
                          Set Variable [ $k; Value:1 ]
                          Loop
                          Exit Loop If [ $k > $i ]
                              If [ $k > $val ]
                                  Set Variable [ $textuse[$k - 1]; Value:$textuse[$k] ]
                                  Set Variable [ $numuse[$k - 1]; Value:$numuse[$k] ]
                                  Set Variable [ $k; Value:$k + 1 ]
                              Else
                                  Set Variable [ $k; Value:$k + 1 ]
                              End If
                          End Loop  // End move counter loop
                          Set Variable [ $i; Value:$i - 1 ]
                      End If
                  #
                  End Loop  // End geneneration loop
                  #
                  #













                  • 6. Re: Creating prize file database for lottery style cards
                    comment_1
                      

                    You could have a template table with 413 "A" records, 338 "B" records, 188 "C" records, etc.

                     

                    To create a new batch, import the template records, sort them by a random number, and serialize them (or randomize them first in the template table, then import them).


                    • 7. Re: Creating prize file database for lottery style cards
                      MsJoanne99
                        

                      I am working on digesting this script, I am sure this is exactly what I am looking for, but I am not versed enough to completely comprehend how to make this work. I tried to recreate this script and wasn't able to duplicate exactly. Example the 2nd sub listing under the 1st "loop" that reads: "Set Variable [ $testuse[$i]; Value:settings;;testuse]" I am unable to insert the "[$i], it won't let me. I created a database with the two fields: textuse and numuse, that looked like this:

                      A   413

                      B   338

                      C   188

                      D   38

                      E   28

                      F   9

                      I   2250

                      H   488 

                       

                      Was this the correct way to do this? 

                       

                      Also I am far from a pro, so perhaps you could help me understand the "Settings table". Do I create two separate tables within the same database and link them via the script or relationship tab?

                      • 8. Re: Creating prize file database for lottery style cards
                        MsJoanne99
                          

                        I could do this but each game is different (different amounts of "A's", "B's, etc..) and I was hoping for a way to automate this process. Although this way is probably more in keeping with my ability level. We currently job out the more complicated games and I was hoping that FileMaker pro would allow us to do these in house as FileMaker is touted as for use by "non technical people", but I think I may need programing experience to pull this off.

                         

                        Thank you for the idea! 

                        • 9. Re: Creating prize file database for lottery style cards
                          mrvodka
                            

                          Yes two tables. In my example I call one "Settings" and the other "New". No relationship is required.

                           

                          The "New" table is where your records will be generated. You will need a field called "text" and an auto-entered "ID" field

                          The "Settings" table is correct. You already made the two fields and your 8 records.

                           

                          As for the Set Variables with the [], this script uses repeating variables. Therefore, a step such as:

                          Set Variable [ $testuse[$i]; Value:settings::testuse ]

                           

                          In the Set Variable options dialog, you will see name, value, and repetition fields ( default should be 1 ).

                          name field:   $testuse

                          value:   settings::testuse

                          repetition: $i

                           

                           

                          Just double check to make sure that the script looks the same as the one that I posted and mind the $ signs. ;)

                           

                           

                          • 10. Re: Creating prize file database for lottery style cards
                            comment_1
                              

                            MsJoanne99 wrote:

                            each game is different (different amounts of "A's", "B's, etc..) and I was hoping for a way to automate this process.


                            That is certainly possible. You should probably have two tables for this: Games and Prizes (i.e. one Game to many Prizes). In the Prizes table, there would be fields for:

                            • GameID

                            • Prize (e.g. "A", "B", "C"...)

                            • Quantity

                             

                            Then you can use a script to create template tickets for each new game. This part would be somewhat similar to what John suggested - but you would only need to run it once, when entering a new game.

                             

                             

                            @John:

                            I am struggling to understand your randomizing method.


                            • 11. Re: Creating prize file database for lottery style cards
                              mrvodka
                                

                              Hi Michael.

                               

                              Could you be slightly more specific?

                               

                               

                              • 12. Re: Creating prize file database for lottery style cards
                                comment_1
                                  

                                I wish I could, but I'd have to reconstruct your entire script and I am trying to evade that...

                                 

                                 

                                Let's have a simple example game:

                                 

                                10 x First Prize;

                                20 x Second Prize

                                30 x Third Prize

                                40 x Consolation Prize

                                -----------------------

                                100 tickets in total

                                 

                                 

                                Now it looks as if your first loop ends with $i = 4 (4 records in the Settings table). Then you assign Ceiling ( Random * $i ) to the first record - and after that I lost you.

                                 

                                I believe records need to numbered randomly with numbers from 1 to 100, with no duplicates.

                                 

                                • 13. Re: Creating prize file database for lottery style cards
                                  mrvodka
                                    

                                   The script was using the random step to choose which repetition of the variable to go to when setting the field value. Once it sets, then the number was subtracted. If a certain repetition hit zero then the reps higher than that would shift down.  I was trying to treat these variable reps like arrays.

                                   

                                  I was choosing to set the value with the record creation rather than creating the records first then setting a random value.

                                  • 14. Re: Creating prize file database for lottery style cards
                                    comment_1
                                       Could you send me your file in a PM on FM Forums or e-mail?
                                    1 2 Previous Next