Find a random record

Squiffy

Is there a way to pick out one or more random records from a BO? For example :

BO1 - Customer :
Name (text)
RandomGift (text)
Status (text)

BO2 - Gift
GiftName (text)

BO3 - APIResult
Name (text)
GiftName (text)

Conceptually I need to do this kind of thing :

FIND BO1 WHERE BO1.Status='ProcessMe' TAKE BEST 100
CREATE BO3 FOR EACH BO1 WITH BO3.Name=BO1.Name, BO3.GiftName=[b]A_RANDOM(BO2.GiftName)[/b]
BO1.Status='Processed'

The process is run from a REST API call, and BO3 is returned as a JSON string.

Each run of the process would generate a random gift to each Customer, even if the same customer was rerun by having its status reset to 1 (so there is no relationship before or afterwards between Customer & Gift).

Also, there may not be as many BO2 instances as BO1, meaning some will inevitably double up. In fact, BO1 is going to be in the millions and BO2 will probably be a few hundred, maybe less in some cases.

Does any of that make sense?

tford

I would number the Gift instances sequentially, then when you want to select one randomly you can use RANDOM function to generate a number in that range.

Squiffy

I thought of that too, but it relies on the numbers being contiguous. I think that over time deletes will break that method, returning periodic blanks.

Something like SQL's ORDER BY RAND() would be ideal, but there's nothing like that that I can see.

I can only think of counting the records, selecting a random number between 1 and the count, then stepping through that many times to get the record. Seems a bit long winded though.

Anyone done anything like this before?

tford

I thought of that too, but it relies on the numbers being contiguous. I think that over time deletes will break that method, returning periodic blanks.

A couple tweaks to consider:

1) Re-order the Gift instances periodically through a rule that fires when one is removed

2) Once you calculate a random number, do a FIND with a sequence number either >= or <= (whichever you prefer) and use TAKE BEST 1.

Squiffy

tford wrote
1) Re-order the Gift instances periodically through a rule that fires when one is removed
2) Once you calculate a random number, do a FIND with a sequence number either >= or <= (whichever you prefer) and use TAKE BEST 1.

(2) is what I'm playing with now, but (1) hadn't occurred to me at all. Need stronger coffee.

Thanks,

Squiffy

At the risk of sounding thick ..

is there a built in way to reorder them? I assume by reordering you mean recreating the auto ID fields to be contiguous.

I can't see anything obvious in the rule documentation.

thanks

tford

I assume by reordering you mean recreating the auto ID fields to be contiguous

No, you can have another attribute like GiftSeqence where you manage the order.

Squiffy

That's what I meant, even though it wasn't what I wrote 🙂

So, I am maintaining my own Sequence attribute. I set it to MAX +1 on instance creation and I zero them all then rebuild upon a delete.

Untested, but I think that should work.

After that, just got to get the random bit working.

Thanks.