I need to create a drop down on a form and have the user select what team they want to assign to and then QuickBase auto assign to a member on that team in order. So employee 1 first then employee 2 and so on until it gets to the last employee and then it starts back on the first employee and continues.
I have no clue if this could happen or how to.
Thanks,
Carol
Qrew Legendre: "I have no clue if this could happen or how to." This is Quickbase, so for sure its possible!
I have some ideas. But first can you tell me if you have a table of Employees and a flag to say if they are active? Or is this a down and dirty user drop down field.
Yes, I already have a employee table. I don't have an active or inactive field in that table, but I could add one.
Qrew LegendOK, here is an idea.
Make a numeric field on that Employee Table called Round Robin #. Make it Unique. Assign Each employee eligible for assignment with a number starting at 1, in sequence.
You will need a Helper Table called # Active Employees with a single record in it ([Record ID#] = 1) and then make a relationship down to the "Ticket's" record with a formula field on the Ticket's table with a formula of 1 to connect it to the Tickets. Also Make a similar relationship to Employees.
Make a summary field on # Active Employees table to count the number of Active employees. Look that up down to Tickets.
(note you can avoid the helper table buy using a Formula Query formula to count up the number of Active Employees).
OK, so have all the ingredients now.
On your form, make a calculated field for the Assigned Employee Sequence #.
The formula will be REM([Record ID#], [Total number of active employees] +1)
Now will see that there is a riound robin number assigned to each Ticket.
Lastly you will need a Pipeline to trigger when a Ticket is created and the assigned employee is blank. It will search the Employyes table looking for the calculated Sequence number on the Ticket (limit to 1 record in the search), and then assign that employee to the ticket.
Another way to go on this would be a dedicated table for a round robin candidates where the key field of the table is that sequence number. If you did that, then you could directly look up the Round Robin employee from that table down into your tickets with a form rule.
Feel free to post back if you want some more help from this. These are just some ideas that come to mind but there's definitely a way to do this.
Hi Carol,
I worked up a solution in my builder account that might solve your use case.
First, I created three tables and related them as follows using default key fields for each table:
Teams<Task
Teams<Employees
Employees<Task
Within the relationships I created fields that will be used to determine the current task assignee for each team and calculate the next assignee up:
Teams Table:
Employees: Summary : # of Employees
Task: Summary: Maximum Team Task RID
Task: Summary: Maximum Related Employee Team Number
Tasks Table
Team: Lookup: Maximum Team Task RID
(NOTE:Maximum Team Task Record ID is PASSED BACK to task records for matching the Maximum Related Employee Team Number)
Employee: Lookup: Employee – Team Number
Additional fields added/modified for the solution:
Teams Table
nextTeamMemberUp : Formula, numeric.
var number memberCount = [# of Employee records]; var number lastAssigned = [Maximum Related Employee Team Number]; var bool backtoStart = [# of Employee records] = [Maximum Related Employee Team Number]; If($backtoStart, 1, $lastAssigned + 1)modified the Add Employee button to insert the next up Team Number.
var number teamNumber = [# of Employee records] + 1; URLRoot() & "db/" & [_DBID_EMPLOYEES] & "?a=API_GenAddRecordForm&_fid_7=" & URLEncode ([Record ID#])& "&_fid_9=" & $teamNumber & "&z=" & Rurl()Employees Table
NOTE: The solution relies upon the number of employees on a team being equal to the largest assigned Team Number. For a team with 4 members, the maximum assigned team number must be 4.
When a team member is removed from a team, the Team Numbers of the remaining team members must be manually managed to ensure that each active team member has a UNIQUE team number greater than 0 and less than or equal to the number of team members.
Management / Reassignment of Team Numbers could be managed automatically via pipeline or scripted automation which is not in scope of this solution.
Form rule on the task form is used to populate the task assignee using the logic built into the relationship and formula fields described above.
In Options toggle off the rule to Fire “change” actions only when a condition changes from false to true. Since we are using a lookup field, there will not be a change when the form loads for tasks added via the team record.
Jeff Richey | Solutions Consultant
Website | LinkedIn | Knowledge Base
Hi Carol,
Please see the attached PDF which better illustrates the solution described below:
Create three tables and relate them as follows:
Teams<Task
Teams<Employees
Employees<Task
Within the relationships, create fields that will be used to determine the current task assignee for each team and calculate the next assignee up:
Teams Table:
Employees: Summary : # of Employees
Task: Summary: Maximum Team Task RID
Task: Summary: Maximum Related Employee Team Number
Tasks Table:
Team: Lookup: Maximum Team Task RID
(NOTE:Maximum Team Task Record ID is PASSED BACK to task records for matching the Maximum Related Employee Team Number) as shown in the screenshot for Maximum Related Employee Team Number.)
Employee: Lookup: Employee – Team Number
Additional fields added/modified for the solution:
Teams Table:
nextTeamMemberUp : Formula, numeric.
var number memberCount = [# of Employee records];
var number lastAssigned = [Maximum Related Employee Team Number];
var bool backtoStart = [# of Employee records] = [Maximum Related Employee Team Number];
If($backtoStart, 1, $lastAssigned + 1)
Modified the Add Employee button to insert the next up Team Number*.
var number teamNumber = [# of Employee records] + 1;
URLRoot() & "db/" & [_DBID_EMPLOYEES] & "?a=API_GenAddRecordForm&_fid_7=" & URLEncode ([Record ID#])&
"&_fid_9=" & $teamNumber &
"&z=" & Rurl()
Employees<Task
Numeric: Team Number: used in the calculation of who is up next.
Each Employee on the Team is automatically assigned a Team number when added via the Add Employee button.
When a team member is removed from a team, the Team Numbers of the remaining team members must be manually managed to ensure that each active team member has a UNIQUE team number greater than 0 and less than or equal to the number of team members. Management / Reassignment of Team Numbers could be managed automatically via pipeline or scripted automation which is not in scope of this solution.
Form rule on the task form is used to populate the task assignee using the logic built into the relationship and formula fields described above.
In Options toggle off the rule to Fire “change” actions only when a condition changes from false to true. Since we are using a lookup field, there will not be a change when the form loads for tasks added via the team record.
The attached pdf includes screenshots to better illustrate the solution.
The attached gif shows the solution in action on a form.
Jeff Richey | Solution Consultant
Website | LinkedIn | Knowledge Base
Create three tables and relate them as follows:
Teams<Task
Teams<Employees
Employees<Task
Within the relationships, create fields that will be used to determine the current task assignee for each team and calculate the next assignee up:
Teams Table:
Employees: Summary : # of Employees
Task: Summary: Maximum Team Task RID
Task: Summary: Maximum Related Employee Team Number
Tasks Table:
Team: Lookup: Maximum Team Task RID
(NOTE:Maximum Team Task Record ID is PASSED BACK to task records for matching the Maximum Related Employee Team Number) as shown in the screenshot for Maximum Related Employee Team Number.)
Employee: Lookup: Employee – Team Number
Additional fields added/modified for the solution:
Teams Table:
nextTeamMemberUp : Formula, numeric.
var number memberCount = [# of Employee records];
var number lastAssigned = [Maximum Related Employee Team Number];
var bool backtoStart = [# of Employee records] = [Maximum Related Employee Team Number];
If($backtoStart, 1, $lastAssigned + 1)
Modified the Add Employee button to insert the next up Team Number*.
var number teamNumber = [# of Employee records] + 1;
URLRoot() & "db/" & [_DBID_EMPLOYEES] & "?a=API_GenAddRecordForm&_fid_7=" & URLEncode ([Record ID#])&
"&_fid_9=" & $teamNumber &
"&z=" & Rurl()
Employees<Task
Team Number to be used in the calculation of who is up next.
Each Employee on the Team is automatically assigned a Team number when added via the Add Employee button
Manually added team members must be assigned a UNIQUE Team Number greater than 0 and less than or equal to the number of team members. (If a team has 4 members and 2 members are MANUALLY added, each of the new members must be assigned either team number 5 or 6)
NOTE: The solution relies upon the number of employees on a team being equal to the largest assigned Team Number. For a team with 4 members, the maximum assigned team number must be 4.
When a team member is removed from a team, the Team Numbers of the remaining team members must be manually managed to ensure that each active team member has a UNIQUE team number greater than 0 and less than or equal to the number of team members.
Management / Reassignment of Team Numbers could be managed automatically via pipeline or scripted automation which is not in scope of this solution.
Form rule on the task form is used to populate the task assignee using the logic built into the relationship and formula fields described above.
In Options toggle off the rule to Fire “change” actions only when a condition changes from false to true. Since we are using a lookup field, there will not be a change when the form loads for tasks added via the team record.
Hi Carol,
I worked up a solution that might fit your use case:
1. Create three tables and relate them as follows:
Teams<Task
Teams<Employees
Employees<Task
2. Within the relationships, create fields that will be used to determine the current task assignee for each team and calculate the next assignee up:
Teams Table:
Employees: Summary : # of Employees
Task: Summary: Maximum Team Task RID
Task: Summary: Maximum Related Employee Team Number
Tasks Table:
Team: Lookup: Maximum Team Task RID
Additional fields added/modified for the solution:
Teams Table:
nextTeamMemberUp : Formula, numeric.
var number memberCount = [# of Employee records];
var number lastAssigned = [Maximum Related Employee Team Number];
var bool backtoStart = [# of Employee records] = [Maximum Related Employee Team Number];
If($backtoStart, 1, $lastAssigned + 1)
Modified the Add Employee button to insert the next up Team Number*.
var number teamNumber = [# of Employee records] + 1;
URLRoot() & "db/" & [_DBID_EMPLOYEES] & "?a=API_GenAddRecordForm&_fid_7=" & URLEncode ([Record ID#])&
"&_fid_9=" & $teamNumber &
"&z=" & Rurl()
Employees Table
NOTE: The solution relies upon the number of employees on a team being equal to the largest assigned Team Number. For a team with 4 members, the maximum assigned team number must be 4.
When a team member is removed from a team, the Team Numbers of the remaining team members must be manually managed to ensure that each active team member has a UNIQUE team number greater than 0 and less than or equal to the number of team members.
Management / Reassignment of Team Numbers could be managed automatically via pipeline or scripted automation which is not in scope of this solution.
3. A Form rule on the task form is used to populate the task assignee using the logic built into the relationship and formula fields described above.
In Options toggle off the rule to Fire “change” actions only when a condition changes from false to true. Since we are using a lookup field, there will not be a change when the form loads for tasks added via the team record.
I have attached a gif which shows the solution in action on a form and a pdf document which contains screenshots to better illustrate the solution components.
Jeff Richey | Solutions Consultant
Website | LinkedIn | Knowledge Base
