How to send email based on two columns in SharePoint List

[Kiara1987]

I have a SharePoint that has a list of current projects. I want to set up a flow that when a Status is chosen, an email gets sent to a person based on the status chosen. 

For example, when "Pricing" is chosen, an email is sent to whoever is in the "Project Manager" but when status "In Progress" is chosen, an email gets sent to whoever is in the Designer/Estimator Column.

@FLMike 

@SudeepGhatakNZ 

[happyume]

1 - Create an automated Flow which runs whenever the SP List is modified.

2 - Initialize a string variable with no value

3 - Create a Switch block on the Status column

4 - In each block set value of the string variable (from step 2) to the column from where email address has to be obtained

5 - Create a single send email action with To being the string variable (from step 4)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up.

[FLMike]

@Kiara1987 

First I want to call out @happyume  for a very good solution. I am going to give you a different one, but its just a different way than @happyume  showed.

This is just slightly different and lol may look

1. I am going to assume you have the email address on the line where the person is selected. If not while we can look that up at run time, its simpler if its in there. Please let me know if you plan to add it or not. 

2. In the Trigger we are going to set conditions that it only fires when the Status is Pricing or In Progress.

-Click on the three ... for the trigger

-Click settings

-at the bottom you will add a condition

-In this case where you see the word MyStatus, that is MY fields name, you need to put YOUR field name. And assuming yours is like my list it will trigger

@or(equals(triggerBody()?['MyStatus']?['value'], 'In Progress'),equals(triggerBody()?['MyStatus']?['value'], 'Pricing'))

3. Add your Send Email

Now, I do not know what the content will be. I am not 100% sure whether we can dynamically generate it or you even like it but.

Here is what I did.

--In the Send

If the value is In Progress (and again make sure to change MyStatus to your field name), AND assuming you put the Email Addresses for Project Manager and Designer IN the row, you should have their Dynamic Emails listed

if(equals(triggerBody()?['MyStatus']?['value'], 'In Progress'), InsertDynamicPropertyForTheDesignEstimator, InsertTheProjectManagerDynamicEmailPropertyHere)

In the Subject

I put

Hello this is an email message (whatever)

In the Body

Now.. this is where I do not know if you want to send a completely different worded message to each person or if its just a generic message letting them know the project is ready for them?

So if its just generic, then you are good.

If you want a custom message per person, then we would have to again, check if its the In Progress or not.

@Kiara1987  I know this might look like alot because I typed alot, but the flow looks like this.

If you like my answer, I would really appreciate if you please Mark it as Resolved, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[Kiara1987]

Hi Mike, 

I can't seem to see anything about settings to do the trigger you are talking about.

I have added "When an item is created or modified"

When selected it just asks for the site and list but I can't see any "..." to open apart from this one?

[FLMike]

Ohhh @Kiara1987

that is a recurring trigger. So we would not add another trigger. Do you want this scheduled or do you want it to happen as soon as they save the Setting to In Progress or Pricing?

I assumed you wanted it in real time.

[Kiara1987]

Yeah in real time as its changed. and with regards to the email address, the field setup is "person or group" so i have ASSUMED that flow is smart enough to get the email from that?

[FLMike]

@Kiara1987 

Hi, then you need to remove that reocurring trigger and use the trigger I had.

Yes you will be able to get the Email addresses from that field. However, you would need 2 fields, since you have emails for Project Manager and for Designer (you probably have 2, I am checking hehe)

[Kiara1987]

Hi Mike, 

So sorry but i still dont understand. I have added the 'trigger' "When an item is created or modified" and selected my site and list.

Where do i find this to go to settings to show a condition? 

When I click on the three dots (highlighted above), i click settings and it shows this menu that doesn't say anything about condition? 

What am I doing wrong?

[FLMike · Accepted solution]

@Kiara1987 

You first have to remove the reocurrence trigger and the wait, which you cannot add like this.

Then re-add JUST the when an item is created or modified trigger

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[Kiara1987]

Thank you Thank you!! it has worked perfectly. Really appreciate your help!😀

[Kiara1987]

Solved: How to send email based on two columns in SharePoi... - Power Platform Community (microsoft....

Hi Mike,

I have another question about this post .... I was thinking of creating a separate flow for each 'status' in my list of jobs as i have 8 different statuses with different recipients.

I have been using the triggers you sent me:

@or(equals(triggerBody()?['MyStatus']?['value'], 'In Progress'),equals(triggerBody()?['MyStatus']?['value'], 'Pricing'))

So this is staying when the Status = "In Progress" or when the Status is "Pricing"

I want to create one that just says

When the status is "In Progress" .... and then send email to user in the list. in the next step.

I figured because I am only looking at one criteria (In Progress) that the trigger text could change and not need to have the second part (that refers to "Pricing")

How would I form this trigger formula?

THEN....

When it comes to the email part, the trigger you sent me says 

if(equals(triggerBody()?['MyStatus']?['value'], 'In Progress'), InsertDynamicPropertyForTheDesignEstimator, InsertTheProjectManagerDynamicEmailPropertyHere)

I just want it to say If the Status is "In Progress" then email InsertDynamicPropertyfortheDesignEstimetor

How would i form that trigger formula?

Hoping you can help... again 🤔