Automatic assignment of case to worker

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
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!
Photo of Jay (DDS)

Jay (DDS)

  • 430 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

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?

Photo of Jay (DDS)

Jay (DDS)

  • 430 Points 250 badge 2x thumb
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.
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

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.



(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,352 Points 20k badge 2x thumb
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
https://haversineconsulting.quickbase.com/db/bmnpvxnfk?a=td

Then open the page to create a new Job:

New Job
https://haversineconsulting.quickbase.com/db/bmnpvxngn?a=nwr

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
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=565

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

https://getsatisfaction.com/people/dandiebolt
I created a New Worker with 0 jobs in your demo and then created new jobs but he was never allocated any?
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,272 Points 20k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,272 Points 20k badge 2x thumb
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.