Expand all | Collapse all

Automatic assignment of case to worker

  • 1.  Automatic assignment of case to worker

    Posted 03-29-2017 14:10
    We're designing a system in which a new case will be entered into the system, and the functionality we're looking for is automatic assignment to a worker, based on a rotating basis. I'm not even sure how to start such functionality and would love if anyone has any ideas. Thanks!

  • 2.  RE: Automatic assignment of case to worker

    Posted 03-29-2017 14:16

    Are specific Cases established, or is that a free text entry field? If it's the former, it sounds like a related parent table with lookups brought into the child is in order here, so far...

    The parent table would consist of records of Cases with an associated Worker for that Case. When a Case Details record is made in the related details table, a case is selected from a drop down list and the worker would be brought in as a lookup field.

    Rotating the workers would be based on what? Dates of availability? How is rotation established?

  • 3.  RE: Automatic assignment of case to worker

    Posted 03-29-2017 14:28
    Rotation would be based on number of cases the worker has. If they have, say 150 cases, they wouldn't be assigned any new cases. Other than that, rotation would be based on a round robin. Once someone gets assigned a new case, they're effectively moved to the back of the line.

  • 4.  RE: Automatic assignment of case to worker

    Posted 03-29-2017 14:51

    So far with this information, we have a two table relationship where::

    Case Worker >>>> has many >>>> Cases

    A summary field established in the Case Worker parent table will count the number of Cases for the Case Worker, [# of Cases}

    Another [Max Date] summary field in the Case Worker parent table will give you the latest date a Case was created. It could be based on [Date Created] or another Date field you wish to establish. Summarize > Maximum > [Date field].

    Create a report in the child Cases table that lists cases in Ascending order based on the [Date Created] or whatever date field you wish to use for the summary max date field.

    {{ Correction: The report described above is to be made in the Parent Case Worker table }}

    Create a formula column in the parent Case Worker's table where the [Case Worker Name] is displayed only if they have less than 150 cases. Call the formula field, [Available Case Workers]. The formula field would look something like this::

    If([# of Cases]<150, [Case Worker Name], "")

    In the Child Cases table, create a drop down selection list field based on the parent's Case Worker formula field, [Available Case Workers]. In the properties for this selection list field, you'd choose select from different table and navigate to that formula field.

    In the Cases child table, that [Available Case Workers] formula field should list only those names of Case Workers that are available in earliest case date to latest case date order, according to the formula.

    Let us know how this helps.

  • 5.  RE: Automatic assignment of case to worker

    Posted 03-29-2017 19:21
    I created a quick demo of one way to do this with script.

    Have a look at the number of Jobs each Worker currently has:

    List All Workers and Number of Jobs

    Then open the page to create a new Job:

    New Job

    You will notice that the first Worker with the least number of Jobs is pre-selected as the related Worker for the about to be created new Job.

    I did not bother excluding Workers with more than 150 Jobs from potentially being assigned a new Job but this and any other business logic can easily be added.

    Pastie Database

    If you need further assistance implementing this solution feel free to contact me off world using the information in my profile:


  • 6.  RE: Automatic assignment of case to worker

    Posted 03-30-2017 11:15
    I created a New Worker with 0 jobs in your demo and then created new jobs but he was never allocated any?

  • 7.  RE: Automatic assignment of case to worker

    Posted 03-30-2017 11:18
    After I manually chose him for a job, the next job that I created he was selected.  If jobs=0 then it doesn't look at the selected worker me thinks.

  • 8.  RE: Automatic assignment of case to worker

    Posted 03-30-2017 11:33
    I created it in 15 minutes so maybe it needs some more testing. I am at an all day event today so maybe I will look at it over the weekend.

  • 9.  RE: Automatic assignment of case to worker

    Posted 03-30-2017 11:48
    I see the problem. I queried the Jobs table and looked for the related worker with the least number of jobs but did not consider those workers with no jobs. It is a simple fix I will attend to over the weekend.