1

I have a list in SharePoint online where I want to filter on several columns and then select a specific number of random items that meet the filter criteria. Is this possible?

For example, I may have 3000 items in my list, but I want to choose some from the ones who have Status = Approved and School = Central High School. After I filter on the Status and School columns, I may have 75 list items. From those items, I need to define the number to be selected (let's say in this case it was 50), and I'd like SharePoint to do a random selection of 50 items (from the 75 who met the filter criteria, out of the 3000 in the list). Ideally, I'd then like to use Flow to change the Status value for those list items, to change them from Approved to Placed.

I have a list where I'll need to make multiple draws, where there may be 7-10 fields used to filter the list first.

My first thought was to do something manually (filter the list items, then have a "DrawNumber" column where I could assign each item a sequential number, then use a standalone RNG to pull from those numbers before manually reconciling the SharePoint list items to the RNG selections), but it will be time-consuming, so if I can automate it I'd prefer that.

If needed, I could create a view for each permutation and build some kind of flow around that, but what I'd love to do is be able to dynamically filter my results in the browser, choose the number of items to be picked, apply my "picker", and then apply a flow to those items.

Another alternative that wouldn't be too bad would be to generate the set of numbers in a standalone RNG if I could somehow paste them in (or otherwise access them) for the DrawNumber column filter.

So I could go to https://numbergenerator.org/ and set it to pull 50 numbers between 1 and 75, and then open that set of numbers in Excel:

Set of random numbers

But then, in my SharePoint list, the only way I see to choose the filter values is to click on each individual box (this screenshot shows ID, but I'd want to use my DrawNumber column):

SharePoint filter pane

Even if I create a view and try to paste in the numbers for the filter value, it only seems to accept a single number at a time (not a set of comma or space-delimited numbers). If I enter "1 2", I get an error telling me that's not a number. If I enter "1,2", like this...

filter criteria

... it removes the comma and treats it like "12".

Any suggestions on how to streamline this process?

1 Answer 1

0

I figured out a way to do this. It's not perfect (there are still manual steps) but it's going to save me a lot of time. Here are the steps. I'm sure there are ways to automate more steps and clean up the process, but this is as far as I took it.

There are three main phases:

  • Populate the draw number for eligible list items manually using Excel and SharePoint
  • Use NumberGenerator.org and notepad to build the list of selected (drawn) items
  • Use Flow to update your SharePoint list based on the list of selected items

Here are the steps for phase 1 (populate the draw number in SharePoint)

  1. Add a DrawNumber column (number) to the list I want to draw from
  2. Switch over to Classic Experience (it won't work in Modern Experience)
  3. Filter on whatever columns are necessary so that only eligible items are displayed
  4. Select all visible list items to get a count of how many there are (or copy all visible list items and paste into Excel to see how many rows there are)
  5. In Excel, populate a column numbered sequentially (I start with 1 in A1, 2 in A2, then select them both and drag down as far as I need to fill the column). I make sure that I have at least as many rows in my Excel column as I have visible items in my SharePoint list
  6. Copy exactly as many rows from Excel as I have visible items in your SharePoint list
  7. In SharePoint, click "edit this list" to edit in datasheet view
  8. Select the DrawNumber field in my first visible row in SharePoint and then paste in the values I just copied from Excel. This will populate a DrawNumber for my eligible list items, but not add a DrawNumber value to any items I filtered out. I can test this by removing my filters to view all items in the list again.

Here are the steps for phase 2 (build the set of selected/drawn items)

  1. Go to https://numbergenerator.org/randomnumbergenerator/ and enter the number of items I want to draw out of the total number of eligible items I have. So, to draw three numbers out of eleven possible values, it would look like this: random draw
  2. Copy the values generated to my clipboard
  3. Open a notepad file. Add one space as the first character, and then paste the random numbers I copied after that
  4. In Notepad, I use CTRL-H to access the Replace function. In Find What, I enter a single space. In Replace With, I enter (without quotes) " DrawNumber eq " and then click Replace All Notepad find and replace
  5. Remove the first space in my notepad file, then copy the rest of it to my clipboard enter image description here

Here are the steps for phase 3 (use Flow to update SharePoint based on the randomly-selected items)

  1. Create a manually-triggered flow and add the Get Items action. Point it to my SharePoint site and list, and click Advanced Options so that I can paste the text from Notepad into the Filter Query field. This is where flow will only update the randomly-selected items Get Items with Filter Query
  2. Add an Update Item action - Flow will automatically put it into an Apply to Each container, after which I choose to apply it to each value (dynamic content) from the previous step. I choose here how I want my selected items to be modified in my list. In this case, I'm updating the Request Status Value to now be Approved enter image description here
  3. I save and then launch my flow, which updates my SharePoint list to change the randomly-drawn items to have an Approved value for my Request Status field, leaving all of the other items in the list untouched

Wrapping Up I could add additional steps to my flow as needed, for example I could add a field in my SharePoint list to log the results for items that were selected in the draw, and store the draw date and time as part of my Update Item action. Or, after my Apply to Each container I could add another action to select all of my list items and delete the values in the DrawNumber field so I'm ready to do another draw, if needed.

To do subsequent draws I would erase any DrawNumber values from the prior draw and then follow the same process, except that I wouldn't need to create the DrawNumber column each time (I would just use the column that I already created for the first draw) or build the flow each time (I would just need to update my Filter Query field in my Get Items action for each fresh set of random numbers I generate).

While it's not just plug-n-play, it's a significant step forward, since I'm going to be doing up to 80 draws on my list using different filter criteria for each one. Previously, the process was to use a printed piece of paper for each list item (about 2.5 thousand) and sorting them based on each filter criteria permutation, and doing a physical draw for each round, so up to 80 individual draws. After each draw, any items that were not selected would need to be integrated into the remaining eligible items for the chance to be selected in one of the remaining draws. As a result, this will turn a multi-day process involving several people to a single-day task that can be completed by one person, with a far lower risk of error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.