Reporting time on a monthly basis - Workspace & bases structure looking for advices

[MathieuM]

Hello Airtable Community, 

I've started building a Project management tool on Airtable for a marketing agency that sells consultants time.

The Project management part is quite straightforward even though I believe this might change based on the agency needs and internal processes.

However, I am now facing some difficulties to implement the "Time reporting" part to this tool. (bases are connected) :  

1 - Project (information regarding customer, start/end dates, tasks, sub tasks, consultants,  #total available hours, #total spent hours #nb of tasks completed ...) This is meant to organize projects, track budget and project progress. Projects, are tracking time on related tasks. 

2 - Tasks (information regarding the tasks to perform : start/end dates, sub-tasks, status, priority, consultants, predecessors/sucessors, time for the task, time spent on task ... ) these are the main action to perform within a project. Tasks are tracking the time spent on related subtasks. 

3 - Sub tasks : (details of the tasks : same information as tasks bu these are used for building TO-DO in interfaces, and also to report the time spent on each subtasks currently.) The subtasks add a granular level on tasks and were meant to track time more precisely.

My problem now is that for each project, the pool of available hours is not linear.

There might be : 
- Project one : January 40h, February : 40h, March 30h, April : 10h, May: 40
- Project two : January 10h, February : 10h, March : 0h, April : 20h ...

What I would like the consultants to do is :
- Reporting time (on tasks or subtasks)
- Time on tasks or subtasks should then be associated to a month (based on their dates)
- Each project would have a pool of monthly hours available (listed initially by the sales) => dedicated base ?
- Time reported at the tasks or subtasks level should then be reported on the monthly time spent (at project level) so I can track time spent and remaining time available precisely.

Do you have any advice on how to build it. I tried different methods but I am blocked as I do not understand how to achieve this. Currently the time reporting system is based on a "Global"" pool of hours at the project level. However, I do not know how to structure the system in order to use a customized/not linear monthly hours available system.

Sorry for the long post, hope you can help me figure how to mo

[Mike_AutomaticN · Accepted solution]

Hey @MathieuM!
This sounds confusing, however I'll give it a shot. We can connect as needed to dig deeper.

1. Having "Total Hours" at a Project level would not be enough, as you need this to be set per time period.
2. You might want to have a table (e.g. "Periods for Project") which has a Start Date, an End Date, and a link to the Client (e.g. 1/1/2024 - 1/30/2024 - Client A).
3. Time Tracking/hours log should not be directly linked to Projects, but to Periods for Project.
4. Time Tracking could be also linked to Sub-Task.

With the above you could get: Total time spent per Project (in full) and Total time spent per Period per Project.

However, the above would not allow you to get time spent per Period for Sub Task. If that is the idea, then you would need to have a "Periods for Sub Task" table, and link the Time Tracking to such new table rather than to Projects directly or to Periods for Projects directly.

Sounds confusing, and I might be missing some context, but I hope that helps. Otherwise, please feel free to reach via Private Message and we can have a brief call to go through it!

Mike, Consultant @ Automatic Nation

[MathieuM]

Hello Mike, 

Thanks a lot for sharing some guidance on the best method.

I'll try again following these steps and hopefully get something working this way 🙂

Have a good day

[Mike_AutomaticN]

Sound great @MathieuM! Feel free to reach out as many times needed. 

Mike, Consultant @ Automatic Nation