Hi, guys! With some help from you all, I've built a flow which pulls data from an Excel row, turns it into an HTML table, and then sends an email with the table as a .doc attachment.
Here's the flow (the condition is just to filter the data down for testing, and the first two compose actions are just to reformat some dates):
This flow currently sends one email per row with just the data from that row in the attached table. This is an improvement from sending all the rows in each email, but I realize now I need to be able to group rows with the same ID into one table and email. Is this possible to do in Flow?
Here a sample from the data set:
Thanks in advance for your help!
Solved! Go to Solution.
Hi @thejack0fhearts,
Could you please share a bit more about the ID that you mentioned? Is it the row id of the excel file or the ID of the client?
Further, could you please show a bit more about the data structure of your excel file?
If the ID that you mentioned is the row id of your excel file, I think it is impossible to do that in Microsoft Flow currently. Each row of your Excel file has different row id, so we could not group rows with same row id.
If the ID that you mentioned is the ID of the client, I suppose that you add a ID column within your Excel file, is it true? I have create a Excel file in my OneDrive for Business folder and the data structure of it as below:
Note: The ID column is a custom column which is used to identity a client.
If you want to group rows with same client ID, I have made a test on my side and please take a try with the following workaround:
union(variables('Client ID'),variables('Client ID'))Add a "Do Until" action, within Condition box, click "Edit in advanced mode", type the following formula:
@empty(variables('Client ID'))Within "Do Until" action, add a "Filter array" action, From set to output of "Get rows" action, then click "Edit in advanced mode", type the following formula:
@equals(item()?['ID'], first(variables('Client ID')))
Add a "Select" action, From set to output of "Filter array" action, within Map entry, type the entries as below:
Note: Within Enter key box, type the Column Name. Within Enter value box, type the following formula:
item()?['ColumnNameOfYourExcelFile']
For Excel table that contain column names with spaces, we should specify each space as "_x0020_". More details about the usage of "_x0020_", please check the following article:
https://docs.microsoft.com/en-us/powerapps/common-issues-and-resolutions#recently-addedchanged
Add a "Create HTML table" action, From set to output of "Select" action. Then add a "Send an email" action, Attachments Name field set to GroupRows.doc and Attachment Content set to output of the "Create HTML table" action.
Add a "Compose 2" action, Inputs set to following formula:
skip(variables('Client ID'),1)
Add a "Set variable" action, Name choose Client ID and Value set to output of "Compose 2" action.
Image reference:
The flow works successfully as below:
Best regards,
Kris
Hi @thejack0fhearts,
Could you please share a bit more about the ID that you mentioned? Is it the row id of the excel file or the ID of the client?
Further, could you please show a bit more about the data structure of your excel file?
If the ID that you mentioned is the row id of your excel file, I think it is impossible to do that in Microsoft Flow currently. Each row of your Excel file has different row id, so we could not group rows with same row id.
If the ID that you mentioned is the ID of the client, I suppose that you add a ID column within your Excel file, is it true? I have create a Excel file in my OneDrive for Business folder and the data structure of it as below:
Note: The ID column is a custom column which is used to identity a client.
If you want to group rows with same client ID, I have made a test on my side and please take a try with the following workaround:
union(variables('Client ID'),variables('Client ID'))Add a "Do Until" action, within Condition box, click "Edit in advanced mode", type the following formula:
@empty(variables('Client ID'))Within "Do Until" action, add a "Filter array" action, From set to output of "Get rows" action, then click "Edit in advanced mode", type the following formula:
@equals(item()?['ID'], first(variables('Client ID')))
Add a "Select" action, From set to output of "Filter array" action, within Map entry, type the entries as below:
Note: Within Enter key box, type the Column Name. Within Enter value box, type the following formula:
item()?['ColumnNameOfYourExcelFile']
For Excel table that contain column names with spaces, we should specify each space as "_x0020_". More details about the usage of "_x0020_", please check the following article:
https://docs.microsoft.com/en-us/powerapps/common-issues-and-resolutions#recently-addedchanged
Add a "Create HTML table" action, From set to output of "Select" action. Then add a "Send an email" action, Attachments Name field set to GroupRows.doc and Attachment Content set to output of the "Create HTML table" action.
Add a "Compose 2" action, Inputs set to following formula:
skip(variables('Client ID'),1)
Add a "Set variable" action, Name choose Client ID and Value set to output of "Compose 2" action.
Image reference:
The flow works successfully as below:
Best regards,
Kris
Thanks for your help, Kris! I used a combination of your flow and the flow listed here to make it work. I ran into the 2048 row limit from Excel, but everything else seems to be working.
Thanks again!
Thanks Kris
This is very helpful. However I am facing a challenge in the number of emails that get triggered basis this flow. The equiavalnt of ID for me is Business leader. However I have the following 2 challenges
(1) How do I trigger a flow where the email is sent to the each business leader just for his/her portion of the excel sheet
(2) How do I adjust the count in the do until based on the number of business leaders. For example if I have 5 business leaders do I need to manually choose 5 in the count column in do until ?
Thanks a lot in advance for your help
User | Count |
---|---|
75 | |
50 | |
49 | |
27 | |
19 |
User | Count |
---|---|
28 | |
27 | |
25 | |
20 | |
19 |