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.
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?
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.
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.
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 ]
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 ]
# Generates records
Go to Layout [ “new” (new) ]
Exit Loop If [ $i ≤ 0 ]
Set Variable [ $val; Value:Ceiling ( Random * $i ) ]
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 ]
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 ]
Set Variable [ $k; Value:$k + 1 ]
End Loop // End move counter loop
Set Variable [ $i; Value:$i - 1 ]
End Loop // End geneneration loop
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).
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:
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?
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!
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
Just double check to make sure that the script looks the same as the one that I posted and mind the $ signs. ;)
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:
• Prize (e.g. "A", "B", "C"...)
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.
I am struggling to understand your randomizing method.
Could you be slightly more specific?
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.
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.
Could you send me your file in a PM on FM Forums or e-mail?