Email HTML Table with Filtered Excel Results

[jaxisland]

Hello,

 I am trying to build a flow that looks at an excel file, based on a column's date value it returns rows older than today, this I have set correctly and my Filter array step is returning the correct values. Now I am trying to take those results, create a HTML Table, then I will add that table to an email and send it out. 

My problem is as soon as you select any value from the List rows present in a table action it automatically wraps that step in a For each loop. So in my current example I am trying to select specific columns to be added to the table and it wrapped the Select action in a For each loop. In my Create HTML table step I cannot select the output of the Select action, it does not show as dynamic content.

If someone can help me take the results of the Filter array step and put that together in a table I would appreciate it, thank you.

[CFernandes]

@jaxisland - The problem here is you have a "For each" loop which and you cannot access the output of the select outside the For Each Loop? Is my understanding correct?

[jaxisland]

Correct now being fairly new to Power Automate I may be doing this incorrectly but I want to use those fields from the List action but when I add them to the Select action it automatically wraps it in that For each loop and then I cannot select the output. 

[CFernandes]

@jaxisland Please try this,

• Initialize the variable.
• Set the variable inside the loop.
• use the variable outside the loop.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.

[jaxisland]

Just to clarify, if I have 5 columns I want in the table I have to do the Init, Set, and Compose 5 separate times?

[CFernandes]

@jaxisland - Please try out the logic... You should just need to set once...

[jaxisland]

So I think I am doing something wrong. 

When I try to use the Set variable to the Init variable 1 it automatically rapped that in another For each loop

[CFernandes]

@jaxisland - This does not look correct... Can you switch back to Classic and remove the set variable, remove the for each and try to set it again. 

[jaxisland]

Here is the new flow: 

here is the error:

[CFernandes]

@jaxisland No loops 😐

 Just did a TEST. Did you use New Designer ?

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.

[jaxisland]

I switched back to Classic, one thing I noticed is your Select is pulling from the spreadsheet, mine needs to pull from the Filter array as there are only specific rows I want to add to the table. My Select is using the Body from the Filter array action above. 

[CFernandes]

@jaxisland 

I think I understand now - 

Note: In Select you need to use item()?['<column name>']

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.

[jaxisland]

Sorry for the delay, I have updated my flow but the last thing I need to do is to take those results and put them in a HTML Table so I can put that in an email. When I use the Create HTML table action using the created variable it fails saying: "The 'from' property value in the 'table' action inputs is of type 'String'. The value must be of type 'Array'."

[jaxisland]

*bump*

Still not able to get the flow working

[jaxisland · Accepted solution]

So I was able to get this working and will post for future viewers.

1. Recurrence: set to daily at 0800

2. List rows present in a table - just connected those values to my sheet with the table of data

3. Filter array - I only wanted results from the sheet that had a date in a column older than today

4. Select - I mapped the column name to the data from the filter array, I called out specific columns in this step to break out the data, this avoided me having to deal with the for each loop automatically being used. I also used this step to format my dates to yyyy-mm-dd so its consistent.

5. Create HTML table - used the output from the Select action above

6. Compose - Added some styling and formatting to the Outputs of the Create HTML table step, makes the email look nicer

7. Send an email (V2) - This has some custom text and the output from the Compose step above

8. Post message (V2) - This is configured to run after if the step before has failed, skipped, or timed out. Just posts a message in Slack to let me know there was something wrong with the run. 

One thing to note is in the excel sheet if the table contains extra rows of blank data it fails on the Select step. I am sure its just in how I am handling the data and I am sure there could be a better way but this works for me. Thats why I added the Slack message to know if something fails during the run.