Hi, I am using Airtable to track work my team does for clients, and I was hoping for some help on managing time spent on a project.
A client usually retains me for 10 hours on their project. The project is usually split between two or three people, each of whom has different tasks regarding the project. We need to make sure that together, those people do not exceed the 10-hour retainer while working on those tasks. E.g., if A, B, and C are working for Client X, who has hired us for 10 hours, and A has logged 5 hours, and then 2 hours, and B has logged 2 hours, then C should be able to see that the total time spent on the project so far has been 9 hours. In addition, if A spends another 1 hour on Client X’s project and logs it, Airtable should register that the 10 hour cap has bee reached, and update Client X’s record to show that the retainer needs to be refreshed. I was wondering if there’s a way to do this with Airtable.
Right now, we use one tasks table to input all the tasks the whole team is working on. We input: the task, the client it is for (*), who is doing the task, and the time spent. Therefore, A, B, and C put in all their tasks for the different clients they have (each with a different hour cap). The client column links to another table with each client’s details, including the hourly cap. I was trying to put in columns in this table titled “Total Hours Spent on Client Project” and “Refresh Retainer?”. The idea was that the former would sum up all the hours spent on that client from the tasks table, and the latter would return “Yes” if we had reached the 10 hour cap and “No” if not. I have been having trouble with this, and I’m out of ideas. Is there a better way?