I need to create a script that can randomly select 15 records, but I don't know how. Please help me.

I'd start here:

http://www.filemaker.com/ti/102318.html

http://www.filemaker.com/ti/102295.html

http://www.filemaker.com/ti/101922.html

The basic method is to do a Replace in a number field using the Random function. Then you Sort on that field, Omit 15 records and Show Omitted.

I know I answered this very question last week. Where did I put that thread?

If anyone finds it, please link it, because it's an effective, tested solution.

Hi There

Please let me know if the following would work. Will the Status(CurrentRecordNumber) update since it is stored?

Create a global text field called, for example, "Linker"

Create a text calculation called "RecordNr" with the following calculation: Status(CurrentRecordNumber)

Create a relationship Linker::RecordNr. We'll call this relationship R1.

Create a script as follows:

Clear [select, "Linker"]

Loop

Insert Calculated Result ["Linker", Round(Random* Status(CurrentRecordCount), 0) + 1 & "

Hi Pete,

I did the following some time ago. I had to go back to the file to extract the script. So yours should also work.

g_random field (text)

Script (just changing my 100 to the 15 records)

clear g_random (select)

Loop

End Loop If (Patterncount(g_random, "

Hi There Ugo

I really should know this, but firstly with my solution: the Status(CurrentRecordNumber) I have used as one side of a relationship, and is therefore stored. Isn't this then going to capture the RecordNumber at the time I created the field and not update - hence records will effectively be excluded from the "search" process?

In your solution what happens if records have been deleted and therefore some will also be missed in the "search" process. You may have a serial nr of 65 but a total of 50 records? Also, as random is being used multiple times it could produce the same result in some cases and therefore not produce 15 results.

Have I lost the plot? Or are my concerns genuine?

Pete

Hi Pete,

True you are, I should have counted the relationship instead of the carriage in the g_random.

I may have been lucky cause I didn't run into duplicate records in the "g_random", but the chances were little cause this file holds about 8,000 records now (7,000 at the time of the script I think).

In addition, I've used this script twice for a lotery within my customer file, where I hopefully don't have deleted records, and it was triggered to the Customer_Id instead of the serial. That's the purpose of the NumToText (even if I don't know if it would have changed anything ).

>>

I really should know this, but firstly with my solution: the Status(CurrentRecordNumber) I have used as one side of a relationship, and is therefore stored. Isn't this then going to capture the RecordNumber at the time I created the field and not update - hence records will effectively be excluded from the "search" process?

Yes, the Status(CurrentRecordNumber) changes when you delete a record, but it is still there. So it would work very nicely I assume.

Pete,

So I tried yours over my 8,225 records and it never stoppd looping. I assume the Exit Loop if Count(rel) doesn't work in a loop step

OK, here's the answer:

First, create a global field ("gRandom") that returns a number.

Now the script:

Show All Records

Loop

SetField(gRandom, "Round(Random* Status(CurrentFoundCount) + .5, 0)")

GoToRecord/Request/Pate ("gRandom") -- that's by Field Value

Omit Record

Exit Loop If (Status(CurrentRecordCount) - Status(CurrentFoundCount) = 15)

End Loop

Show Omitted

This has been tested on a file with over 11,000 records, and it works -- to change the number of records you want to pick at random, just change the number in the "Exit Loop If" step.

Thanks danjacoby - I can see that your method would work beautifully.

Hi Ugo, I don't know why my solution should be continuosly looping. It must be to do with the Status(CurrentRecordNumber) part of my process.

Can anyone confirm exactly why my solution is problematic (message 66369 about 5 entries up)

Thanks - Pete

Thank you danjacoby, I know you have answered this question before. I'm sorry that I have to post it again because all my records are different, so I can't use the global field. Please help me create a script that works with text field. Thank you all.

Thank you all!!! I finally figured it out (with your help) Ok, tell me if wrong, but this is how I did mine.

First of all, I created a text field and named it 'A'

Then for my script:

Show All Records

Loop

Set Field ["A", "Round(Random* Status(CurrentFoundCount) + .5, 0)"]

Omit Multiple [No Dialog, 15]

Exit Loop If ["(Status(CurrentRecordCount) - Status(CurrentFoundCount) = 15)"]

End Loop

Show Omitted

Please tell me if I'm right. Thank you!

Omit Multiple?

I think that's gonna be a problem. Take a look at how this is different from my script above.

What you are doing is merely omitting the first 15 records; you're not using the Random function to choose records for you.

You have to use the Random function to choose a record to omit, then omit that record, then loop this until you have omitted 15 records.

Also, 'A' does not need to be a text field, as this is wasting space in the file. Just a global field will do.

Too right, Silvan -- "A" should be a global, number field.

But I can't have global, number field because they have to be different for every record!!! And someone please tell me how can I do random without omit. Thank you.

The first step, Show All Records, is very important. What happens after that is that you go to a random record and Omit it. You do that 15 times. Then in the final step, you Show Omitted and there you have your 15 records. Nobody is saying that you should do random without omit -- just that the script you posted is not going to give the desired results.

As for the global, that is simply used to create a value for the "Go to record" step, so each time through the loop you go to a different random record.

Try Dan's script, and just for testing, put a Pause right after the SetField(gRandom -- put the gRandom field on your layout -- and you'll see that it works.

Hi,

Moreover, don't forget what a loop script is....You are defining 15 different global fields, then triggreing 15 different Go To record step and then 15 omit step....

That's why the global isn't a "fixed" record in that case, and that's why Dan solution is working as a treat.

Thanx muchly for your support -- I'm also available for weddings, bar mitzvahs...

Thx for helping me out. But I still have some questions, I have 45 records, and I have a field name "selected number". How can I make a script that can tell me which one got selected first up till 15?

Let say my first record came up randomly is 'C', then in the field "selected number" should have number'1' in it. If the second record came up to be 'T', then the field "selected field should have number '2' in it. And so on.

Please help me, I appreciated it.