Auto Assign Button

re: "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.

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

• 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.

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.

• 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.

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

• (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 Table

• 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.

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