How can I write a formula for a calculated field in runtime to bring up a the executive name of a certain department or subdepartment from user profiles (which is synched from AD)!
I have used calculated field, inserted a formula for <userprofilelookup(username, "manager")> to bring up the manager of a certain user and other values such as department from the managed properties. Now I need to use the department name brought up for that user to display the executive name .. all in runtime.
it's all about setup of your AD. you should examine hierarchy and attributes and find ones that you want to pick.
what's the difference between user's manager and department executive? isn't it the same person?
maybe you need to read manager of manager. or manager of manager of manager.
as I said you have to examine your hierarchy.
well, sometimes a Manager of a person could be the executive himself, or as you said it can be manager of manager of manager.. it really depends on where the employee resides in the hierarchy. AD is only setup to track the manager of a person regardless of where they are in the hierarchy and SharePoint then puts it altogether through the org.chart and displays it for you. It would be too much if in AD and userprofile, we'd have to insert a managed/mapped field and update executive for every single person. And since it is a large corporate with different departments and structures/units/divisions, a lot of people can be really called managers at different levels, so the hierarchy is not consistent across the board. I can't rule it out for all to assume that level 4, for example are all managers, and level 2 are all executives then I would lookup manager of manager, but that's not going to work in this corporate. That's why I wanted to lookup from AD based on the position (exact title) as I only wanted to pull the name of the person where the title= executive and department=ABC. Oh well, seems like this probably can be done if it was within the workflow actions but not as a runtime calculated form field, then!!
Not easy to provide a solution for this without also bringing a lot of overhead to your environment. I'd first check the gains that you achieve with showing the executive of a person (which a person should know).
I only see the following options:
- bring a lot of logic to your form that gets manager of manager until the found person holds a specific ad property value and hide all other found people.
- use SDK to create a control that looks up the person you want by logic
- add a property "department" to each user that can be used to lookup the executive of that department.
- try to bring the default or any other org chart webpart onto the page that holds the form or integrate it directly to the form
I hope this brings you some ideas on how to proceed.
as you have identified there is no straightforward solution how to achieve that.
maybe easier solution for you would be to maintain a (lookup) list of departments and their executive managers in sharepoint.
that brings as well advantage for cases when 'executive responsible' from workflow process point of view is different from 'executive responsible' from organizational structure point of view.
Probably can query using SQL Request Control into SQL Server that contains Active Directory data where department equals to department and directReports is empty.