Forum Discussion

DavidHoover's avatar
DavidHoover
Qrew Trainee
7 years ago

How to create a total of weekly allocated hours per employee

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?
App builders
NotificationsEmail
Quickbase Automations
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.




    • DavidHoover's avatar
      DavidHoover
      Qrew Trainee
      7 years ago
      In this scenario where/how would a notification get triggered? I do already have a Summary Report that displays the total hours allocated for all weeks in the future grouped by employee and weekly end date- I also used some JavaScript to highlight/color the cells (employ row, weekly end date column) that are over (and under) allocated. But management wants to notified in email instead of just looking at the summary report. I'd also like to be able to display the total hours allocated to an employee for any given week when the project managers are allocating hours (so instead of just management getting an email notification, the PM's can see in real-time how many hours an employee is already allocated (for any/all tasks they are assigned).
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      re:But management wants to notified in email instead of just looking at the summary report.

      It would be a daily subscription to the report I described above.  The report would be called EE's with >Hours Allocated hours in the next 6 weeks.

      As for your 2nd question it is hard for me to answer without seeing how the Pm allocate hours, but you could calculate the week identifier of the week(s) that are over allocated by formula and show that on some report or the drop down list for the EE.

      For example, today is January 29th.  If I was an EE who was over allocated for the bucket +1 and +2, it could read

      Mark Shnier (Feb 4!, Feb11!)
      David Hoover
      Fred Flintstone

      So in the example above they would not allocate me more hour, but give the hours to you or Fred as they are not in warning.


      You would not need to show the year as that is obvious. The formula would calculate what the first Monday is of the week that is over allocated.



    • DavidHoover's avatar
      DavidHoover
      Qrew Trainee
      7 years ago
      Okay, I see where you are going with this. I'm not sure that's what management wants but let me play with it and see what I can do.

      Being a developer (SQL, .Net, Perl, etc) I could handle this with a simple query. I feel like I'm such a newb when trying to work with QB lol.