To-Many Hierarchical Relations and Filtered Lists

I have two questions below. I’m trying to go back through my workspace and simplify some structures. If anybody has any ideas/advice on either of these issues I’d greatly appreciate it.

Hierarchical Relations for One/Many-to-Many

image

In this example, is there any way to redesign these relationships to get the benefit of hierarchical relations (filtered choice) when selecting people on the project page? This is a simplified version of a problem I’m having. Since people is a many-to-many relation, it will show the full list of people rather than being filtered by the selected team. With a lot of entities in the people database, this is becoming messy and prone to mistakes.

It would be interesting if you could create a relation to a formula or lookup. I could have a lookup for that team’s people, then have a relation to that lookup to have a filtered selection.

Many-To-Many Filtered Lists

image

Another simplified version of my problem: employees have a company and a position. A lot of companies have the same types of positions. So if multiple companies are linked to that same position, and even if an employee is only linked to one company and one position, they will show up under that position for every company on the list (see below)

Is there anyway to accomplish this without having to create the same position types unique to each company? That’s how I currently have it, and it’s mostly automated, but I feel like it’s overcomplicated.

For this issue, should it be understood that a single Team is linked to a Project, but not all the People in the Team are tied to the Project?
You want to pick People for the Project only from those People in the Team, right?