Assigning User based on # of assigned records

Question

I have a table of Records ( Claims ) that I have been assigning a User to from a User table that tracks the number of records each User has assigned to them. I want to automatically assign the user who has the least number of records to the next record I create in the Claims table.

For Example
Mike 74 claims
Tom 73 claims
Mary 67 claims
Jane 87 claims

The next record I create in the claims table I would like to assign Mary.

Any thoughts or suggestions on doing this?

Thanks in advance.

------------------------------
Gary McDonald
------------------------------

everettpatterso · Answer

It takes some setup and managing, but you can use all native features. I will let you determine if the effort is worth it. It should begin to assign Users to Claims evenly as you add them. Please test thoroughly and enjoy!

Setup a new table, User Statistics. Create one record, record ID is 1.&nbsp;
Create a new relationship, each User Statistic has many Users

On the Users table, change the field Related User Statistic to a formula numeric and set the formula to 1.
Create a lookup field for each User, name it "John Total", "Mary Total", etc *keep updated for every User

On the User Statistics table, create a formula user field "Next User", *keep updated for every User

var number myformula = Min([John Total],[Mary Total]);

If(
$myformula=[John Total], ToUser("john@gmail.com"),
$myformula=[Mary Total], ToUser("mary@gmail.com"))​

Create a new relationship, each User Statistic has many Claims

On the Claims table, change the field Related User Statistic to a formula numeric and set the formula to 1.
Create a lookup field for the field "Next User"

Create a new automation, when a user adds a record in the Claims table, modify records (record ID = record ID) and change the field Related User to the Next User

------------------------------Everett Patterson------------------------------

everettpatterso · Answer

How many Users do you have to manage like this? What is the key field on the Users table?

I have a working solution if you just have a few Users, but it would take some setup (one table, two relationships, summary/lookup fields, couple formulas, and one amazing automation).

------------------------------
Everett Patterson
------------------------------

garymcdonald · Answer

There are 19 Users in the Table but only 7 of them are adjusters being assigned to claims.
The key field in that table is User ( it is a User field )

------------------------------
Gary McDonald
------------------------------

babipanjikar · Answer

Gary,&nbsp;I would be interested to see how this solution works. I have been looking for queue based solution to manage the workload automatically using native quickbase functionality. However, still scratching my brain.------------------------------Babi Panjikar------------------------------

donlarson · Answer

Gary,Have you looked at Triggers from Juiced Tech?https://www.juicedtech.com/triggersIt is inexpensive and very powerful.&nbsp; &nbsp;I have all my clients sign up for it.&nbsp; They have a Search feature in it that allows you look for records with certain criteria.&nbsp; You would build a Formula Field that would identify Mary as the User with the fewest assigned Claims.&nbsp; The Trigger would then find her record and can use that information to assign the next claim to her.------------------------------Don LarsonPaasporterWestlake OH------------------------------

Forum Discussion

Assigning User based on # of assigned records

Related Content

Field to "assign" a user group

Docusign Pipeline Assign Signer

Assign Child Record to Parent Record

Assigning reference numbers to records

capture date when user is assigned