Ask Search:
Christian MeredithChristian Meredith 

how to make a forumula for a hierarchy of people

Hi - I have this that works for my queues, but not 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.

CASE(
Owner,

"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",
NULL
)
Best Answer chosen by Christian Meredith
Amnon KruviAmnon Kruvi
There's no coding required for the last option. You just need to create the object and the various lookup relationships, then populate the data.

All Answers

Amnon KruviAmnon Kruvi
Hi Christian,

If you only have a few reps, you could go with a formula like that (though you'd need a few changes):
CASE(
Owner.Username,

"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",
NULL
)

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.
Christian MeredithChristian Meredith
Hi - I do like the sound of the last option, but it may be out of my programming reach.  If you have an example of a possible syntax it would be greatly apprecaited.
Amnon KruviAmnon Kruvi
There's no coding required for the last option. You just need to create the object and the various lookup relationships, then populate the data.
This was selected as the best answer