What you’ve got in mind looks right.
If I were you I would probably write a script and just get it to calculate everything and create records etc on its own. If you needed it automated and you’re on a Pro plan you could run it via an automation, if not you could click a button to get it to run every time you needed the data
Without a script, the only way I can think of accomplishing what you’re trying to do would be with a bunch of conditional rollups, but this means you would have to create a conditional rollup field per variable, e.g. one field per employee, possibly one field per month etc. So to get the total number of hours worked by Bob, for example, you’d create a rollup field in your Team table per team member, rolling up on the Allocations linked field with the formula of sum(values), and the conditional being Team Member = Bob, you know what I mean?
Hopefully I’ve missed something and there’s a simple way to handle this that I’ve missed that someone else can suggest
