Forum Discussion

CarolynHendryc1's avatar
CarolynHendryc1
Qrew Member
6 years ago

Schedule hours over multiple weeks in one entry

We have a Project Tasks table where each task has a Start Date, Duration, Budget Hours, and Employee assigned to it. One of the most important pieces of data we need is how many hours each employee is scheduled per week. 

The problem comes when we have a task that will happen over multiple weeks. The hours for the employee will only show up the first week, but I need them spread out over all the weeks.

For example, I have a task with a start date of June 3, duration of 50 days, and 60 budget hours. On the summary table it shows that employee as having 60 hours booked the week of June 3 and 0 hours over the following weeks. I'm stuck as to how to write a formula to split out these hours over the multiple weeks. 

We have thousands of tasks and I don't want our scheduler to have to enter multiple entries per task to break things out into weeks.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    The way I would tackle this natively, is to have a child table of weekly hours for a task.  The table would contain the first day of the week, say Sunday and then the hours for that week.  I'm sure you would agree that then you could easily make your reports of task time per Staff member per week.

    That way one task would have Many Weekly Hours.

    The issue, of course, is how to maintain that child table automatically.
    I would set a practical limit as to how many week a task can span and prevent a task form being entered more than that # of week.

    Then say we allow for a max of 10 weeks.

    I would create 10 fields on the task to calculate the the time for each of the 10 weeks.

    Then any time a task is added or modified I would trigger an Automation. 

    Step 1 would be to delete the tasks children.

    Steps 2 through 11 would be to create 10 children, may of which will have zero time that respective week, for example a task may only span a week or two.

    Step 12 would be to delete all, the children, for that Task with no time that week.

    Once you get that working, then the 1 time project would be to create the initialized values for all the children.

    That can be done by making 10 saved table to table imports t import from the parent task to the children, and then mass deleting all the zero time entries..

     
  • CarolynHendryc1's avatar
    CarolynHendryc1
    Qrew Member
    6 years ago
    I'm going to have to stew on this for a while to see if I can make it work for us. This is for a forecasting schedule, so the times often go well into the future and can span many months, even years. But I like this idea of a child table (hadn't thought of that) and I may be able to limit the input to just the next 10 weeks or something like that. Thank you for the detailed response!