Assign 1 of 4 users to a specific Record type? Make it even/fair?

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
Okay so I have a team of 4 that pick up specific record types categorized by a drop down. 

I want to apply an even split to the 4.

For instance the record type (category) might be "complaints". We get lots and lots of comments and feedback, but I only want to distribute the "complaints" evenly among the team of 4. 

I have a MOD() formula in place but it isn't going to be perfect because it is based on Record ID. Is there a way to accomplish what I am trying to do?

My current formula for reference
If( 

[Feedback Type]="Complaint" and Mod([Record ID#],ToNumber([Number of people])) = 0, ToUser("person1@companyemail.com"), 

[Feedback Type]="Complaint" and Mod([Record ID#],ToNumber([Number of people])) = 1, ToUser("person2@companyemail.com "), 

[Feedback Type]="Complaint" and Mod([Record ID#],ToNumber([Number of people])) = 2, ToUser("person3@companyemail.com "),

[Feedback Type]="Complaint" and Mod([Record ID#],ToNumber([Number of people])) = 3, ToUser("person4@companyemail.com ")

)
Photo of Evan

Evan

  • 312 Points 250 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
That should be perfect, what is the problem?

If you have some data in your app and run a summary report by Assignee, you will find that while the allocations may not be equal they will be very close to equal.
Photo of Evan

Evan

  • 312 Points 250 badge 2x thumb
It is random so everyone has a 1/4 chance of being assigned.
I was curious to know if there was a method for perfect split based on conditions, that's all. 

so instead of "close to equal", just "equal"

And thanks for your reply!
I've done something exactly like that in a similar situation and that solution is the one I employed. One thing to consider is that you probably want to re-sequence each day to reset the counts. Basically if Person 1 is just faster - and they do 10 more than person 2 each day - within a couple of days Person 2 is going to be pretty far behind.

An easy solution is to have a daily 'reset' almost - where you assign a number from 1 to n that lets you reset the assignments. So:

[Feedback Type]="Complaint" and 

Mod(if( [Resequence #] > 0, [Resequence #],[Record ID#]),ToNumber([Number of people]) ) = 0, 
if you've assigned a new Resequence # - use that number for your Mod calc, otherwise RID

ToUser("person1@companyemail.com")

Chayce Duncan | Director of Strategic Solutions
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base
(Edited)
Photo of Evan

Evan

  • 312 Points 250 badge 2x thumb
Okay but if I only get one complaint every few days, wouldn't it always assign to the first person on the list?
Is is that infrequent that they come in? if they only come in every couple of days - you could get creative using automations - and instead of having a formula - let the automation pick for you. Basically you could have a set up where you log the most recent person who was assigned in some kind of assignments module, and then use automations to store the next person up into the assignment field for the complaint. The thinking behind the first solution and a formulaic approach works well when you have a lot of traffic and need to assign them quickly and in bulk 

Chayce Duncan | Director of Strategic Solutions
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base
Photo of Xavier Fan

Xavier Fan, Champion

  • 820 Points 500 badge 2x thumb
Instead of using MOD - you can set up a "rotation" of your 4 users, so that a new Complaint goes to the "next" person in line.  This way, you can get an "exactly equal" distribution.

For example - I've done this for "sales rotations", where a new lead goes to the "next" sales rep, so that the all the sales reps get an equal share of the leads.

There are a number of ways to do this - here is an example using a parent table to:
- find the "most recent complaint assigned to" with summary / lookup fields
- use a formula in the parent table to figure out the "next complaint assigned to"
- lookup back down to the record so that you can assign to the "next complaint assigned to"

====
Tables

Parents
- parent table to the Records

Records
- child records
- [Category] = Text Multiple Choice field, with values = Feedback, Complaint, etc.
- [Assigned To] = User field
- [Assigned To - Email] - Formula Text field with formula:  UserToEmail([Assigned To])
====

1)  Create a summary field [Last Complaint RID] 
(Parents summarizing Records)

- maximum [Record ID#] (of Records) 
where [Category] is equal to the value "Complaint"

- this gives the Record ID# of the most recent Complaint

2)  Create a lookup field [Parent - Last Complaint RID] 
(Record looking up to Parent)

- this allows the Record to know the most recent Complaint RID

3)  Create a summary field [Last Complaint - Assigned To - Email]  
(Parents summarizing Records)
- combined text of [Assigned To - Email]
where [Category] is equal to the value "Complaint"
and [Record ID#] is equal to the value in the field [Parent - Last Complaint RID]

- now this means that the Parent knows the email address of the User who was assigned the last Complaint

4)  Create a formula field [Next Complaint Assigned To - Email]
(in the Parent table)
with the formula:

Case(
ToText([Last Complaint - Assigned To - Email]),

"person1@biz.com", "person2@biz.com;",
"person2@biz.com", "person3@biz.com",
"person3@biz.com", "person4@biz.com",
"person4@biz.com", "person1@biz.com"
)

- So this says that if the last person to get a Complaint was Person 1, then assign it to Person 2, and so on

5)  Create a lookup field [Parent - Next Complaint Assigned To - Email]  
(Record looking up to Parent)

- now the Record knows the email address of who the next Complaint should go to

6)  Create a Formula User field [Next Complaint Assigned To - User]
(in the Record table)
with the formula:

ToUser([Parent - Next Complaint Assigned To - Email])

- so now you have a user field that is the user to assign the next Complaint to

7)  Assign the user for a new Complaint 
(in the Record table)
- now you have the user that the next Complaint can go to - you can now assign this in a number of different ways, depending on your set up.

For example - you can use a form rule:

- When [Category] is equal to the value "Complaint"
change [Assigned To] to the value in the field [Next Complaint Assigned To - User]

or with an automation:

Trigger -
when a Record is added 
and [Category]  is equal to the value "Complaint"

Action - Modify Record
- where [Record ID#] is equal to the value Trigger: [Record ID#]
set [Assigned To] to the value in the field  [Next Complaint Assigned To - User]

etc.
(Edited)