Discussions

 View Only
  • 1.  Assigning User based on # of assigned records

    Posted 10-23-2019 14:25
    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
    ------------------------------


  • 2.  RE: Assigning User based on # of assigned records

    Posted 10-23-2019 17:39
    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
    ------------------------------



  • 3.  RE: Assigning User based on # of assigned records

    Posted 10-23-2019 17:47
    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
    ------------------------------



  • 4.  RE: Assigning User based on # of assigned records

    Posted 10-24-2019 12:54

    How are the claims added? From an Add button one at a time or do you import multiple claims at once?



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



  • 5.  RE: Assigning User based on # of assigned records

    Posted 10-24-2019 12:58
    For sake of this argument and to make it as easy as possible lets say I add a new record one at a time.

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



  • 6.  RE: Assigning User based on # of assigned records

    Posted 10-24-2019 14:06
    Edited by Everett Patterson 10-24-2019 14:07

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



  • 7.  RE: Assigning User based on # of assigned records

    Posted 10-24-2019 16:59
    This is great thank you.  I ended up figuring out a solution but it was not as elegant as this...I ended up with an extra table that I didn't need using a Max record ID summary on the one record to get the User into the table...but then did the automation just like you showed above...seems to be working great.  Thanks again for your help.

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



  • 8.  RE: Assigning User based on # of assigned records

    Posted 10-23-2019 23:27
    Gary, 

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



  • 9.  RE: Assigning User based on # of assigned records

    Posted 10-24-2019 07:50
    Gary,

    Have you looked at Triggers from Juiced Tech?

    https://www.juicedtech.com/triggers

    It is inexpensive and very powerful.   I have all my clients sign up for it.  They have a Search feature in it that allows you look for records with certain criteria.  You would build a Formula Field that would identify Mary as the User with the fewest assigned Claims.  The Trigger would then find her record and can use that information to assign the next claim to her.


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------