I've got 2 files (a team hierarchy from Remedy and a SharePoint user list of individual) that I need to join together in order to create a record in SharePoint for the SharePoint workflow to send an email notification. Each level of the hierarchy is a different field in SharePoint so that it executes the first workflow to the manager, and in 2 weeks to the Director and so on. I'm unable to figure out how to join the Hierarchy with the user list so that I get the email for each individual but maintain the hierarchy structure.
The Remedy file has the team name along with manager, director and VP for each team.
File 1: Team Hierarchy (from Remedy) | |||
Team | Manager Name | Director Name | VP Name |
Team 1 | Manager 1 | Director 1 | VP 1 |
Team 2 | Manager 2 | Director 2 | VP 2 |
Team 3 | Manager 3 | Director 3 | VP 3 |
Team 4 | Manager 4 | Director 4 | VP 4 |
Team 5 | Manager 5 | Director 5 | VP 5 |
Team 6 | Manager 6 | Director 6 | VP 6 |
Team 7 | Manager 7 | Director 7 | VP 7 |
Team 8 | Manager 8 | Director 8 | VP 8 |
The User list of by each individual.
File 2: Email Addresses (from SharePoint) | |
Name | Email Address |
Manager 1 | Manager 1@company.com |
Manager 2 | Manager 2@company.com |
Manager 3 | Manager 3@company.com |
Manager 4 | Manager 4@company.com |
Manager 5 | Manager 5@company.com |
Manager 6 | Manager 6@company.com |
Manager 7 | Manager 7@company.com |
Manager 8 | Manager 8@company.com |
Director 1 | Director 1@company.com |
Director 2 | Director 2@company.com |
Director 3 | Director 3@company.com |
Director 4 | Director 4@company.com |
Director 5 | Director 5@company.com |
Director 6 | Director 6@company.com |
Director 7 | Director 7@company.com |
Director 8 | Director 8@company.com |
VP 1 | VP 1@company.com |
VP 2 | VP 2@company.com |
VP 3 | VP 3@company.com |
VP 4 | VP 4@company.com |
VP 5 | VP 5@company.com |
VP 6 | VP 6@company.com |
VP 7 | VP 7@company.com |
VP 8 | VP 8@company.com |
Preferred output would look like:
File 3: Team with hierarchy and email addresses | ||||||
Team | Manager | Manager Email Address | Director | Director Email | VP | VP Email |
Team 1 | Manager 1 | Manager 1@company.com | Director 1 | Director 1@company.com | VP 1 | VP 1@company.com |
Team 2 | Manager 2 | Manager 2@company.com | Director 2 | Director 2@company.com | VP 2 | VP 2@company.com |
Team 3 | Manager 3 | Manager 3@company.com | Director 3 | Director 3@company.com | VP 3 | VP 3@company.com |
Team 4 | Manager 4 | Manager 4@company.com | Director 4 | Director 4@company.com | VP 4 | VP 4@company.com |
Team 5 | Manager 5 | Manager 5@company.com | Director 5 | Director 5@company.com | VP 5 | VP 5@company.com |
Team 6 | Manager 6 | Manager 6@company.com | Director 6 | Director 6@company.com | VP 6 | VP 6@company.com |
Team 7 | Manager 7 | Manager 7@company.com | Director 7 | Director 7@company.com | VP 7 | VP 7@company.com |
Team 8 | Manager 8 | Manager 8@company.com | Director 8 | Director 8@company.com | VP 8 | VP 8@company.com |
I tried to join on the name, but it only joins the one field and not the hierarchy. I know it's got to be easy, but it's got me stumped. any insight or help is appreciated. thanks!
¡Resuelto! Ir a solución.
Try using the Transpose tool while grouping by Team Name (to keep things grouped together by team), which will put all your Manager/Director/VP names in one column as values... then join that Value column to the list of names + emails using a Join or Find & Replace tool. You can then Cross-Tab the results back to get to your original layout (with a little tweaking...) - I included a workflow that shows a few tricks using Formula and Multi-Field Formula tools to get the data parsed in the right fields... hope that helps!
Cheers,
NJ
Thanks so much! this worked perfect and I learned about the Multi-Field Formula that I would have never thought to use before.