Need to trigger Single email for same set of records in Excel using Microsoft Flow

[simhadri]

I have below records available in excel, So now I have to read Onedrive excel and then send the email notification to the employees by Group Wise only one notification per user using Microsoft Flow.

EmpID EmapName Start Time End Time Email ID
1 raju 22/8/2017 11:00:00 22/8/2017 22:00:00 raju_s@hotmail.com
1 raju 23/8/2017 11:00:00 23/8/2017 22:00:00 raju_s@hotmail.com
2 kumar 22/8/2017 11:00:00 22/8/2017 22:00:00 kumar@hotmail.com
2 kumar 23/8/2017 11:00:00 23/8/2017 22:00:00 kumar@hotmail.com
  

Email Notification per user as mentioned in the below format

Subject: [Empname Dynamic] Swipe Report

email Body - Please look your swipe in / Out details.

Start Time End Time

22/8/2017 11:00:00 22/8/2017 22:00:00

23/8/2017 11:00:00 23/8/2017 22:00:00

same for the second user also we need to send only one notification to the other users with their details.

Can you help how to achieve this in Microsoft Flow

[v-micsh-msft · Accepted solution]

Hi @simhadri,

Please take a try with the following workaorund:

1. Add a proper trigger, for testing purpose, here I use the Flow button trigger,

2. Add the Excel Get Rows, specify the OneDrive connection, locate the file and the formatted data table,

3. Add a Select Action, configure From to Value content of Excel Get Rows, under the map entry, Enter enName, then on the right side, select the EmployName as value input,

4. Add a Compose Action, Input the following formula, which is used to distinct the EmployeeName,

"@union(body('Select'),body('Select'))"

5. Add Variable->Initialize Variable action, Name set to NameArray, type set to Array, Value set to the Compose Output,

6. Add a do until, condition set to 

@empty(variables('NameArray'))

7. Within do until, add a Filter Array, From set to Value of Excel Get Row, condition set to the following:

@equals(item()?['EmployeeName'], first(variables('NameArray'))?['EmName'])

8. Add another select (Select_2) after the Filter Array, From set to Body of Select Array, map set to:

  StartTime :  formatDateTime(item()?['StartTime'],'dd/MM/yyyy HH:mm:ss') ----Use Expression

  EndTime  : formatDateTime(item()?['EndTime'],'dd/MM/yyyy HH:mm:ss') -----Use Expression

9. Add Create HTML table action, From set to the output of the Select_2, including Header,

10. Add the Send an Email Action,

To set to Expression: first(body('Filter_array'))?['EmailId']

Subject set to Expression: first(variables('NameArray'))?['EmName']

Body set to: Please look your swipe in / Out details: Output content from Select_2,

11. Add another compose (Compose_2), inputs set to:

"@skip(variables('NameArray'),1)"

12. Add Set Variable, choose NameArray, value set to Output of Compose_2.

Image reference:

Running results:

Regards,

Michael

[simhadri]

I have tried below solution but seems not getting the required results, Can you share your email id so that i can give Edit access to my flow , So that you can guide me .

[v-micsh-msft]

Hi @simhadri,

I don't think my Email account would be allowed to edit the flow created in your organization.

If you have any trouble following the steps I posted earlier, please let me know, and I will try to explain it in a more clear way.

Regards,

Michael

[angus]

Hi

Just wanted to say thanks - this saved me a load of time doing a similar task on SharePoint list items.

Could you recommend any documentation for Flow functions (e.g. union), as the documentation seems a bit hit and miss.

thanks

Angus

[sunithav]

Hi Angus,

I'm trying to group sharepoint list items by a column and tried the steps explained for excel file. However i couldnt get it working. Would you be able to share your implementation here please so that people who are trying to do the same would be benefited?

Thanks,

Sunitha

[Angusham]

Hi

Yes, no problem.

I basically replaced the Get Rows in the example given here with a Get Items from a SharePoint list.

The Select was from the value of that Get Items, and I selected the field I wanted to group by. (EmailAddress in my case).

I added the compose with the expression:

union(body('Select'),body('Select')) (I confess I just copied this from the example!)

The name array and doUntil were the same as above (although I called by field Requestedby in the name array).

The filter was the tricky bit - I set this as follows in the expression builder:

equals(item()?['Requested_x0020_By']['Email'], first(variables('NameArray'))?['RequestedBy']) 

Bear in mind that Requested_x0020_By was the field in my SharePoint list that was a person, so ['Email'] gave me their email address. In the NameArray, I'd called the field RequestedBy.

In the Select2, I had to use the Expression builder to specify the fieldnames to extract from the SharePoint list, as mentioned above - so for example item()?['EndTime'] to get the EndTime from the SharePoint list.

After that I pretty much followed the instructions above.

Please reply/let me know if there's a specific stage causing you an issue.

thanks

Angus

[Perez]

Hi @v-micsh-msft,

I’m trying to use your suggestion here to my flow but failed to do it...

I have an excel with list of project risks, each risk in the excel is assigned to a specific owner.

I need the flow to send a mail to each owner with all the risks assigned to him.

I used the select-->compose--> initiate variable as you suggested below and it works fine as I can see when running the flow.

my issue is that when running the filter in the do until loop I can see the input is received, but output is "[]"

filter expression: @equals(item()?['Owner'], first(variables('OwnerArray'))?['ItOwner'])

where:

Owner, is the value from the excel output

OwnerArray, is the name i gave to the array in the Initialize varaible action

ItOwner is the name in the select

configuration screenshots:

results screenshots:

will appreciate if you can assist with understanding what went wrong with my filter.

if somthing is not clear in my request i will be happy to farthere elaborate.

thanks in advance

Eyal

[Perez]

Hi @v-micsh-msft,

I’m trying to use your suggestion here to my flow but failed to do it...

I have an excel with list of project risks, each risk in the excel is assigned to a specific owner.

I need the flow to send a mail to each owner with all the risks assigned to him.

I used the select-->compose--> initiate variable as you suggested below and it works fine as I can see when running the flow.

my issue is that when running the filter in the do until loop I can see the input is received, but output is "[]"

filter expression: @equals(item()?['Owner'], first(variables('OwnerArray'))?['ItOwner'])

where:

Owner, is the value from the excel output

OwnerArray, is the name i gave to the array in the Initialize varaible action

ItOwner is the name in the select

configuration screenshots:

results screenshots:

will appreciate if you can assist with understanding what went wrong with my filter.

if somthing is not clear in my request i will be happy to farthere elaborate.

thanks in advance

Eyal