filtering data and outputing into a HTML table

(0) Share

Report

Posted on by loha

Hi, I've been trying to filgure this out for longer than I care to admit. I've tried multiple different ways but have yet to solve it.

I have a SP list, I'm using GET ITEMS to bring it in and using an ODATA query to pull in only rows that are null in one column. This part is working fine, I have a compose to check the length of the output at this stage.

Next I need to filter the data again and group it by a line manager email field (single line of text), then also group it by head of department email field. After all this I need to output it to a HTML table, grouped by their line manager reports which will be emailed to each of the HODs, none of them will see the other departments data, only their own.

I have got the emails working to send to the first line managers and its outputting to HTML table. But I'm really struggling to get it to next level head of department, grouping their line managers data and outputting only a count of how many rows their are for each line manager in their department.

This is my Line manager flow (which is working):

The second Get items filters for the same null value in one column as the first Get Items, and also checks for the line manager email match from the COMPOSE EMAIL TO step just above it.

My HOD flow only needs to count the number of rows per line manager within their department, no detail is required. Am I missing a really simple way to do this?
My goal is to have an email going out to each department head containing a HTML table which has a list of the line managers that report to them along with a count of the rows related to them from the SP list.

Any help much appreciated, I've followed several youtube videos from Reza Dorrani, articles by Matthew Devenney, all have been fantastic but I just can't work this flow out.

Categories:

Business Process Flows

All responses (2)

Answers (0)

Chriddle 7,791 Super User 2025 Season 1 on at

Like (1)

Report
filtering data and outputing into a HTML table

Here the data is in a Compose and it looks like this:

[ { "Title": "Title 0", "Manager": "jim@example.com" }, { "Title": "Title 1", "Manager": "jim@example.com" }, { "Title": "Title 2", "Manager": "john@example.com" }, { "Title": "Title 3", "Manager": "jim@example.com" }, { "Title": "Title 4", "Manager": "jim@example.com" }, { "Title": "Title 5", "Manager": "jane@example.com" }, { "Title": "Title 6", "Manager": "jim@example.com" }, { "Title": "Title 7", "Manager": "jane@example.com" } ]

Apply to each

Get unique managers

union( xpath( xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))), '//Item/Manager/text()' ), json('[]') )

Select

From:

Get the XML nodes for the current manager

xpath( xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))), concat('//Item[Manager="', items('Apply_to_each'), '"]') )

Map:

Create a PA object from the XML nose

json(item())['Item']

You get 3 HTML tables like this that you can put into the emails
Suggested answer

Ellis Karim 11,051 Super User 2025 Season 1 on at

Like (0)

Report

filtering data and outputing into a HTML table

First, in your Select LM Emails action, you need to switch to Text Mode

Here is an example flow:

Please see the following tutorial:

Excel and Power Automate: How to Combine Multiple Excel Rows into a Single Email for Each Recipient – Ellis Karim's Blog

Ellis

https://elliskarim.com