The grouping operations will probably get you a lot of what you want. Make a new grid view, and in that new view choose to group by client. That will automatically organize everything into collapsable groups, with the Employee Hours column auto-summarized for each. For subgroups, add another grouping criteria like Employee, so you can then see an overall summary for each client, and within that sub-summaries for how much each employee worked for that client.
Thanks Justin. I actually just amended my original post because I realize I didn’t specify-- I need to have these sums be in a cell so I can automate an Invoice in Blocks. Setting the “group by” filter does this, but it’s not translatable to a block.
Ah, that makes sense. I messed around with a sample base and a quick temp block to act as an invoice. (Haven’t used blocks before, but it was fun playing for the first time.) Here’s what I came up with.
I’ve got three tables: Work, Clients, and Employees. Work is where the date, client, employee, and hours are entered. However, unlike your example, the client and employee fields are links to their respective tables (single record links only).
The bulk of the work happens on the Clients table. The automatically-generated linked field from the Work table, I renamed “Dates Worked.” I then added a Rollup field named “Hours Worked.” This points to the linked records in the Dates Worked field, and looks up the Hours field from each of those records, using the SUM function to aggregate them, creating a total number of hours worked for each client. I added another Rollup field named “Employees,” also targeting Dates Worked, this time pulling from the Employee field of those records, and using the ARRAYUNIQUE function to aggregate them. From there the data can be pulled into a block as you build your invoice.