random value in a range

[mikecrengland]

Hello --

I need to choose a random value from a range of numbers. The numbers are form save IDs and are not sequential. The IDs might range from 100,000 to 500,000, and only have 200 actual values. (The database assigns consecutive IDs to every record, regardless of the record type.)

My goal is use a text box to display a random comment each time something 'happens' (such as a filter being applied).

I have a statement that returns a random value between the min and max of the range:

= Ceil (rand () * (max([Success Story Form Save ID]) - min([Success Story Form Save ID])) + min([Success Story Form Save ID]))

My plan is to use this as a maximum and return the largest value of the data set that's left. So, in plain English, I want to say that the minimum value is the first form save ID and the maximum value is the random number generated from the formula above, then pick the largest of the IDs left.

Hopefully the question makes sense... Is this possible? Maybe there's an easier way?

Thanks in advance...

[johnw · Accepted solution]

I'd probably map your [Success Story Form Save ID]s to sequence numbers.  Then display something like this:

=only({<Sequence={$(=ceil(rand()*max({1} Sequence)))}>} Story)

If you want more control over the definition of "something 'happens'", you could instead set a variable randomly every time something 'happens', and use the variable in the set analysis.

I haven't tried any of this, so I don't guarantee it works, but it seems like it would.

[mikecrengland]

Thanks, John! I'll give it a try and let you know what happens.