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

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

  3. 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

    Reply

    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

      Reply

      1. I get the error due to multiple selections in the assigned to

        The execution of template action ‘Filter_array’ failed: The evaluation of ‘query’ action ‘where’ expression ‘@equals(items(‘Apply_to_each_-_varAssignedto’)[‘Claims’], item()?[‘AssignedTo’])’ failed: ‘The template language expression ‘equals(items(‘Apply_to_each_-_varAssignedto’)[‘Claims’], item()?[‘AssignedTo’])’ cannot be evaluated because property ‘Claims’ cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.’.

        Like

      2. Hi Gene,

        You are going to have to loop through the Assigned To values for this Flow pattern to work. It’s complicated but doable.

        Thanks for reading!

        NY

        Like

      3. Hi Norm,

        Thanks for explaining this error message – I’m wondering if you’re still on this thread and we can schedule some time to discuss how to loop through the Assigned To values for this Flow pattern to work…

        Like

  5. 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

    Reply

    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

      Reply

      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

  7. 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

    Reply

    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

      Reply

      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

      Reply

    1. Hi Steelwin,

      See below and thanks for reading!

      {
      “type”: “array”,
      “items”: {
      “type”: “object”,
      “properties”: {
      “@@odata.type”: {
      “type”: “string”
      },
      “Claims”: {
      “type”: “string”
      },
      “DisplayName”: {
      “type”: “string”
      },
      “Email”: {
      “type”: “string”
      },
      “Picture”: {
      “type”: “string”
      },
      “Department”: {},
      “JobTitle”: {}
      },
      “required”: [
      “@@odata.type”,
      “Claims”,
      “DisplayName”,
      “Email”,
      “Picture”,
      “Department”,
      “JobTitle”
      ]
      }
      }

      NY

      Like

      Reply

  11. Hello Norm, very good post, exactly what I was looking for. But I can’t get the Claim value from Parse JSON. I have “Item”, “Body” and many “odata.type..Microsoft.Azure” options. In the List Column I choose “No” by “Allow multiple selections”.

    Like

    Reply

    1. Hi Eqor,

      Glad to hear that the post was of value. It was a tricky one to put together. When you generated the JSON where fully populated rows in the List? I can share my schema but if we are using the same List structure it may not work as expected.

      Let me know how you make out.

      Thanks for reading!

      NY

      Like

      Reply

      1. Hi Norm,

        thanks for answering to my question. I tried with your scheme as mentioned above but without success. Yes the rows and columns are fully filled. Maybe it’s because of Azure and SSO?

        My scheme looks like this:

        {
        “type”: “array”,
        “items”: {
        “type”: “object”,
        “properties”: {
        “{\”@odata.type\”:\”#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser\”,\”Claims\”:\”i:0#.f|membership|user1@myorganisation.com\”,\”DisplayName\”:\”Max, Mustermann\”,\”Email\”:\”user1@myorganisation.com\”,\”Picture\”:\”https://myorganisation.sharepoint.com/sites/General1546/_layouts/15/UserPhoto.aspx?Size=L&AccountName=user1@myorganisation.com\”,\”Department\”:\”CRM\”,\”JobTitle\”:null}”: {
        “type”: “string”
        },
        “required”: []
        }
        }

        Like

      2. Hi Norm,

        I have found my issue 😀 So if you create a new SELECT, you have to input for the KEY COMPONENT a string like RiskBelongsEmployee and on the right side for the ENTER VALUE the dynamic content from GET ITEMS. After you will be able to copy and generate the right JSON and you will have the possibility to input CLAIM into FILTER ARRAY.

        Liked by 1 person

  12. Hi Norm, this has proved so useful I cannot even tell you.

    I need to extend it slightly, in that my SP List has 3 owners (not multi select, 3 distinct columns; primary owner, secondary owner, tertiary owner).
    I have tried having 3 separate flows run, with the Select action mapping which ever Owner column that flow was intended for. It works fine for the Primary owner as this is a required field, as every item in my list must have an owner, but not every item will have a secondary or tertiary owner. So in the schema for the other flows this is throwing up null values and wont parse. I have tried replacing “type”: “string” in the schema with [“string”, “null”], but that doesn’t appear to work. I just get an error on the Parse JSON – ValidationFailed. These schema validation has failed. Invalid type. Expected Object but got Null.

    If you have any ideas or pointers it would be much appreciated.
    Or if you are aware of any way I can parse the secondary and tertiary owner’s email (if there is one) to include these columns in the HTML table and also send the email output to them, that would be even more amazing.

    Thanks

    Simon

    Liked by 1 person

    Reply

    1. Hi Simon,

      Thanks for the great feedback, I really appreciate it. I think you need to evaluate the secondary and tertiary fields for NULL’s prior to any execution of actions. If NULL, do nothing. If NOT NULL, do actions.

      Hope that makes sense. Let me know how you make out.

      Thanks for reading!

      NY

      Like

      Reply

  13. Hi Norm,

    I am trying to follow the same steps but I am getting my data from SQL with Get Rows (V2). All good until Filter Array as I cannot find “Claims”.

    This is how the output from Initialize Variable step looks on my side
    {
    “variables”: [
    {
    “name”: “varEmail”,
    “type”: “Array”,
    “value”: [
    {
    “Employee Email”:

    followed by Employee Emails.

    Could you please advise?
    Are these steps compatible with SQL too?

    Thank you,
    Daniela

    Like

    Reply

    1. Hi Daniela,

      I would consider adding a “Get user profile” action to resolve the email stored in SQL and then reference the output in the “Filter array” action.

      I hope this helps and thanks for reading!

      NY

      Like

      Reply

      1. Hi Norm,
        I just fixed it. Actually “Claims” in my Outputs is “Employee Email”. So I used in Filter Array “Employee Email” from Parse JSON equals to “Employee Email” from Get Rows step and it works great.

        Your flow was a life saver!

        Thank you,
        Daniela

        Like

  14. One more question. Is this possible with ‘When an item is created in SQL’? I have employee emails with multiple rows that are being created in SQL. Can I send one email with all the rows assigned to that employee email (without duplicating emails) with ‘When an item is created in SQL’ trigger?

    Thank you,
    Daniela

    Like

    Reply

    1. Hi Daniela,

      It sounds feasible and you could probably use a similar Flow pattern only replacing SharePoint actions with like SQL actions.

      Would like to know how you make out. Please share back your results.

      Thanks.

      NY

      Like

      Reply

  15. Hello! Thanks for all the time you put into this walk through. I am attempting to perform a similar operation on a sharepoint list of ‘action items’ I’m maintaining. I’m assuming the process is similar, however I run into an issue I believe at the “Data Operation – Select action” step. I don’t have a ‘person or group the issue is assigned to” parameter. Instead I just use the ‘Assigned to” parameter, which I assume will suffice, however the output from “initialize variable” step is quite different, as seen below. Any thoughts as to where I’m going wrong? Thanks so much!
    [ { “{\”@odata.type\”:\”#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser\”,\”Claims\”:\”i:0#.f|membership|mweivoda@hdrinc.com\”,\”DisplayName\”:\”Weivoda, Michael\”,\”Email\”:\”Michael.Weivoda@hdrinc.com\”,\”Picture\”:\”https://hdrinc.sharepoint.com/teams/NorthCentralMarketing-RBG/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Michael.Weivoda@hdrinc.com\”,\”Department\”:null,\”JobTitle\”:\”Area Marketing Lead\”}”: “” }, { “{\”@odata.type\”:\”#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser\”,\”Claims\”:\”i:0#.f|membership|mweivoda_gmail.com#ext#@hdrinc.onmicrosoft.com\”,\”DisplayName\”:\”mweivoda\”,\”Email\”:\”mweivoda@gmail.com\”,\”Picture\”:\”https://hdrinc.sharepoint.com/teams/NorthCentralMarketing-RBG/_layouts/15/UserPhoto.aspx?Size=L&AccountName=mweivoda@gmail.com\”,\”Department\”:null,\”JobTitle\”:null}”: “” } ]

    Like

    Reply

  17. Happy Holidays Norm! I hope you’re well – thanks for all the great knowledge this year!

    Question: I have a similar filter query in place, however in my scenario, I want a reminder email to go out if the status is not equal to Completed, Validation, or Duplicate. This is what I have in place, however it’s sending even when the status is completed – does this look right (the var reminder date is 5)?

    DateDue le’@{variables(‘varReminderDate’)}’ and Status ne ‘Completed’ and Status ne ‘Validation’ and Status ne ‘Duplicate’

    One note: I see that other admins of the list have added additional statuses that I don’t account for i.e. ‘won’t fix’ – do I need to add all of those in as and/or?

    Thanks in advance – have a great weekend!

    Like

    Reply

    1. Hi Scott,

      Happy holidays to you as well!

      Try this in your filter query: DueDate le ‘@{variables(‘varReminderDate’)}’ and (Status ne ‘Completed’ and Status ne ‘Duplicate’ and Status ne ‘Validation’)

      You should the additional statuses to the filter query.

      Thanks for reading!

      NY

      Like

      Reply

  19. First, I have to say thanks the amazing piece of work. I got until working until the very last step (apply to each control) where I encountered an error. The error details is as follows:

    The execution of template action ‘Filter_array’ failed: The evaluation of ‘query’ action ‘where’ expression ‘@equals(items(‘Apply_to_each’)[‘Claims’],item()?[‘Assignedto’] failed: ‘The template language expression ‘ equals(items(‘Apply_to_each’)[‘Claims’],item()?[‘Assignedto’]’ cannot be evaluated because property ‘Claims’ cannot be selected. Array elements can only be selected using an integer index.

    Will appreciate your help.

    Many thanks

    Like

    Reply

    1. Hello Oladman,

      Make sure you are using the Assignedto Claims from the dynamic output. Let me know if you are still stuck. Its a tricky Flow.

      Thanks for reading!

      NY

      Like

      Reply

  20. Hi Norm,

    It is great solution and I need to get it working badly, but have an issue with ‘Create HTML Table’ step.
    I am doing exactly what is described in this article, but I keep receiving Empty value in a table. I use ‘item ‘ expression. When I examine the issue I see that I have only [] in Inputs/From. Do you have any idea, what I have done wrong?

    Like

    Reply

    1. Hi Piotr,

      Are you getting data from the Get items action? See the Outputs and then Click to download.

      Let me know how you make out and thanks for reading.

      NY

      Like

      Reply

  21. Hi Norm,
    Thanks for your post. I’ve been working on this type of flow for a few weeks and it actually works now!

    I am having problems with getting the Link to populate in the HTML Table (and subsequently in the email).
    Any help you could provide would be greatly appreciated.

    Thanks,
    April

    Like

    Reply

    1. Hi April,

      Glad to hear that the post was of value.

      Check the expression for the link:
      item()?[‘Link’]

      WordPress does formats my straight single quotes into open single quotes.

      Let me know how you make out and thanks for reading.

      NY

      Like

      Reply

      1. Thanks for the quick reply!

        I’ve double checked the syntax but still no luck.
        I can see {Link} in the Create HTML table > Inputs > From, but when I view the Outputs the field is blank.

        Not sure what else to check 🙂

        Thanks
        April

        Like

      2. Hi April,

        These ones are hard to debug/support from blog comments. I would step through the Flow and ensure that data was coming back as expected. If you are still stuck lets try and find sometime to connect and see what is going on.

        Thanks for reading!
        NY

        Like

  23. I’m having trouble right up to the filter array it’s failing for me. Could be that I am using a multiple people picker?

    Like

    Reply

  24. Thank you for your directions. I do have an issue as it sending multiple emails. If the user has two open action items on the SharePoint list, it still sending two separate emails. I’m not sure why it’s doing this. I don’t have a Person/Group column in SharePoint so I’m mapped it o the the Submitted by column. Any ideas on why it’s doing this? Thanks!

    Like

    Reply

    1. Hi Tony,

      Confirm that your Filter array action is working as expected. That is to say there should distinct users/emails/claims etc.

      Thanks for reading!

      NY

      Like

      Reply

  25. Thanks for posting this!!! I am SO close, but unfortunately get stuck with this error on the Filter Array step:

    InvalidTemplate. The execution of template action ‘Filter_array’ failed: The evaluation of ‘query’ action ‘where’ expression ‘@equals(items(‘Apply_to_each’)[‘Claims’], item()?[‘SCOwner’])’ failed: ‘The template language expression ‘equals(items(‘Apply_to_each’)[‘Claims’], item()?[‘SCOwner’])’ cannot be evaluated because property ‘Claims’ cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.’.

    I’m really new to Power Automate, so I’m not sure what to make of the penultimate sentence, “Array elements can only be selected using an integer index.” Any advice on what to do here?

    Like

    Reply

    1. Hi Cullen,

      Others have reported that they don’t have a claims value and had to use the email value. Try that and let me know how you make out.

      Thanks for reading!

      NY

      Like

      Reply

  26. This flow looks like the best solution I have come across so far for sending a single reminder for multiple items. I have encountered an error at the step where the call is to parse the JSON for varAssignedto (I called my variable varCurrentOwner instead). Per instructions, I ran the initialize variable step for varCurrentOwner, copied the content of the Value field, inserted a Parse JSON action and pasted the code into the module using the Generate from Schema button. I’m getting a message that “The schema validation failed” This is what is in the schema field:

    {
    “type”: “array”,
    “items”: {
    “type”: “object”,
    “properties”: {
    “Current Owner”: {
    “type”: “object”,
    “properties”: {
    “Claims”: {
    “type”: “string”
    },
    “DisplayName”: {
    “type”: “string”
    },
    “Email”: {
    “type”: “string”
    },
    “Picture”: {
    “type”: “string”
    },
    “Department”: {
    “type”: “string”
    },
    “JobTitle”: {
    “type”: “string”
    },
    “@odata.type”: {
    “type”: “string”
    }
    }
    }
    },
    “required”: [
    “Current Owner”
    ]
    }
    }

    Any help you are able to provide will be greatly appreciated.

    Thanks.

    Kurt

    Like

    Reply

    1. Hi Kurt,

      Sorry for the delayed response. I would want to confirm the output from the varAssignedto initialization and make sure there was data. Also, confirm that the columns in your list are of Person type.

      I hope this helps and thanks for reading!

      NY

      Like

      Reply

  27. filter should be AND , no ? should it not be “Status ne ‘Completed’ AND Status…” ? ‘Or’ will get the item when Completed, because at that moment status will not be Duplicate and an OR will give true.

    Like

    Reply

    1. Hi LalaLand,

      No I don’t think so. A column can’t be both values so OR is correct for this pattern.

      Thanks for reading!

      NY

      Like

      Reply

  28. Hi Norm, this is a great solution. As some other folks have mentioned, I’m getting nothing in my output though.

    I made sure my Select action is not set to Table and it’s grabbing @item()?[‘Department_x0020_Administrator’]

    My union action is @{union(body(‘Select_Department_Administrator’), body(‘Select_Department_Administrator’))}

    My parse json schema is:
    {
    “type”: “array”,
    “items”: {
    “type”: “object”,
    “properties”: {
    “@@odata.type”: {
    “type”: “string”
    },
    “Claims”: {
    “type”: “string”
    },
    “DisplayName”: {
    “type”: “string”
    },
    “Email”: {
    “type”: “string”
    },
    “Picture”: {
    “type”: “string”
    },
    “Department”: {
    “type”: “string”
    },
    “JobTitle”: {
    “type”: “string”
    }
    },
    “required”: [
    “@@odata.type”,
    “Claims”,
    “DisplayName”,
    “Email”,
    “Picture”,
    “Department”,
    “JobTitle”
    ]
    }
    }

    My filter array action selects the BODY from the json action.
    Filter array uses the value from Get Items, and claims is equal to @item()?[‘Department_x0020_Administrator’]

    When I create my html table they are Expressions, but after I run the flow, they are data expressions like this: @item()?[‘Computer_x0020_Type’]

    The email does go to the correct people, but the output is just the headers from the HTML table.

    When I look at the flow run, the create html table in the FROM section is empty.

    Like

    Reply

    1. Hi Steve,

      Sorry for the delayed response. Do you get data after the steps leading up to the filter array?

      Thanks for reading!

      NY

      Like

      Reply

  29. Hi Norm, is there a way to apply a colour to the Html Table, when one of the colums is Priority or Status, and if Critical is listed, then its red, or if Priority is High its blue and Priority Low is green? Thank you,

    -DK

    Like

    Reply

  30. Hi Norm,

    I’ve followed through to finish something like 15 tutorials on this same flow subject and none have worked as well as yours, thank you!

    I have one additional need out of this flow that I would love to get your expert instruction on, if you are still interested in expanding on the topic.

    At the beginning of the flow, I would like to initially filter the retrieved rows from Get Items based on when the SP list in your article and one other SP list have the same values in a given column. Can this flow be altered to first filter for rows in your articles list that have the same value in a designated column on both lists, then make that result the items to be leveraged in the remainder of the flow?

    Like

    Reply

    1. Hi Sean,

      Sorry it took so long to respond. I think you will have to add another Get items action and filter based on your specific logic. From there you should be able to use an Apply to each to filter out the remaining items from the first list.

      Good luck and let me know how you make out.

      Thanks for reading!

      NY

      Like

      Reply

Leave a comment