0

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.

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:

enter image description here

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.

4
  • Nice point. As there already exists a flow that is updating items in List A after every edit, it should not be a problem to enhance it with copying the approver ID. It's a pity that sharepoint doesn't allow to import a people picker column from List B alongside the lookup column. This works with text columns but not with complex columns. – Ariser Jan 28 at 7:45
  • @Ariser. Glad to be help. Just let me know if you have any problem during the process. And if it works, please accept the reply as answer. – Jerry_MSFT Jan 28 at 7:50
  • The link seems to be broken, btw. Concerning the autotrigger problem I invented a nice method to discern changes made by a flow vs. made by humans. When a flow edits an element which can trigger itself or another flow, it will write something special into a column. The easiest thing is to save the version number of the element, it will have after the edit. Then I can check on a flow trigger if both fields match and stop execution. Can be extended to be more idiot proof though. – Ariser Jan 28 at 7:56
  • @Ariser. Sorry for that. I updated the link, it shall work now. For the infinite loop, your way seems to be fine and just test with it. And I used to see examples to make the trigger avoid the creator of the flow or to ensure the modified time is earlier enough compared to current. Just options you can take reference when you want another way. – Jerry_MSFT Jan 28 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.