Get an Array of random results from a SharePoint List.

[Hamillkw]

I have a SharePoint List where I need to get a number of items and then mark them as selected.  After reading a couple of articles on how to do this I settled on a method that seems to reliably produce a random item just not a different item each time. the problem seems to be that the filter array is not doing its job. From a very small pool of 4 items, I'm asking for 3. About half the time I get two of one or all three of them the same.  when I look at the output the Filter Array isn't taking out the selected item (the Get Item Output) in the Do until loop. I am using the ID field as the identifier for the item.  Is it possible that the SharePoint list ID field is just the wrong field?

Thank you in advance for your assistance

Do Until Loop

[Expiscornovus]

Hi @Hamillkw,

Out of interest, do you always want to use 3 of the 4 items?

If that is the case can't you exclude one random item instead? Safes you the trouble of keeping track which items already have been picked 😁

[grantjenkins · Accepted solution]

If I'm understanding what you're after, this is how I would build the flow.

For this example, I'm using the following SharePoint List where I want to get a certain number of random items.

See full flow below. I'll go into each of the actions.

Manually trigger a flow has a single Number input. This is the number of random items I want to return. If the number of items you wanted was always the same, then you could skip having an input here.

Get items returns all the items from the SharePoint list.

Select uses the output from Get items and Maps the ID field and another custom field called Order that uses the rand function to generate a random number for each item. The expression used is below.

rand(1, 1000)

This would output something like that below which includes the ID for each item, and a random number between 1 and 1000.

Select IDs uses the output from Select, but firstly, sorts it by the Order property (our random number), then takes N number of items. In this example, N is the number we entered in our trigger. The expressions used here are:

//From
take(sort(body('Select'), 'Order'), triggerBody()['number'])

//Map
item()?['ID']


//Note that if it was always the same number of random items you wanted, then you could hardcode it as so, where it takes the first 3 items.
take(sort(body('Select'), 'Order'), 3)

If we had entered 3 as our trigger input, then we would end up with a simple array of 3 random IDs.

Lastly, our Filter array will use the output from our Get items and use the output from Select IDs in the filter. Effectively, the filter will be where the array of IDs contains the ID of the list item.

Our Filter array would now contain N number of random items from our SharePoint List. You could then use an Apply to each to iterate over the Filter array and do whatever you need to.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[Hamillkw]

That's an Interesting idea but sadly no. I expect to in the end to get 3-9 selections from pools of approximately 100. 

[Hamillkw]

Thanks for your idea,

This Solution is certainly interesting, however, Power Automate insists on wrapping Select ID in an Apply to Each. It gives the following error if I don't: 

 'InvalidTemplate' and message 'The template validation failed: 'The repetition action(s) 'Apply_to_each' referenced by 'inputs' in action 'Select_ID' are not defined in the template.'.'.

If I do this though, the Filter Array contains no output. 

Perhaps I'm not applying the solution correctly?

---

[grantjenkins]

@Hamillkw Are you able to show the design of your flow including what you've put as inputs, etc?

[Hamillkw]

Thank you Grant,

I copied your solution and attached the SharePoint List I'm using and applied an update item at the end. as you can see Power Automate wants to wrap the Select ID  in an Apply to Each. 
 Am I misunderstanding something? is the Select ID not a Select Data Operation? 

[grantjenkins]

Can you show what your inputs are for your Select ID? That's where the issue is as it looks like you're trying to use something in there from Get items but shouldn't be.

The input would be as follows:

//From
take(sort(body('Select'), 'Order'), triggerBody()['number'])

//Map
item()?['ID']


//Note that if it was always the same number of random items you wanted, then you could hardcode it as so, where it takes the first 3 items.
take(sort(body('Select'), 'Order'), 3)

[Hamillkw]

thank you Grant,

you are correct I applied the solution incorrectly, I have tested it several times and not gotten the same ID twice in one selection. I will test it on a larger pool and let you know.