Assigning Reps to a Sharepoint List

[tstaben]

Hello,
 
Our company is beginning to use SharePoint and Flow, and I was asked to add a bit of automation for one of our departments:
 
Current Setup: One of our departments uses a SharePoint list to assign open quotes for reps to complete. We have a separate flow currently that emails reps when they get assigned a task
 
Desired Addition: At the end of the day they want to automatically assign any open and unassigned tasks to reps for them to complete the following day. The assignment of tasks will based on having certain reps getting tasks first. Say we have RepA, RepB, and RepC. If 4 quotes are unassigned, RepA gets 2, the rest get one
 
Where I'm at with this:
To accomplish the distribution of assignment, my best idea at this point is create a separate SharePoint list that has each rep assigned with a Weight.
 
I can successfully use Get Items on the Quote list to grab all tasks that are Open, Unassigned, and sort by the due date. I can also use the Get Items to get the rep list and sort by the weight.
 
This is where I'm stuck on the logic of now going about assigning reps to the task in order:

One thought I had was making two arrays: OpenQuotes, Reps. When I use Get Items on the Quote list, I do an "Apply to Each" control -> Compose -> Append to Array". Right now I only have the Compose grapping the ID of the SharePoint item since at least my approach is SharePoint is already sorted in the order I want, so I can just go through sequentially without needing other fields

Then my thought was using a Do Until control, where the condition is the length of the array Reps needs to be equal to the length of array Open Quotes. During the loop it would just do a Get Items on my Rep SharePoint list, and for each item add that to the Reps array
 
This doesn't quite work however and in my tests I have results where even with only 3 open quotes, it loops about 60 times. My best guess is it doesn't evaluate the condition until the loop is done. So say you have 3 open quotes, 2 reps:

First Pass: Length of quote array is 3, rep array is 2

Second Pass: Length of quote array is 3, rep array is 4 

Basically this comes down to me not having a real programming/development background. I'm not sure what the best approach is with the logic of having two lists of different sizes, and populating one field off one of the lists with values off the other in a sequential fashion.
 
Any help would be greatly appreciated and I'm open to suggestions on how to change the method of assigning the reps. Using a SharePoint list for the weighting at least made sense to me in that it could easily be modified later without going into the flow itself.
 

Thank you

[tstaben]

If anyone happens across this with a similar requirement I did figure it out (whether or not it may be the best way to do it is another question).

What I ended up doing in my flow was:

• Initialize array variable arrOpenQuotes
• Initialize array variable arrReps
• Get Sharepoint Items -> Open Quotes (Get from SharePoint list of the Quote Queue).
  • Filter Query where Assigned To eq null and Completed eq 'false'
  • Order By Due Date
• Apply to each (output = value from Get Items step)
  • Compose
  • Append to array variable: Append the output of Compose to arrOpenQuotes
• Do Until length(variables('arrReps')) is greater than or equal to length(variables('arrOpenQuotes') ----This is one step I was messing up on. This way it will stop as soon as the list of reps is at least the same as what is needed
  • Get SharePoint Items - Reps (Get Items from Quote Reps list, which is just Title/Rep (object)/Weight (number)
    • Order By Weight
  • Apply to Each (output from Get Item step above)
    • 
    • Append to Array Variable: Append the output of Compose to arrReps
• Initialize Variable
  • Name: IndexCounter
  • Type: Int
  • Value: 0
• Do Until IndexCounter is equal to length(variables('arrOpenQuotes'))
  • Update SharePoint Item
    • ID field was set to use expression: variables('arrOpenQuotes')[variables('IndexCounter')]?['ID']
    • Same with Title/Due Date subbing out the ['ID'] part with ['Title']/['DueDate']
    • Assigned to Claims: variables('arrReps')[variables('IndexCounter')]?['REP']
  • Increment Variable
    • Name: IndexCounter
    • Value: 1

The Do Until loop will just iterate through arrOpenQuotes and assign the reps. Since both arrays are already in the order of the reps I want to assign I realized I could just use the same number on the array element from each (or however to properly describe that).

So again maybe there is a much better way to do it and it's simple logic, but it was still pretty cool to finally figure out

[v-alzhan-msft · Accepted solution]

Hi @tstaben ,

Glad that the problem is solved and the solution is perfect.

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.