Forum Discussion
XavierFan
5 years agoQrew Cadet
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.
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.