Skip to main content

Hello all! Been struggling with this for a while with my current base. Subject of this post is a decent summary, but for explanation sake I've drastically simplified my base design:

Link to the simplified base I'm describing
tl;dr: The end goal is to look at a project, and total the number of hours spent on each task over the multiple scheduled events related to that project. 

There are three Employees:
Employee A
Employee B
Employee C

There are three Tasks:
Task A
Task B
Task C

There are three Projects:
Project A
Project B
Project C

There are Scheduled Events.

Each Scheduled Event assigns an Employee to a single Project, and various Tasks for that project. For instance... Employee A is scheduled on Jan 1 to work on Project B. They are assigned to tackle Task A and Task C. This is what I'm calling a scheduled event.

Each day will have multiple scheduled events. Any combination of Employee, Project, or Task is possible for each day. Each employee might work on multiple projects in one day, and there may be more than one employee working on the same project in the same day. If that's not clear, this might be an example of a couple days

JAN 1:
Employee A - Project B - (Task A & Task C)
Employee B - Project B - (Task B)

JAN 2:
Employee A - Project C - (Task B)
Employee A - Project B - (Task A & Task C)
Employee C - Project A - (Task A, Task B, & Task C)

Up until this point, my base layout makes sense to me, and I can accomplish everything above successfully. The problem comes in logging and totaling specific Task Hours as related to the Project:

I need each employee to log their hours spent on each assigned task for each scheduled event. So in my previous example, on the Jan 1 event. Employee A would need to manually log hours for Tasks A & Task C.

The end goal is to look at a project, and total the number of hours spent on each task over the multiple scheduled events related to that project.

So the desired output would be something like:

Project A:
Task A: 25hrs
Task B: 2hrs
Task C: 14hrs

Project B:
Task A: 12hrs
Task B: 3hrs
Task C: 20hrs
... and so on

Do any of you have thoughts on the best way to add to/structure my base for this? I'd prefer to keep the basic structure I've described and shown in my simplified base, but very willing to adjust things to help me achieve this goal. In a perfect world, hours are logged on the event, and summed hours are displayed on the project table. Would appreciate any input anyone has to give! Sorry for the novel, I really did try to keep this as short as possible. Happy New Year, ya'll!


For what it's worth to anyone reading, I've come up with a solution that works pretty well.

Hours for each task are entered on the Schedule table for each event. There is a number field for each kind of task.

On the Projects table, a Lookup for the schedule entries was added so each scheduled event related to the project is listed. Then a Rollup field was added for each task from the schedule. The result is a field on the project table with total hours spent for each task.

Reply


Terms & Conditions