Reps list from SharePoint based on Issue Status and Supervisors

[ReyOrtiz]

Hi

I need some guidance if is possible. I am trying to create a notification email that will be send once a week to the supervisor that have reps with Pending items. Right now, the reps get a notification every 3 day letting them know they have pending items. But every Monday I want the supervisors of those reps to get a list of reps that have pending items.

For example, the sup email ideally will look like this

The following Reps from your team currently have the following tickets in Pending status

Ticket number                   Rep name

122                                         John Smith

124                                         Sandra Smith

The SharePoint list will look like the following:

Ticket	Rep Name	Rep Email	Supervisor	Sups Email	IssueStatus
122	John smith	jsmith@test.com	Cathy Miller	cmiller7@test.com	Pending
123	Rebecca trainer	rtrainer@test.com	Eddie Tucker	etucker3@test.com	Pending
124	Sandra smith	ssmith@test.com	Cathy Miller	cmiller7@test.com	Pending
125	Carlos trainers	ctrainers@test.com	Cathy Miller	cmiller7@test.com	Completed

[v-xiaochen-msft · Accepted solution]

Hi @ReyOrtiz ,

Let's say the columns are all text columns. I made a sample.

Best Regards,

Wearsky

[ReyOrtiz]

Thank you for the respond 
I keep getting this error
The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(item()?['Supervisor'], item('Apply_to_each')?['Supervisor'])' failed: 'The template language function 'item' must not have any parameters.'.

[VictorIvanidze]

There should be 

items('Apply_to_each')?['Supervisor']

[ReyOrtiz]

I miss read the Apply for each expresion I created it as "item", and it was supposed to be "items" which did the trick. My issue now is that the ticket column is not showing since it is a numerical column that auto-generated the number as the case comes in.

So it is shown in table format; ticket numbers and the reps name but only the reps name is populated, any suggestions on this? thank you again for all the help

Best Regards 
Rey O

[ReyOrtiz]

@VictorIvanidze & @v-xiaochen-msft 
I was testing the flow with my email, and it worked when I added the Expression into the "TO:" section on the send email section, it gave me an error. expression= items('Apply_to_each')?['SupsEmail']

Parameter 'To' cannot be null or empty.
clientRequestId: 40f586c5-d495-4b49-b154-ffea328c36a1

Any suggestions

[VictorIvanidze · Accepted solution]

Check if the value of items('Apply_to_each')?['SupsEmail'] is null or empty. 

[ReyOrtiz]

@VictorIvanidze & @v-xiaochen-msft 

Thank you for the help; the issue was a space in the wrong place