Hi all,
I recently posted a question on the FileMaker community https://community.filemaker.com/en/s/question/0D50H00007AH6IOSA1 and got minimal response so I thought I’d repost here. Feel free to respond in either forum, I’m not picky! 
To save people having to jump to the other forum just to see what I’m talking about, I’ll include my original post and followup here.
Thanks!
Mike
Original Post:
I’m struggling with a task scheduling problem, and I could use some assistance thinking through solutions. I consider the problem a sort of a 2-dimensional, or multi-factor, Gantt. Here’s the setup (simplified for now):
- Client has lots of Projects, each of which has a deadline
- Each Project has lots of Tasks, which must be completed in order
- Each Task is assigned to an Employee and given an expected duration
Using each employee’s assignments, expected task durations, and project deadlines, we need to:
- Prioritize tasks as best as possible to meet project deadlines
- Give as accurate a prediction as possible of when each Project will actually be completed
What I’ve got now accomplishes the first requirement fairly well. I’m able to take all open tasks, work backwards along task critical paths, taking into account each employee’s schedule, to do the prioritization. The problem I’m having is giving an accurate prediction of project completions.
The problem is because often, following a critical path of tasks, there occur gaps between the end of one and the start of the next due to conflicts in employees’ schedules. This is fine for prioritizing and work load-- if an employee can’t work on their nominal highest priority task because it’s still waiting for a preceding task to be completed, they just work on the highest priority task waiting that is available. So at any time, an employee might have a prioritized bank of tasks that are available to work on, plus a backlog of tasks that are not yet available to work on. When a “backlogged” task does become available, it is merged into the employee’s available list at the appropriate priority.
What I’m hoping for is some way to better merge the two critical paths we’re working with here, the project tasks and the employee assignments, to give more accurate estimated start and end dates for tasks. This seems like the sort of thing that project management theorists have probably already solved, but I don’t have the right background to know.
Anyone out there have ideas that might help?
Followup:
I think I’ve got a decent solution, or at least a vast improvement over the current implementation.
I learned that, in this business’s case, when someone’s working on a lower priority item and a higher priority item becomes available, they generally pause the lower priority item to take care of the higher priority one, unless it’s more trouble than it’s worth to interrupt it. Because of this, I’ve come up with the following proposed refinement.
If a high priority task won’t be available until a particular date, we fill in the gap in an employee’s schedule with their next lower priority tasks. But, if the lower priority task would finish after the estimated start date of the higher priority item, we leave the higher priority task’s estimated start and end dates as is, and adjust the lower priority task’s end date to add the duration of the higher priority task. Say, for instance, a low priority task starts Oct 1 with an estimated duration of 9 days. Ignoring weekends, normally this would est. end on Oct 10 (Oct 1+9). But let’s say a 5-day higher priority task is estimated to start on Oct 3. Then the higher priority task gets an est. end date of Oct 8 (Oct 3+5) as before, while the lower priority operation gets an est. end date of Oct 15 (Oct 10+5, or Oct 1+9+5).
My understanding is that this logic is the most faithful description of what the company actually does in practice, so I’m pretty optimistic it will work.
For comparison, using the earlier example’s tasks, my current logic for an employee’s estimated schedule might have a gap before the 5-day high priority item estimated to start on Oct 3. Then the lower priority task would have as its estimated start date the estimated end date of the higher priority task, Oct 8 (Oct 3+5) rather than Oct 1. And the estimated end date of the lower priority task would be Oct 17 (Oct 8+9) rather than Oct 15.
Anyone see any holes in my new logic, or suggested improvements?