Stuck after almost creating my ultimate Organizational Base

[Pieter_van_Leeu]

Hi!
I’ve been playing around with the default Organizational Base template and have changed it in a way that it would perfectly suit our company, but got stuck at the last bit.
I have a tables with employees, clients, industries, etc. I wanted to register how employees are utilized sacross clients and have done so using junction tables. I have embedded a fake and simplified version of this below
my test table

As you can see I can define the percentage of time a single employee is utilized per client in the Client utilization table and I do a LookUp with a SUM(values) in my employee table to sum up the total amount of time an employee is utilized for any client at all. in this way I can check if he or she is available for more work or not.

Now the part where I get stuck.
I created a similar table as the utilization table where I can define per user how much time they prefer to work per industry. I want to compare this preferred time per industry with the total time per industry they are utilized in, but somehow I can’t figure out how to get this last thing working. If someone can help me with this I would be forever grateful!
Thanks!

[Lisa_H]

I am not real good at these things yet, but looking at it, it almost looks like you could combine all those tables into one, and just look at the one table with different views, and it would be a lot simpler.

[Justin_Barrett]

Does this do what you want?

Airtable

Test base - Airtable

Explore the "Test base" base on Airtable.

[Pieter_van_Leeu]

Hi Justin,
Thanks for your reply and attempt! I’m not exactly sure how you created this, but your solution looks like I would need to manually create lookup columns in the employee table for each utilized industry, and also for each prefered industry (if they have more than 1). And in the client utilization table I would have to add columns and manually fill those out as well (or so it seems). I don’t think this is exactly what I was looking for.

[Justin_Barrett]

You can copy the sample base to your own workspace if you wish and see how everything it set up. It just uses rollups, lookups, and formulas to collect the info and make it available to other tables. Some field setup will be necessary, but once that’s in place, the rest is automatic. No other manual entry is required aside from what your original sample has.