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!
