So I need to identify which user could each manager have access to. The tricky point is that a Manager can have an Individual that can be Manager also. So he could have access to the Individuals of his team members Managers.
What I need in order to filtering correctly is the green column:
Below is the method which you can use if you only need it inside Glide and not the Sheets.
Create a template column to join all individual names (assuming you’ll scale and have more individual columns), in a comma-separated format.
Create a split column to split the template column above.
Create a relation from that column to the managers column.
Create a join text column on top of the relation, return the template column from step 1. Here, you have all individuals that are managed by your individuals. (2 levels below)
Then, add a final template joining the template in step 1 with the join text column in step 4. You should have all values ready to be used.
Edit: Seems like I missed the “3 levels below” with the Pedro row > Patri > Adolfo link. If you want to scale to more than 3 levels then that might be hard to achieve.
I took a different approach going from the individual to the manager, but used a similar strategy. It’s a little clunky, but works as long as you know how many “levels” you will have.
OMG @Darren_Alderman ! Thank you soooo much! This is a good a approach if the company knows how many levels they will have.
On this case, this is a dashboard where is supposed to work with a lot of different companies. So this is not the best solution but at least it’s a middle-solution.
I will leave this opened in order to see if there is someone that can think about doing it fully automated. It’s quite interesting your approach though and thank you sooo much again!
I spent some time trying to come up with something unique based on your existing layout, but kind of hit a dead end. The open ended number of levels kind of makes it tricky. However, I approached the problem a little differently.
I still have a user sheet with users and their associated title in the company. But I also created a PositionAuthority table that lists all of the positions, the direct manager title one level above, and then a comma delimited list of manager titles that have authority over that particular position. This made it a lot easier to construct complex open ended hierarchies.
First I gave each user a title in a User table:
Then I created a PositionAuthority table that lists each position, the direct manager title above them, and a list of all managers that have authority to that position.
Back in the User table, I have a group of columns that take the user’s title, relates to the position authority table, gets the direct manager title, then uses that direct manager title to find one or more direct managers for a particular user.
Then I have another group of columns to determine all of the users a manager has authority over. This is done by creating a relation between the user’s title and the Authority List in the Position table. Then it looks up all of the matching individual position titles. Then it creates a relation linking those titles to the user titles.
So in the end, everything is dynamic as long as each user has a title and the Position table is updated with all titles and authorities. And it’s also much easier to maintain in the long run. A user can switch titles and the granted access will update automatically across all users. Also, a position can be placed under a different manager title, or access authorities can change and it will update for all users. Hopefully that all makes sense. If I were to do anything differently, I would probably include Row ID’s or Unique ID’s for each position title and then assign to each user the title ID instead of the title name, and also write the ID’s into the authority list instead of name. This allows a title to be renamed in the future without causing conflicts. I would have done it in this example, but it would have been harder to explain and understand.
