Send a single reminder for multiple items using Lists and Power Automate (UPDATED)

Note: The steps in the Create HTML table action have been updated from my original post, on Dec 21, 2020, to address an issue where the Filter array columns were no longer available in Power Automate.


This post is inspired by a question I received from a previous blog post where the user wants to send a single email reminder for multiple Microsoft List items. The typical Power Automate pattern is one reminder per item. Consolidating items, by owner for example, is harder that you would think. I am using the Issue tracker template for my demo and we will send a single email message for all open items assigned to the same user on a weekly schedule.

Create a new Flow from Power Automate > Create new > Scheduled cloud flow. Provide a Flow name, i.e. “Issue tracker – Weekly report” and click “Create”.

Scheduled cloud flow

Insert a SharePoint – Get items action and set:

  • Site Address and List Name to your target site and list
  • Filter Query to: Status ne 'Completed' or Status ne 'Duplicate'
SharePoint - Get items

Insert a Data Operation – Select action and set:

  • From to “value” from the previous SharePoint – Get items action
  • Map to “Person or group the issue is assigned to” from the previous SharePoint – Get items action
Select

Insert a Initialize variable and set:

  • Name to “varAssignedto”
  • Type to “Array”
  • Value to the following expression: union(body('Select_-_Assignedto'), body('Select_-_Assignedto'))
Initialize variable

Save and test your Flow. Copy the output from the Initialize variable step.

Copy output

Insert a Data Operation – Parse JSON step and set:

  • Content to “varAssignedto” from the previous Initialize variable step
  • Click “Generate from sample” and paste in the copied output from the Initialize variable step
Parse JSON

Insert a Control – Apply to each action and set:

  • Select an output from previous steps to “Body” from the previous Parse JSON step
Apply to each

Add a Data Operation – Filter array action and set:

  • From to “value” from the SharePoint – Get items action
  • Choose value to “Claims” from the Parse JSON step; “is equal to”; and Choose value to “Person or group the issue is assigned to Claims” from the SharePoint – Get items action
Filter array

Add a Data Operation – Create HTML table action and set:

  • From to “Body” from the previous Filter array action
  • Columns to “Custom”:
    • Header: Title – Value to expression “item()?[‘Title’]”
    • Header: DueDate– Value to expression “item()?[‘DueDate’]”
    • Header: LinkToItem– Value to expression “item()?[‘Link’]”

The output from the Create HTML table can be messy but there are options to format using CSS if so desired. In my example, I specify Title for the issue name, due date and a link back to the item (See image below). A nice upgrade to this listing would be to combine Title and the link into a single clickable HTML tag.

HTML table

Finally, add a Send an email (V2) action and set:

  • To “Email” from the Parse JSON step
  • Subject to “Issue tracker – My items”
  • Body to “Output” from Parse JSON step
Send an email

My completed Flow looks like the image below.

Completed Flow

Save and the test the Flow. If all goes well the persons assigned to the issue will receive a single email with all items assigned to them.

I like the idea of this pattern but I would invest time into formatting the HTML table for the best possible user experience prior to production use.

Thanks for reading!

NY

19 thoughts on “Send a single reminder for multiple items using Lists and Power Automate (UPDATED)

  1. Hi Norm and Thank you! I am new with power automate and this helped me alot. I have some concerns and requesting for your help/clarification after doing the test run:

    1. Multiple email sent to “assignedto”. – I don’t know if this is the problem but when I added “Email” under “To” from the Parse JSON step under Send an email the flow turns or converted into “Apply to Each”.

    Note: Same happened when “Claims” from Parse JSON step” under “Filter Array” (just an observation)

    2. Email sent also have the list of action which not belong to that person/persons/group. How can I filter that email to be sent only has/have pending list assigned to that person/persons/group to be sent.

    Like

    1. Hi Miko,

      1) Does your “Person or group the issue is assigned to” column allow multiple selections? If so, that will cause the “Apply to each”. Interestingly, the template schema has changed and now “Person or group the issue is assigned to” is called “Assigned to”.

      2) Possibly related to issue 1. Check the output from Parse JSON action to confirm distinct values.

      If you are stuck book some time and we can look into things further. https://calendly.com/norm-young/sharepoint-power-automate-help

      Thanks for reading!

      NY

      Like

  2. Over the last week I must have followed half a dozen guides for this exact process, and not one has worked. Until now. Thank you!!!
    Other than syntax errors, the only place I really got stuck was the Create HTML Table, because the array fields don’t show in the dynamic listing. However, I discovered that if I enter “item()?[‘Title’]” as a function it works. The next time I reopen the flow fresh it looks just like yours.
    Now if I could just figure out how to show the values from Choice fields…

    Liked by 1 person

    1. Hi Jon,

      Thanks for saying that, I really do appreciate it. Let me look into the Choice field stuff and I’ll get back to you.

      Thanks for reading!

      NY

      Like

      1. Thank you, I appreciate that. I actually just found the solution. I just needed to spend a few more minutes hunting to find the exact right post!
        https://powerusers.microsoft.com/t5/Building-Flows/Create-HTML-table-from-Sharepoint-List-with-multi-selection/m-p/409714#M47043

        Norm Young commented: “Hi Jon, Thanks for saying that, I really do appreciate it. Let me look into the Choice field stuff and I’ll get back to you. Thanks for reading! NY”

        Like

  3. Hi Norm,
    Many thanks for sharing this helpful post!
    Unfortunately I have already got stuck on step 3 – initialize variable.
    I am getting the following error when I save at this step:
    Correct to include a valid reference to ‘Select_-_Assignedto’ for the input parameter(s) of action ‘Initialize_variable’.
    Any ideas what I might be doing wrong?
    Kind Regards, Marc

    Like

    1. Hi Ozzy,

      Is the compare value set to the “claims” value of the person column? i.e. “Person or group the issue is assigned to Claims”

      Thanks for reading!

      NY

      Like

      1. Hi Norm, huge thanks for posting this! Also having an issue with the Filter array as I don’t have an option to select ‘Claims’ for the first value – but possibly due to the Parse JSON not working (although I copied and pasted into the ‘Generate from sample’ as instructed, I just get the following:
        {
        “type”: “array”
        })

        Thanks for any help!
        Laura

        Like

      2. Hi Laura,

        Does your List column allow multiple entries? If so that would explain the error. Let me know and I will help further.

        Thanks for reading!

        NY

        Like

    1. Hi Joanna,

      I don’t think this pattern would work as it is currently built. In your scenario are you trying to pull certain messages from a shared mailbox?

      Thanks for reading!

      NY

      Like

Leave a Reply to Norm Young Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s