Stored Calculations with Status Functions

[Peter Fenner]

A FM Forums User posted to following question: "I need to create a script that can randomly select 15 records, but I don't know how. Please help me."

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

My response was:

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 & "

[cjaeger]

most status functions do not update if calc is stored. However, you can force the recalculation by another field:

field a = Left(field b;0) & Status(CurrentRecordNumber)

This updates field a if field b is modified....

[BobWeaver]

If it's a stored calculation, no it won't update. If it's unstored, it will update (ie, recalculate) whenever it is referenced.

BTW, your formula: Round(Random* Status(CurrentRecordCount), 0) + 1

will not give equal probability to each record. The first record will only have 1/2 the probability of being selected compared to the other records. And there is a chance of coming up with a record number that is one higher than the actual record count. To see why, assume that you only have 3 records in the file and work through the math. There is a 16.67% chance of picking record 1, a 33.33% chance of picking record 2, a 33.33% chance of picking record 3, and a 16.67% chance of picking non-existant record 4. You should replace the Round() function with the Int() function to fix this problem.

[cjaeger]

you will have the random on the Linker field, i.e. extract a list of record IDs from the related file, then pick 15 (words) of them at random and paste into linker. Since you are using a script anyway, you can go to a layout in the related file just containing record ID & copy all records or use the value list design function ....

[danjacoby]

Besides all that other stuff, you're not directly addressing the problem; instead, you're going around via a rather circuitous route.

I stand by my solution:

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

It has the added advantage that, if you want to pull a different number of records (say, 20), just change the ExitLoopIf number to (in this case) 20. You could even create another global [number] field to determine how many records you want to pull.

[cjaeger]

this is the easiest script. at first, i was not so sure about the round(random * x;0) +0.5, but it should always yield the same result as int (random * x) +1 ....

[Peter Fenner]

Hi All - Thanks for the Responses.

BobWeaver - I meant to use the Int function, but thanks for pointing out my error. I ought to be more careful!

danjacoby - I do realise I did not address the problem, however here I was more interested in the logic behind the storage of the status function. BTW, your solution to the "Find 15 records" question was very useful to me. So, Thank you.

Christian - thank you for clarifying the crux of my question.

Pete

[Vaughan]

Script to find 15 random records

Show All Records

Sort by Rdm

Omit Multiple (15)

Show Omitted

where the field Rdm = Unstored, Random

[Ugo DI LUCA]

So we just all needed to wait for Vaughan, back from a good week-end

[BobWeaver]

I've used Vaughan's method a number of times. It's nice and simple, but it does have one drawback. If there are a lot of records (more than a few thousand), sorting on an unstored field can be slow. In which case, Peter or Dan's method would be more efficient, especially if you are only selecting a few random records out of the large set.

[Vaughan]

Bob -- it must be your method then, because I've never done it before. That was just a thought exercise on my part!

I can see that it would be slow as the record count grows, but it does have the advantage of 100% reliability in multi-user -- Set Field will fail with locked records.

Of course, the speed could be enhanced by first finding, say, the last (newest) 500 records and picking the 15 randomly from these.

[BobWeaver]

I used the random sort method for a little slideshow database that I made. I has about 500 images in it, and it works well for that. When you start the slideshow, it randomly sorts the images so that you always get a different display order. The nice thing about the method in this situation is that the records remain in this same random order until the next slideshow. This allows a user to stop the current slideshow, and go back or forward several pictures without losing the current order.

[Peter Fenner]

Ahem! Hi Bob. I see you mention my method as an option. As far as I now understand my method simply won't work because the stored field I have cannot be stored if it is to work. Of course, I can't make my RecordNr field unstored as it is the other side of a relationship. So i have a no-go solution there.

Pete

[BobWeaver]

I meant that the basic idea behind your method seemed feasible, and your method should work if the random selection is to be made from the full set of records, because you could use an autoenter serial number field rather than an unstored calculated field based on Status(CurrentRecordNumber).

If you want to select a few random records from a subset of records, then you could begin the script with a Replace on the RecordNr field with the formula: Status(CurrentRecordNumber). This isn't as neat, but then Dan's method would have to be modified too, to do a random selection from a subset.

A lot depends on the specific circumstances.

[Peter Fenner]

Got it! Thanks Bob.

Pete.