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

8 Replies

Related Content

Assigning reference numbers to records

Quickly Update Asset Assignments

Assign related record in other table

capture date when user is assigned

Create Linked Assignments from Multi-Select Field

Recent Discussions

'Is Set' in Pipelines

Share your feedback on the new Qrew Discussions

When the Expiration Date is On or Before Today, Change the Contract Status to Expired

Forms help

Empower Next Week!