Creating a Randomiser in PowerApps

[joshieboy]

Hi everyone,

I have a requirement whereby our company would like to do substance abuse testing (drug testing) with the entire staff.  Our company has upwards of 600 employees, and there is a policy in place to ensure that each employee gets tested at least TWICE per year.  The policy further goes on to say that it must be a randomized list of employees for testing, to avoid being bias, so we need a system in place for this.  

The system must allow us to select dates from a calendar for the entire year that we would like to use as dates for employees to be tested, omitting weekends and public holidays as well.  ie:  We would like our own date pool to work with, and once these dates are selected, let the system randomly select employees ( A total of 15 employees per week as an example).  At the end of the randomizer, each employee should have two dates in the year for their testing.  We would also like to cater for any new employees that join the company during the course of the year, and how they could be added to the system and the randomizer also give them two dates for testing.  Would be nice to also have a status and or comment field to update based on if someone was tested.  Note:  This application will need to run each year across the employee pool and give everyone two dates for testing.  Any guidance on how to setup something like this appreciated.

[madlad]

Hi!

It's tricky to give specifics on this, but some good starting ideas could be:

• Keep a list of all employees
• Keep a list of all the dates or date ranges when you'll be testing. These could be defined in-app with the date picker control
• Keep track of who's already been tested, either using a column on an existing list, or a new list could be a number( 1 or 2), a list of tests and test results, or something else entirely.
• For  every date or date range, select however many random employees, who haven't been tested twice yet, that you need. This could be done using the randbetween function and looping until you have your ideal amount of people

You could use an app to create & view these schedules, and a power automate flow to notify the employees of the tests. If it doesn't need to be viewed/edited more than once a year, you may not even need an app, just power automate flow(s)

But these are just the first ideas I had. Really the best way to approach this depends on the specifics of your scenario.

Hope this helps! 🙂

[Pstork1]

The biggest issue with your design is that if it is truly random there is no way to guarantee that each employee has two testing dates a year.  Its easy enough to do a random number generator to pick a random candidate.

Having said that I think it would be better to use a Power Automate flow to create the actual schedule and save it to a data source. Then have the Power App display the information in the data source.  To have a flow do it you would do the following

1) Create an array of all employees

2) Create an array of dates (leave out weekends and holidays)

3) Loop through the dates using an Apply to each loop. Generate a random number between 1 and the number of employees in the array. Add that employee to that date. Update the employee array. If an employee has been selected twice remove them from the array. Do the same again till you've chosen 4 employees for that day.

As you select employees the employee array will decrease in size.  You will keep selecting employees from that array until there are no employees left. You will then have a random schedule where each employee has two dates.

[joshieboy]

Thanks all @madlad @Pstork1 for the feedback.  Will take the above on board and get back to you if any issues.

Regards,