Anyone good with Array, SharePoint Lists and Random Numbers?

[Winlou]

So I think this is possible,  I'm just having trouble forming it in my head.  

So the User is going to give me a number (whole) and I'd like to create an array of random numbers based on it.  Then user those numbers to select items from a list/array of SharePoint items.

**Example**

Out of possible 200 items

User enters                                  3 

Random Array  generated           {5,62,122)

Array of SharePoint 'Get Items"  {5th item of SP Array, 62nd item of SP Array, 122nd item of SP Array}

Any help or advice welcome.  

Thank You, 

L

[edgonzales]

@Winlou 

This one is fun.  There are a couple of ways to go about this, here's what I did:

• Whatever your trigger is that gives you the number of things to get (I used a manual trigger with a numeric input)
• Initialize an Integer variable - I called mine "varCount" and set it to 0.  I'm going to use this to count through the iterations until it equals the number in the trigger.
• Initialize an array variable - I called this one "varSPItems".   (sidenote:  Do you need a separate array for just the numbers?  Or is it ok if that's included in the SP array?)
• Next, do a Do Until loop.  If you're not familiar with these, they will keep doing whatever actions are in the box, until your designated criteria is met.  Our criteria will have the Integer variable on the left, then "is equal to", then the number that is passed from your trigger.
• Inside the loop, use the SharePoint Get Item, choose your site and list, and for the ID, use the following expression: rand(0,200) - this will grab a random item (including the ID number) from your list.  If you're not familiar with using expressions, let me know.
• Also inside the loop, but after the SharePoint step, is an "Append to Array Variable" step where you select your array variable, and then add the SP columns that you want.  You can structure that as JSON depending on what you want to do with the items later.

• Next step (still inside the loop) is to Increment a Variable to add 1 to your varCount variable.

Then, outside of the loop, you'll have your array of things to do whatever you want with it.

There are things we can do to make it more 'robust' (make sure you don't get duplicate ID's, stuff like that), but I figured I'd see how you liked this idea before going too far down that rabbit hole.  Here are some pics:

Keep us posted.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[v-bacao-msft · Accepted solution]

Hi @Winlou ,

Please try the following configuration:

Best Regards,

[Winlou]

Thank you @edgonzales for your reply!

I really like this it's cleaner than what I've come up with to do the job.

I have a few questions around the limits the rand function and the do-until.

I'm expecting that my list to grow to around 5000 items maybe more. I'm now using 4 lists but that is out of scope for the question.

Can the rand function be prevented from generating the same number twice?  (this will be picking email address to send to.)

Also is the rand function reading the actual ID from the List?  I'm expecting the item ID and the total item count of the list to not match up over time.

Regarding the Do-Until, can it loop up to 5000 times?

Thank you 

[Winlou]

@v-bacao-msft 

Thank you for your response.

Is the Rand function selecting the items in the order, or is it selecting from the list id?

[edgonzales]

@Winlou 

Hi there...lots to unpack:

• 5000 items in the list shouldn't be a problem for the rand() function.
• It sounds like you'll be adding/deleting things from the SP list outside of this process, correct?  (I'm guessing that's why the ID's will get out of sync with the row count?)
• Would there be an instance where someone asked essentially for the entire list?  (Trying to understand why the Do Until would need to loop 5000 times).
• By using a combination of what I gave, and what @v-bacao-msft gave, you can avoid duplicates in the array (Mine won't do that, but Barry's condition will do that).  If your user asks for a large amount of random numbers, though, it may take a while to process because it's creating a random number, checking to see if it already created that one, and then cycling through.  As more numbers are added, that process will take longer and longer.  If you are expecting large amounts of random numbers, it might help us to understand exactly what it is that you're trying to do in case there is a more effective way to approach this.
• Given the new information about the ID's being out of sync with the row count, it might be better to grab the whole collection as an array (Get Items) and then using something like this technique to grab a random item from the array:  Expression Interlude - Random Item from Array in Power Automate

You'll still want to incorporate Barry's condition to avoid duplicates.

Keep us posted.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[v-bacao-msft]

Hi @Winlou ,

It is expected that the Rand() function returns the item ID. 200 represents the number of items contained in the list is less than 200. You can change this number to the value you need.

Then we can filter the items by the value returned by the Rand() function.

Do until is used to get the number of items specified by the user from 1-200. If the number specified by the user is 3, then Do until need only loop 3 times. This has nothing to do with the number of items contained in the List.

You can remove 60 from the Do until limits.

Best Regards,

[Winlou]

@edgonzales 

Basically, this list will be used to send emails to people randomly.  

Yes, I experienced the same slow processing as it tried to figure out which number was available.

Also, yes items will be added and/or removed from this list on an ongoing basis.

I like the idea of grabbing the whole array and selecting a set of random items as mentioned in the link you shared.

Right now I'm setting a random number in one of the list columns and then sorting the list by it and number that increments every time they are sent an email. 

Thank you all for your time and efforts.

If anyone looking at this in the future I think the solution lies in the Expression Interlude and @v-bacao-msft's suggestion.  

[RobElliott]

@Winlou you asked whether the rand  function can be prevented from generating the same number twice. Just be aware that it then isn't technically a random number as a pure random number will always have the ability to return a value more than once 🙂 

In case it adds anything, I did cookbook article on getting a random number from a list at https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-a-random-item-from-a-SharePoint-list... and we use this to get a random country with its flag, Wikipedia page and Bing map every day on our intranet. Just a bit of fun.

Rob
Los Gallardos