cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnom
Level: Powered On

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@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.

 

FindDeptHeadV1.jpg

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

10 REPLIES 10
Sarah_Bull
Level: Powered On

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

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!

Highlighted
johnom
Level: Powered On

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

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

Super User
Super User

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

@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.

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
johnom
Level: Powered On

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

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

 

Super User
Super User

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

@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?

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
johnom
Level: Powered On

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

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

Super User
Super User

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

@johnom 

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

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
johnom
Level: Powered On

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

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

Super User
Super User

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

@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.

 

FindDeptHeadV1.jpg

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

johnom
Level: Powered On

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

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,030)