Answered

Random Record Selector

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

Easy part:

I have a SQL table that contains a list of audits for certain processes in our company. There are 50 different audits. I would like for the user to open the app, press a button and the app tell them which audit to complete by random selection (1-50).

More complicated part:

If a number (audit) has already been assigned to a different user, the random number generator will run until it hits a number that has not already been assigned.

Summary:

I need a random generator that works between a specified range and will not land on the same number more than once.

My thought process on a solution:

These audits will be completed once a week. So I can create a SQL table to store all assigned audits (numbers) as the audits are submitted. Every week, run a SQL job to clear this table out. Use a lookup function to see if the number from the rand function exists in the table. If it does, keep running the rand function until it doesn't.

I have no clue how apply a range to the rand function and I have no clue how to build a loop like this? Maybe someone has a better approach.

Thank you,

-Justin

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

mdevaney 29,991 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@justair007
To get a random record you can use the Shuffle function.

Set(varRandomRecord, (First(Shuffle(your_datasource_name)))

Then to track which audits have already been distributed create a Yes/No column in SharePoint called Issued. Then put this code in the button where you accept the survey to mark it as Issued.

Patch(your_datasource_name, ID=varRandomRecord.ID, Issued=true)

Clear out the issued column at the end of the week using a Flow.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney that is extremely helpful. But how do I prevent the same record from being selected when the Shuffle function runs again? Wouldn't I have to reference the "Issued" column somehow?

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@justair007

You are 100% correct. Here's how.

Set(varRandomRecord, (First(Shuffle(Filter(your_datasource_name, Issued=false))))

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney ahhh of course 🙂 Thank you! So much easier than what I was thinking.

Was this reply helpful? Yes No