Use Flow to Lookup field value in sharePoint list and return variable to use in Email

[johnom]

I’m looking for some help on what I think is a straight forward Email Flow set up involving establishing a variable - getting stuck in trying to obtain the correct information to create variable

Working in SharePoint I have a list that controls view and edit properties of the PowerApp

email	Department	DepartmentHead
joe@abc.ltd	Production	No
mary@abc.ltd	Production	Yes

When a new Item is created by Joe I want to send an email from the department head Mary

My logic is:

Pick up the Created By Email field from the New Item

Lookup the Department

Lookup who is the Department Head for that Department

Return the email address for the Head of that department as a variable and use to send an email from

I appreciate managers can be picked up using Get Manager but there are a lot of other settings and want to keep all together in one list

any help/ideas appreciated

John

[Sarah_Bull]

This might add too much to your runtime, but one way of doing this would be:

1. Look up that email address of the creator in the SharePoint list. If DepartmentHead equals 'Yes', the Department Head is that same person and you don't need to search further.
2. If the creator is not a DepartmentHead, use the Filter Array action to filter the list based on their Department name.
3. Then, loop through all elements in the filtered array and locate the one where DepartmentHead is 'Yes'.

As a prerequisite, you will also need access to all your Department Heads' mailboxes to send an email from another person's mailbox (if you don't already have it).

Hope this helps!

[johnom]

Thanks  - not used arrays  - will do a bit of youtubing

[ScottShearer]

@johnom 

If I understand your requirement, it involves sending an email as someone else - the department head.  Unless all the department heads have granted you permissions to send as them, you won't be able to achieve your requirement.

[johnom]

Ah- sorry got my logic wrong - your suggestion made me read the ask again
- getting too tied up in trying to create a flow

Once an Item is created in SharePoint, the Department Head should be sent an email by Flow
I have a 'Send Email with Options' set up and working if I add an email manually

I need to pick out that email address from the list - I was thinking create a variable (maybe with this 'array' feature) and then add to the 'To' Email

[ScottShearer]

@johnom 

Is the Department column a lookup column?  If so, is the manager listed with the department in the loopkup list?

If this is the case, then here is a link to a solution that I provided to a very similar issue that may help you.

If you situation is different than I have describe, please let me know the specifics and I will try to assist:

1) What type of column is the country column

2) Where is the manager information stored?

3) What type of column is used for the manager?

[johnom]

Hi Scott

Yes, in the list the  Department and Department head are choice fields

The email is text

I'll work through the steps in your link and see how I get on  - thanks again

John

[ScottShearer]

@johnom 

So, Department is a choice column in both lists?  I'll provide an example for you.

[johnom]

much appreciated  - and I will continue working through your other solution, it's more learning

[ScottShearer · Accepted solution]

@johnom 

In my example, I have a SharePoint list with a choice column for the department, a text column for the email and a yes/no column for Department Head.  You solution might not look exactly like this, but this should point you in the right direction.

While the Get items action will only return 1 item, you will still be inside of a loop when you reference a column.

[johnom]

Hi Scott

Great  - got the Flow to work

Thanks for the help

A few things for others just getting into Flow (Scott pls correct me if I have picked up any of this incorrectly)

1. You have to Get Items once for the List and then again to be able to select its fields from dynamic content

2. When creating the Filter Query using functions like eq it does not highlight like it would in sql, etc so you just have to know them and type correctly

3. When adding field for comparison do not forget to enclose in single quotes