Implementing a Round-Robin in Power Automate

Guest post by Brian Kennemer

Thank you to April Dunnam!

I want to give a big thank you to April Dunnam for her assistance in helping us work through this Power Automate need. If you’ve ever searched the web to see how other people were doing round robin assignment in Power Automate, you would find April’s post on how to address this in Power Automate. She laid out a great way to do this using a SharePoint list to hold the ID of the last assigned user.

Our Use Case

Our customer needed us to build a client support system in PowerApps (and PowerAutomate) that would allow users to start a Teams chat with a Tier Two support person when the Tier One person did not know the answer. The solution had the following requirements.

• Allow the Tier Two team to dynamically define who was on duty
• Allow user records to be added/deleted from the On Duty list
• Ensure the Tier Two person was assigned in turn so that the same person would never get assigned to back-to-back chats
• Store all system data in SharePoint

At first glance, April’s solution seemed like it would work. After implementing it in the development system, I discovered our customer requirements were not design assumptions for April’s solution. For example, the customer needed to delete users from the user list and designate when a user was off duty.

I had a discussion with April about the design she outlined. The core issue was the queuing relied on a static and sequential (without gaps) set of item ID values. If you had 5 records and deleted 1 and then added a new one the process broke when the counter got to the ID of the deleted record. After talking with her about some possibilities, we formulated a solution that addresses our needs and changes the queuing to not use ID. I am documenting it here as a way of paying back the help and support received from the community.

The Power Automate Solution

Starting from April’s solution, I changed the basis of our queue to a new DateTime field, LastAssigned that was added to the Agent List. The agent assignment process now retrieves the first SharePoint item where the OnDuty field is Yes and has the earliest LastAssigned date to ensure maximum time since last assignment for all on duty agents.

The detailed process is as follows:

1. Use a SharePoint GetItems Action to get the TOP 1 items where the OnDuty field was equal to Yes but configure it to sort (ascending) by the LastAssigned field.
   1. The key to the solution is the sorting ascending.
   1. By sorting ascending on that date field we ensured that the Top1 item would always be the agent that was assigned the longest ago.
2. Assign the ID of that Top1 item to a variable
3. Use that variable in a SharePoint GetItem action to get the record for the TOP1 item
   1. Yes, you don’t HAVE to do this but since the Get Items action creates a collection (even if you only return 1 item), any time you want to use that item later in the flow it puts it into a ‘for each’
   1. We were doing enough things with it that we didn’t want to clutter up our flow so we just put it into a variable.
4. <Do stuff with that item>
5. Use an Update Item action to set the LastAssigned field for the item to be utcNow

To enable a user to be an on-duty agent to be assigned incoming chats:

• If they are new, you enter a record for each new agent and change OnDuty to Yes
• If they are existing users, change OnDuty to Yes.

Setting Up the Power Automate Solution

SharePoint List – Support Calls Intake List

This list is our trigger list. It is not needed for the round robin to work but it is used to illustrate the round robin.

Create a list with Title, Description and a Person field to use for assigning the agent.

SharePoint List – Support Agents List

This list stores the names of those agents that can be assigned incoming support tickets. Create the list as follows.

• Agent field that was a Person type field. Do not allow multiple entries.
• Agent On Duty as a Yes\No field
• Last Assigned as a DateTime field that defaults to a calculated value of now().

PowerAutomate Flow

Here is the high-level view of the flow. We will look at each action in turn below.

Hot Tip: I like to put the Action type in parentheses at the end of my renamed actions so that if the show up in screen shots or videos the user has an easier time knowing what kind of action it is. I will never forget the first time I tried to build a flow from a blog post and all of the actions had been renamed and I couldn’t figure out what actions to add.

New Support Item Created (Trigger)

In the first step, we are triggering the flow based on a new item created in SharePoint.

Agent ID (Initialize Variable)

We need a place to store the ID value of the Top1 Agent below so we initialize a variable for it.

Get Top 1 On Duty Agent (Get Items)

This steps

• Filters the Agents list where Agent On Duty ne ‘Yes’
• Sorts the Last Assigned date in ascending order
• Retrieves the Top1 records from the Agents list so that the agent can be assigned the next chat

This is a SharePoint Get Items action pointing at my SharePoint list that contains the names and the statuses of the Agents.

A few key points.

1. I had some issues with the Filter Query where using “eq ‘No’ “ didn’t work. Not sure why but I got it to work by going the other diection and using “ne ‘Yes’ “
2. Make sure the Order By is Ascending. This makes it so that the ‘top’ is the oldest records
3. Top Count needs to be 1. This is SUPER important as we don’t want this to return more than 1 record or the whole thing sort of stops working correctly.

Hot Tip: When creating your lists (if you are starting from scratch): Create your SharePoint fields with no spaces in them and then go back and put a space in.

Once SharePoint creates a field with a give name, it will forever refer to the first name you ever gave the column. For example, if you rename the Title field in a list to “Boo”, SharePoint will still use ‘Title’ to refer to the column. Because I didn’t do this initially, I had to put the “_x0020_” in my field names for the Filter Query and Order By fields where the names had spaces.

Loop to Update the Set AgentID (For Each)

Even though our Get Items action above only returned 1 record, PowerAutomate sees the returned data as a SET of records that just happens to contain 1 record. That means that any time we reference the output of that action, PowerAutomate will put it into a loop, in the event the collection has several records. To make it easier on ourselves, the loop value is loaded into the AgentID variable.

To set the variable value, add a Set Variable action and pick AgentID for the Name. Then for the value add the dynamic content for the ID from “Get Top 1 On Duty Agent (Get Items)” and PowerAutomate will setup this loop for you.

Get the Agent Info (Get Item)

This is a SharePoint Get Item action pointing at the Support Agents list. It is using the AgentID variable to go get the single record for the Top1 agent that was returned by our Get Items action above. This way when you have to do something with this Agent ID value later Power Automate will not put it in a loop.

Update the Assigned To Field for the support Item (Update Item)

This is the place where we actually do stuff with the agent provided by the round robin process. We take the Agent info from the Get Item action above and we update the Assigned Agent field in the Intake list.

Here we use the ID and Title fields from the Trigger in the ID and Title fields for this item update since we are updating the same item. Then for the Assigned Agent Claims field in the action we use the Agent Claims field from the Get Item action above. The result is that the trigger item will have its Assigned Agent field updated with the user info for the Agent that was assigned.

Update the Last Assigned Field for the Assigned Agent (Update Item)

This is a key part because without this updating to the Agent record they would be assigned the next time the flow ran. This action is about setting the Last Assigned field to be the current date and time so it will put the agent at the bottom of the queue.

Here we use the AgentID variable to update the agent we just assigned. Then we are updating the Last Assigned field to be the formula utcNow().

Conclusion

Hopefully, you find this round robin framework useful where you can perform flexible assignments using SharePoint lists. In our case, it was support agents but I see many possibilities for other uses here.