Skip to main content
Question

How to create a total of weekly allocated hours per employee

  • January 29, 2019
  • 0 replies
  • 83 views
I'm looking for a way to create a total of all allocated hours per employee, per week. I currently have a Summary Report that does display the information needed, but management wants to be able to trigger notification if/when a resource is over-allocated hours for any given week.

The basic structure for this app is: Projects have multiple Tasks and Tasks have multiple Resources (employees). When resources are allocated hours new entries are created (or updated if an existing entry is edited) into a Assigned Resources table. The relevant fields are:

Resource (employee)
Week End Date
Task

What I'm trying to do is add all the hours for an employee for each week (Week End Date) for any/all tasks. Like I mentioned above my management wants to be notified if an employee is allocated more than 40 hours in any given week for all Tasks they are assigned to- they don't want to just rely on looking at the Summary Report alone.

I tried to create a Summary "Field" in the relationship but I am not really sure that is possible- or how exactly to do it in this case (add all hours assigned to an employee over multiple tasks). I also considered creating a separate table and use Quick Base Automations to store the total, but I'm not sure that's really going to work in this case. I did see a post where someone suggested concatenating fields together as a key (in this case, Resource and Week End Date?) but I'm not sure how to do that either (I did try created a formula field to put those together, but I wasn't sure where to go from there and I couldn't use it as a key).

Does anyone have an suggestions or can point me in the right direction?
    This topic has been closed for replies.

    I suggest that you decide which weeks management wants to look at.  Hopefully they will say like "well we usually only look out about 6 weeks.

    So make a summary fields on the Employee Record to summarize the allocated hours for the current week. call it [Allocated Hours Current Week]

    The filter on the summary will be

    where date is during the current week.

    The copy the field and called it [Allocated Hours Current Week + 1]
    Adjust the summary filters to be where
    ALL of 
    Date is during the next 1 weeks


    duplicate to 
    [Allocation Hours Current Week + 2]
    Adjust the summary filters to be where
    ALL of 
    Date is during the next 2 weeks
    Date is not during the next 1 week

    and keep going so the +3 will be
    Adjust the summary filters to be where
    ALL of 
    Date is during the next 3 weeks
    Date is not during the next 2 weeks


    The you can make a report when any of those say 6 summary fields is  > 50 hours or whatever the threshold of concern is.

    If they want more weeks out well, just turn up the music real loud and keep on duplicatin' that Summary field.  It's pretty quick once you get the rhythm of the mental pattern going for the field duplication.




    Terms & ConditionsAccessibility statement