how to make a forumula for a hierarchy of people
What I'm trying to create is a quasi look up, that when the owner is xxx, that the team leader gets auto populated. I know I can do this in a report via a bucket but want to have a formula do it so its live in my case.
"Rep a", "Team Lead a",
"Rep a2", "Team Lead a",
"Rep b", "Team Lead b",
"Rep c", "Team Lead c",
"Rep d", "Team Lead c",
If you only have a few reps, you could go with a formula like that (though you'd need a few changes):
"Username A", "Team Lead a",
"Username A2", "Team Lead a",
"Username B", "Team Lead b",
"Username C", "Team Lead c",
"Username D", "Team Lead c",
That's because you have to check a particular field of the owner - like the username, ID, etc. You could also use process builder or workflow and put the team leader's user ID in a lookup field, which would allow people to click it and see the user.
With that being said, this solution will not work for you once you get more people in. You'll have to edit it any time someone leaves or joins the company, any time someone changes roles, and time someone takes over a team, etc. Too much hassle and it will eventually get broken.
If your users are split into teams, why not create a Team object and create a lookup from user to Team? Team can then have a team leader. And just like that, you have an indirect relationship from the record to the owner to the team to the team leader, which you can display in a formula that you will never have to change.