How to generate reports from SP list with power automate using nested arrays

Question

I have difficulties with building a nested array in Power Automate. My problem has some preamble, but I think I have to explain what I want to do, before I can get to the core of the problem.

I want to send a daily digest email about to individual approvers. The daily digest should list the calendar entries the approver is responsible for and which are "pending". Approvers are responsible for certain employees. A calendar entry has a field employee. Approvers are hence responsible for a calendar entry exactly when the employee is selected in that entry.

Difficulty here is, that I have to check a lot of entries and I do not know in advance, if there will be work for any of the approvers to do.

Data is in two lists, of which I try to depict the columns used in my flow:

List A (actually a modified calendar)

column name: ID | Start | End | Employee                   | ApprovalStatus
type:           | date  | date| lookup->List B, col. Title | 

List B (custom list)

column name: ID | Title | Approver 
type:           | String| People selector

What my flow already does/should do is this: Things that don't work, or are not done yet have a * in front

Initialize an array "GID"
GetItems on list A, with OData filter set to only retrieve ApprovalStatus=Pending
Apply to Each item from list A
{
   extract ID from field "employee" from current item
   GetItem on list B using the extracted ID
   Check if "Approver" from this GetItem is already present in "GID"
      Append "Approver" to "GID" if not
   *Append calendar data to an nested array within "GID" behind the "Approver"
}
*Do a nested Apply to Each over "GID" and compose the email for each "Approver"

My idea is, to create something like an array of an array to keep the calendar entries for each of the approvers. But I'm totally clueless how to do that. Perhaps my Idea is stupid from the very beginning and the task could be performed much easier.

In the end, an approver should receive a daily email containing something like this:

Hello X, there are calendar entries for you to approve:

Employee Y, startdate, enddate, ...(additional information on the entry)

Employee Z,....

Employee Y,....

Employee A,....

Please log in to approve or decline. (link to sharepoint approval view of List A)

However I do not find any hints how to successively build such a nested array. But maybe I just do not see a much simpler solution.

Answer 1

For the array part, my suggestion will be using the Select action and Create HTML table action. The select action can use the value from get items and pick up the required columns, then format. The output can be converted to table with Create HTML table

For more detailed steps, please refer to this tutorial.

The output is like this:

And for filtering with both approver and approval status, something for your reference. We can create another flow to update the list A with a people picker column. The mechanic is simple, get the lookup id, then get the item in list B, we can obtain the approver claim. Update the list A approver column with the claim. In this way, you shall be able to add odata filter in the get items action of your original flow to filter both approver name and status at the same time. Then covert the filtered value to table and send it.

Note: The flow to update list A may meet the problem with infinite loop if you need to use the item modified trigger(Because we update the list A with the flow, which modified it.). You can try to add a trigger condition to skip events kicked by the flow creator.