Placeholder

Customer Forum

Randomly selecting records in your database

Workbooks Support Posted: 2016-05-06 11:57

It is possible to create a report that randomly selects a set number of records from your database. It works by randomly assigning a number to each record, then limiting the report to the results with the lowest/highest assigned number.

Create a report from the record type you are randomly selecting from, in this example, Organisations. Add the standard columns that you'll require, then add a calculated column. The formula you want to use is RAND(), which outputs different random numbers depending on what number is between the brackets.

If you put a fixed number between the brackets, the Organisations would always be assigned the same number. To ensure that the random number would be different each time you ran the report, you can use a function. For example, SECOND(NOW()) will use the numbers from 0-59, depending on the time that you run the report and will give 60 different sets of results.

If you were running this report each month, you could choose MONTH(NOW()), or even MONTH(NOW()) * YEAR(NOW()) if you want to ensure that the results are different year on year too.

You will then need to make sure that your results are ordered by this random number, either ascending or descending. Then you can go to the Criteria tab, click 'Reduce rows' and limit the results to the set number that you want.

Your results should then look like below. This report can have other criteria added to it: perhaps you only want the Organisations from a certain Industry or Region. It could then be sent out as a scheduled report to the relevant people.