Pull Data from Multiple Sources .. I think

Ask for Help
1

I’m in the middle of a huge problem, I think. Not sure how to make this work, Ill explain below:

Data Sheet 1: Employee Timesheet
Employee adds Weekly Timesheet, with Location and Hours Worked in that Location
Data Sheet 2: Projects (Locations)
Show Data from the Projects & Locations (which is used in the above Data Sheet)
Data Sheet 3: Weekly Timesheet View/Totals
Shows Data from all employess e.g Hours Worked and Locations

I’m get that I add up all the Hours worked and Filter it by Project to show Total hours in the Projects Layout. but how can I show in the Projects Layout Total hours per Employee per Project…

Hopefully theres a solution

2

I assume you can build your data out like this.

1. Users Table

User ID Name
U001 Alice
U002 Bob
U003 Charlie

2. Projects Table

Project ID Project Name Project Manager Status
P001 Project A Jane Doe Active
P002 Project B John Smith Complete
P003 Project C Mary Lee Active

3. Timesheets Table

Timesheet ID User ID Project ID Week Starting Hours Worked
T001 U001 P001 2024-07-01 10
T002 U002 P002 2024-07-01 8
T003 U001 P002 2024-07-01 5
T004 U003 P001 2024-07-01 12

Then:

  • In the Timesheets table, add a template column joining the user ID and project ID together.
  • Create a relation from that column to itself, return multiple matches.
  • Create a rollup on top of that relation, returning the sum of hours worked (this is the total number of hours worked for each project for each employee).
  • Create a single value column, returning the first Timesheet ID from the relation.
  • In the Projects table, create a multi relation to the Timesheet table, matching by the project ID.
  • Display a collection using the relation above, filter by rowID equals to first Timesheet ID from the relation, showing the total hours from the rollup column.