Creating an HTML table from filtered array in a loop

[CassiL]

Hi all. I've tried unsuccessfully to find an answer to my question.

My flow takes an input array of employee names, and filters an excel table by these names, creates an HTML table, and emails it out to each individual employee.

The above works just fine. My issue is now I wish to only fetch records from last month and this month.

loop through name array and filter by employee

Filter array expression: int(formatDateTime(item()?['DateSubmitted'], 'yyyy'))

The filter array step produces the correct output - however I cannot figure out how to use it to create the HTML table. Power automate forces me into some loop with the filter array output body, and items. I can't access the data I need to produce the columns, either.

Can someone advise?

[v-bofeng-msft]

Hi @CassiL ,

You could use 'Parse JSON' action to get the dynamic content of the 'Filter Array' and then use the dynamic contents to fill the Create HTML table.

Best Regards,

Bof

[grantjenkins]

For your Create HTML table you should be using the output from your Filter array. Then to get the values you would use the following expression format.

item()?['NAME_OF_YOUR_FIELD']

//Example, if your field name was Billable, you would use the following.
item()?['Billable']

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[CassiL]

Thanks for the idea - it doesn't seem to work

This is what happens - it forces me into a loop from the filter array, and then when I want to put the table into the body of the email, it isn't available to select the output of the html table here.

[CassiL]

I'm not really familiar with the parse JSON step - can you please elaborate?

[CassiL]

@v-bofeng-msft 

I did attempt the parse JSON and was able to turn the filter array into objects, but I had the same issue as using the Filter Array body - it forces me into a loop to create the table, and it is unavailable to use the output of that html table when I compose the email.

Here's the full steps:

Prior to the loop is simply the recurrence and some variables.

[grantjenkins]

@CassiL Are any of the fields (Billable, Client, etc.) you're mapping multi-select fields (contain multiple values)?

[CassiL]

@grantjenkins 

This is a spreadsheet of employee submissions. So all fields have many values, if that answers your question. There are multiple employees, and the date ranges from November 22 to current, and will continue forward. 

[CassiL]

@grantjenkins  @v-bofeng-msft 

Let me try to explain a little better what I'm after, and perhaps the method I'm trying to use is the problem.

I have a large spreadsheet of information that I wish to parse. 

This spreadsheet contains employee submissions, with a bunch of different fields, like date, employee name, the client billed, if it's billable, etc.

What I am trying to do is send out an automated e-mail every Monday morning to each separate employee with their submissions. I would like this to be only the last month's and this month's submissions.

I have successfully implemented this without the date filter. It's the addition of this second filter that is causing me issues.

Maybe there's just a better way to do what I'm trying to do.

I would very much like to have this finished before it fires again Monday.

[grantjenkins]

Are you able to show your full flow?

If you're using the output of your Filter array as input (From) for your Create HTML table, it shouldn't be putting it into an Apply to each. Can you confirm exactly what causes the Apply to each to show up? Is it related to one of the other actions such as your Send an email for instance.

[CassiL]

@grantjenkins 

I must have put in the expression wrong when I tried this last time. It works now. Thank you.

Can you point me at a good way to filter for items for last month and this month only? I'm not familiar enough with power automate to do that. I know the steps for coding this, but power automate is different.

[grantjenkins]

Hopefully this will get what you're looking for. I've based it off the Created date, but any date field would work. Effectively it checks to see if the date is greater than or equal to the start of last month, and less than the start of next month.

Created ge '@{startOfMonth(addToTime(utcNow(), -1, 'Month'))}' and Created lt '@{startOfMonth(addToTime(utcNow(), 1, 'Month'))}'