cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justair007
Helper I
Helper I

Random Record Selector

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

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Super User III
Super User III

@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."

View solution in original post

4 REPLIES 4
mdevaney
Super User III
Super User III

@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."

View solution in original post

@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?

@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." 

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (2,950)