SharePoint list, filtering items and compiling an email with complex array

[allan_t]

I'm having trouble with this use case and not sure how to handle one of the array columns.   

SharePoint list contains request items.  I want to send an email monthly that lists the open items for each Submitter.  Everything works except I don't know how to handle one of the complex columns in the output email.   

SP list design

Column type	Contains
Text	Request title
Person (single)	Submitter person
Person (multiple)	Assigned people
Choice	Status value, single choice

Current flow design, it works except the final output does not contain a column identifying who the Assigned people are

1. Scheduled trigger
2. Get items
3. I use a loop to add all the Submitter Emails to an array variable and the Submitter DisplayNames to an array variable

4. I then use a Compose action with Union function to remove duplicate Submitter Emails, the output of this compose action is to create another array variable with Submitter Emails (no duplicates)

   

5. I then loop through each of the Submitter Emails in the array to filter and select the items for each Submitter.  The filtered and selected items are then put in an HTML table and email sent.  

   

This solution works, however I would like to add a column to the HTML table the identifies the Assigned people.  The Assigned people is a Person array within the Get items array.  I thought of creating a string variable, and appending the DisplayName of each Person assigned.  I don't know how or where to create this loop since I'm already using a filter and select function in this loop of each Submitter.   

Any help would be greatly appreciated.  Thanks.   

[David_MA]

I think I know what you mean.

1. First, create your array to get your submitter values.
2. Then add another apply to each on the values in the array of submitters. This would be outside of the apply to each to create the array of submitter values.
3. Within this apply to each, add a Get items action but filter it to only return the items for the current submitter in the apply to each action.
4. You can then use the Select action to create the HTML table of that person's items.

[allan_t]

@David_MA Thanks for the suggestion.  In your step #3 when using the Get Items action, I thought you could only use the OData filter query on string and number values.  Is there a way to filter the Get Items action on a person column?  Or do I have to Get all the items again, and then use a subsequent Filter action after retrieving them?  

[David_MA]

On a people field, you can use this type of expression for the filter query:

Author/EMail eq '@{triggerOutputs()['headers']['x-ms-user-email-encoded']}'

The above is based on the created by field. Use the internal name of your people field, and follow it with the backslash and EMail (/EMail) to return the e-mail address stored in the people field. Then filter to where it equals the current item e-mail address. Note, the M in EMail needs to be capitalized.