Select RANDOM record from excel, copy it to another table and remove it.

[Mike29f]

Hey Guys,

Right now I'm doing flow where it selects RANDOM record, adds it to another table and then remove it from original table. I want to create something like Winner list where users are typing their names inside one excel and then flow will randomly place them inside other table with "prizes". Every each user must be copied to this other list.

Problem is, that it can copy random records and then remove it from excel but it will have this record still inside "shuffle" list, so it can place one person 5 times and throw errors "There is not such record" inside excel file after second time.

I can't find a way, to remove this record also from "shuffle" list.

Compose:

body('List_rows_monday')?['value'][rand(0,length(body('List_rows_monday')?['User']))]

Don't worry about this error. Microsoft has some downtimes I think and OneDrive is not working right now.

Thanks,

Mike

[Koen5]

Dear Mike,

Your problems sounds to be fairly simple ... but it seems you did not explain what exactly is your 'shuffle' list. This makes any advice difficult.

FYI, based on what I have read, I would have solved your problem by creating an array from your list of potential candidates, generating a random number between 1-1000 (can be anything depending on the size of the array), then loop through the array continiously with a counter (every next record the counter increases), until the counter matches the random number. Then you have your random selection.

To make sure you do not get the same record twice, all results are saved in a seperate array and this array is checked against if a new result is created. If the record is already here, then start over with a new random number.

Hope it helps,

Happy flowing,

Koen

[Mike29f]

Thanks Koen5!

"FYI, based on what I have read, I would have solved your problem by creating an array from your list of potential candidates, generating a random number between 1-1000 (can be anything depending on the size of the array), then loop through the array continiously with a counter (every next record the counter increases), until the counter matches the random number. Then you have your random selection."

Yes, this is exactly what I did.

Shuffle list is just an array list loaded from excel table with names.

Problem is, that it randomize the output each time. If I try this solution then it will check if record was already placed BUT, it will also decrement the value of loops by 1(which depends on number of total records from excel table).

As far as I understand, I need something that will just remove record from existing array LOADED PREVIOUSLY FROM EXCEL (sorry for caps but it is very important).

Detailed info:

I need to copy all users from one excel table (lets call it "ONE") to another excel table ("TWO"). We have let's say 50 rows inside table "ONE" but 30 inside "TWO", so when there will be no more place for the rest of users, we will need to place them inside something like "THIRD' table. The number of users can be different each time and all users must placed somewhere(inside "TWO" or "THIRD" table).

Important! - Table "ONE" should be randomized to prevent "first come, first served" rule because, my users will start to sign-in on list as soon as it will be open so application will obviously crash.

Thanks,

Mike

[Koen5]

Dear Mike,

I think I know where your problem lies ... I think you try to create a random number which is within the available records range. Hence the need to amend the records.

I was thinking about a random number regardless the recordrange. For example you have 50 records and the random number is 348 (limited by 3 digits max). I would loop through all the 50 records continiously (using a Do Until construction). 

If it finds a record it will check if this record is not yet included in the results array and if not, then add this there.

At the end, when you have all your needed results ... just minus of the orginal array with the results array and you have the left-overs.

Hope this helps,

Happy flowing,

Koen

[Mike29f]

Hmmm, I'm not sure if this will be good solution to just loop throu array list much more times than the number of records. Automate has some kind of limitation so I don't want to suck all of it using one Flow. I don't know how much records will for each day so it is troublesome to specify the MAX value (now it can be 50, but half year later there can be 500).

Right now I have working array load from table "ONE", record placing inside table "TWO" and removing this record from excel table "ONE". What I need is just some way to remove this exact record which was placed from array list (in each loop).

Is it okay to just do compare "ONE" <> "TWO"? It will need to load table "TWO" in each loop so this will make it much more complicated than it should be.

Thanks,

Mike