Employee and Project are connections to other Knack Tables.
In the example below, the columns showing the Monday of the Week are fields in this “Assignment” table.
Ultimately, I’d like the Week Columns to be connections to a Week Table.
I’d like to create a SUM field in the “Project” Table that totals Hours on the Project
I’d like to be able to Total Hours per each Week on a Project and Hours each Week per employee.
I can create a Grid in this format connecting this “Assignment” table to a “Weeks” Table (along with the Employee and Project connections:
@Erik, I have done something similar for a client to transpose number of jobs per day (columns) per contractor (rows), but could be easily translated into hours per week per project employee.
This solution involves a bit of JavaScript code, and incorporates filters, colour scales, and drill-throughs on sums to see underlying records. Feel free to direct message me if you’re interested!
The dates are dynamically generated, and looped through to find records that cross over that period.
In your example, it might be project time entry start date >= week start and project time entry end date <= week start + 6 days.
Definitely interested in understanding more about how you are doing this.
I’m struggling because it seems like a 3D array.
The “Hours” object is connected to a Project Object and an Employee object. Users need to enter hours per week. A user needs of sum of hours across projects. A PM needs a sum of hours per employee per week.
Program Management needs total hours per week and total hours per project.
It’s been a tough nut to crack.
I’ve done some JS and I’ve been using KTL for a while now. I’ve got some pretty cool stuff happening in my app, this one has been a struggle for awhile
This can be solved by creating a Days object, we can create a week entry when the week starts using task. and when we enter hours then we pick the current week(top record).
If we need to enter hrs for the old week then we need some javascript.
I’d love to see an example of what you are suggesting.
That said, I’m not sure it would work for this use case.
Users need to enter edit/enter hours for current, future and past weeks.
This is for planning, not tracking ( like a timesheet.)
Hmmm,
It seems there might be a CSS solution to transpose a table. I’m going to put my junior coder hat on and see what happens there.
Not sure that’s the solution, but fun to play with…
I don’t have access to that project now. But this needs to be done using JavaScript. I will try to find the code and let you know if I saw. Otherwise, it takes time to implement
It’s interesting that is one is so challenging.
I can do it by creating each week as a field, but that doesn’t allow a User to add weeks in the future.
So, I’m not sure a transpose will meet the need.
It really is connecting 4 Objects/Tables together.
Employees + Projects + Weeks inside another Object (Assignment)
So,
Assignment 1 is connected to Employee 1, Project 1 and Week 7/22 and the only field in the Assignment Object would be the Hours planned to work.
To follow up, I was able to make this display as desired by creating a Pivot Table in a Report that groups by Resource and has Weeks as Pivot Columns.
It’s one-for-one for the object feeding the Pivot Table. ie, there is one record for Design Manager for Week 9/9 that has a value of 5 hours. But that’s ok. Just not the way Pivot table are intended to function.
