How to use condition to determine whether need to send mail

[york]

Hi Everyone:

  I have a flow that create the HTML table from SharePoint List, then it will send mail through Outlook, the list have 2 columns, one is the "Review Status" and the data will be "waiting" or "Approve", the other is "Reviewer's mail" and the data is the mailbox of the reviewer,  the following is my current flow; 

   1.Get items from the list, the filter query is if the "Review Status" is equal to "waiting";

   2. Create HTML Table;

   3.Send Mail to the "Reviewer's mail" in the list;

   I have 2 questions that need your help:

   1.If there is no "waiting" in the current list, how can I use condition to make the step 2.3 won't happen;

   2.If there are 2 lines that the "Review Status" is "waiting", I just want to send one email to the 2 “Reviewer's mailbox”, but now it will send twice, how can I make it just send once?

[gabibalaban]

@york ,

Point 1:

Use a condition control to check if the outputs('Get_items')['body/value'] is empty. On the No branch put the step 2 and 3. 

Point 2:

First of all, you need to identify all distinct Reviewer's mail and for that you need to:

- select from outputs('Get_items')['body\value']  Reviewer's mail column values with a select action;

-identify distinct Reviewer's mail value using union(body('Select'),body('Select')) in an compose action on the results of previous select action.

Then you need to iterate through all the distinct values of Reviewer's Email using an apply_to_each action and filter the Get_items outputs based on the current value.

After that, inside the apply_to_each action create the HTML table with the outputs of the Filter and than a send_an_email action with the current reviewer: 

To: items('Apply_to_each')['reviewer_column_name']

Body: body('Create_HTML_table')

Please feel free to ask anything regarding the solution.

Hope it helps !

PS: I'm not comfortable at all with the Reviewer's mail column name, it is more efficient and intuitive to avoid special characters in columns name in Sharepoint

[york]

Hi  gabibalaban:

   Thanks for your help ,I still have questions when I want to run flow like what you said above, could you help solve it?

1. in the point2,

   1.1 the Map in my picture is right? Is it enough that  I just need list 1 line in the map?

   1.2 in the compose, the union has 2 selection, does it because that I have 2 lines in my list? but in fact I don't know how many lines in my list, 2 lines is just an example;

   1.3 in the filter array, the left is item(), and the right is items(outputs('Compose_5')), right? when I want to storage the flow ,it remind me like the picture;

   Thank you very much and looking forward to your reply.12

[gabibalaban]

@york ,

1. Yes, that's correct, you only need the reviewer email.

2. union(table,table) gets in fact  the distinct items of the table.

3. I think you miss a step from my example Apply _to_each and all actions after are executed inside. And the formula is: items('Apply_to_each')['Mail'] (the name of the column defined in the select action, I think my previous explanation is not quite accurate 😞 ).

Let me explain what each action do:

1. Select action - get only the reviewer's email  column from the Get Items outputs.

2. Compose 5 - union(body('Select'),body('Select')) - returns the distinct values of reviewer's email.

3. Apply to each (the one you've missed) - iterate through all items from Compose_5 outputs (if you have many reviewer's email, execute the following (for inside) actions for each).

4. Filter array - Filter the outputs of Get_items action based on the current value of iteration of reviewer's email (apply_to_each).

[york]

Hi gabibalaban：

   Sorry for bothering you again...I still have 2 questions, hope you can help me with it;

1. Please check whether the filter array is right in the picture;

2. What need to fill in the Send an mail "TO"? When I put apply ro each in the "TO", the flow doesn't work; When I put apply to in the body , the mail will be sent twice(if I have 2 rowss in the list), and the body in the two mails will be {"Mail":"123@abc.com"} and {"Mail":"234@abc.com"}, could I just make the outlook just send 1 mail to the two receiver together?

?

[gabibalaban · Accepted solution]

@york ,

No worries ... 

1. Item()['Approver Email'] ;

2. Oh.. So, your list does not contain multiple items for a single reviewer. Well, in this case, my scenario was wrong from the beginning. In fact, I overcomplicate a simple problem. 

 What you need to do is to initialise a string variable to keep the reviewer emails (step 1).

    Check if the results are empty (step 3)

    Iterate through the get_items results using a Append to string variable action  and put in value (step 4):

items('Apply_to_each')['Email Reviewer'];

Hope it helps !