I have data from employees in a table about who they are (name) and what their hourly billable rate is ($/hr). I also have a separate table, that has the time logs from a given time period. This table shows the person who logged the time, the job they logged it for, and the # of hours they logged.
I want to have a “Costs Log” Table that is updated when I click a button (end of each day/week or something). This button action should “go through” the Time Logs table, and for each job represented in the table (Rows), it should add up the hours for each employee in the hours column and multiply it by their hourly rate which is stored in a separate table. It’s important to note that I don’t want the jobs duplicated in the Costs table (if there are multiple entries in the Time Logs for the same job, the function needs to be able to combine them together and modify the row in the Costs table if it already exists.
I was able to do this without the final part (including the billable rate to get the $ amount), but I honestly don’t know enough yet to know how to approach this.
I have created a test document without my attempt because I don’t think my attempt really added value since I don’t really know the right way to do this.
I am very open to other ways of doing it. I just want the most efficient method for getting the costs of a project in a “Costs” table (and eventually I would like to figure out how to automate it so I don’t need the button - if that’s possible and reliable).
Sorry about that, thanks for flagging it. I find it a little confusing that you have to click “Get a shareable link” in order for that option to appear, when Clicking “get a shareable link” is not a part of the process of embedding the doc in a community post.
I had created a “Pay Tracker” template for hourly work a while ago and I’ve also created a Basic Project Management template. These could be mashed together to create something that might work really well for the setup you’re describing.
Here is a link to the original template I created for Pay Tracking…
Here is a link to the Basic Project Management Template…
And this is a doc one where I tweaked the info of the Pay Tracker to work for multiple employees and I removed the “Field” pay type. The idea with this is to track various rates, say if someone gets a raise, it’ll keep the pay value correct for the relevant dates. You can also add “Bulk Pay” values for things like reimbursements or jobs that are quoted at a single price.
Thanks @BenLee. I think your document is quite different because the data is simply in one table, whereas in my situation, the pay rate and the actual time data are separate.
I’ve updated my document to show you where I was at with my last attempt. I realize the faulty use of CurrentValue being the reason I get the “XYZ” showing up a second time and sure there are more issues to be worked out once that is resolved, but this is where I am at. Hopefully it shows the problem I am trying to solve.
I have the pay rate and time per project separate as well. The doc I posted actually has 6 tables including the lookup tables for employees.
It’s a little more in depth because I had to track when I got a raise. If my rate went up, I didn’t want that to affect any previous totals I had calculated, so the table finds the rate based on the date.
Using lookups in Coda lets you link data together, so you can pull rates into a different table, like a pay table.
Hey @BenLee, I apologize - I never saw the third document. Thank’s for sending that over.
The difference with mine was that the data for Timetracking was coming in from cross-doc, so I was not manually entering in new lines to trigger the formulas.
However I used your example and changed the way I was doing things to just do the math in the original document and port over the final result.
I recommend for new users to check out the brand new recording on filtering by using formulas.