In this blog post I will show how to send email reminders for Microsoft Lists items based on a date column using Power Automate. I am using a customized version of the Microsoft Lists Issue Tracker template. See Microsoft Lists: Updated Issue Tracker for more info.
Automated email reminders give users time and opportunity to intervene in business process prior to expiration or end dates. In this example we will base our Power Automate Flow on the “Due Date” column and will send reminders 30 days in advance of the date.
Create a new Flow by clicking “Automate” > “Power Automate” > “See your flows”.
Click “New” > “Scheduled-from blank”.
Provide a Flow name, I.e. “Issue Tracker – Daily”, set “Starting” to “10:00 AM”, “Repeat every” to “1”, “Day” and click “Create”.
Tip: Use the advance options to set your target time zone.
Click “New step”, search for “Variable” and select the “Initialize variable action.
Set the “Name” to “varNumDays”; “Type” to “Integer” and “Value” to “30”.
Click “New step”, search for “Variable” and select the “Initialize variable action.
Set the “Name” to “varReminderDate”; “Type” to “String” and “Value” to the following expression:
addDays(utcNow(), variables('varNumDays'), 'yyyy-MM-dd')
Click “New step”, search for “SharePoint” and select the “Get items” action. Note: Microsoft Lists is really SharePoint, so that is why we are using the Power Automate SharePoint actions.
Set the “Site Address” and “List Name” to you target site and list.
Use the “Advanced options” to set “Filter query” to the following ODATA filter query:
DueDate eq '@{variables('varReminderDate')}'
Click “New step”, search for “Office 365 Outlook” and select the “Send an email (V2)” action.
Set “To” to “Person or group the issue is assigned to Email”. Power Automate will put this into a “Apply to each” loop so it can send multiple emails based on the previous “Get items” action.
Set “Subject” and “Body” to something similar to the image below.
My completed Flow looks like the image below.
Save and test the Flow. If you have items due in 30 days the owner will receive a reminder email.
This Flow is relatively simplistic in design and function but illustrates how value can be added to existing business processes by automating tasks and giving users an opportunity to act prior to a due date.
Thanks for reading.
NY
Norm Young 
Hello, How do you add status as well as the due date? For example, if the status is complete It should not send a reminder.
LikeLiked by 1 person
Great question and something I should have included in the Flow.
Update the SharePoint Get items action Filter Query to:
DueDate eq ‘@{variables(‘varReminderDate’)}’ and Status ne ‘Completed’
I hope this helps and thanks for reading!
NY
LikeLike
How could I send an email to the person that was selected in the Assigned to field. This would mak the program way more useful.
Thanks in advance
LikeLike
It is currently set that way. In the “Send an email” action the “To” value is set to “Person or group the issue is assigned to Email”. Let me know if I can further clarify.
I hope this helps and thanks for reading!
NY
LikeLike
Hi Norm,
I managed to expand your sample further by providing a link back to the item so the person being notified can opt to change the Status flag. This brings me to a new challenge: Restricting the respondent to just the Status column – i.e., he shouldn’t be allowed to edit any other column. I’m thinking of creating a SharePoint Group where members are the only ones allowed to edit the Status column – is this possible? Could you please show me an example?
Thanks in advance. You’ve been most helpful.
LikeLike
Hi Ricardo,
Unfortunately, column permissions are not possible with Lists. You can hide columns using a custom view and new/edit form or PowerApps but at the end of the day, you are just hiding columns and not securing them.
I’m sure there are ways to work around the issue but none of which will simple.
Sorry I couldn’t help more.
NY
LikeLike
Hi Norm, I’m trying to create the same flow for Work Progress Tracker rather than Issue Tracker but I encounter 2 different obstacles as follows:
1. There’s no option to select “Person or group assigned the issue is assigned to Email”
2. The formula – “DueDate eq ‘@{variables(‘varReminderDate’)}’ and Status ne ‘Completed’” don’t seem to work after adding “and Status ne ‘Completed'”
Let me know if you need more info. Thanks so much for this guide!!
LikeLike
Hi Yaw,
For issue #1 use the “Assigned to” column.
For issue #2 replace “Status” with “Progress” and it should work.
Thanks for reading!
NY
LikeLike
Hi Norm,
Trust you are doing well.
Seeking your guidance while I am motivating young developers in the ICT & QA team in achieving a needful feature in the Weekly Call Tree List, they have created with MS Power Automate. The staff receives an outlook email that has link to the Power Automate List on Sharepoint where one has to create a new listing with certain mandatory information on each Monday which is monitored by the security for the staff welfare during the lockdown working from home.
As staff members might not act on this mail instantly on a hectic Monday, I understand there is a need to put to “enforced Flag for Recipient Reminders tick marked” just like we can do in Outlook mail under Message > Follow Up tab inputting a certain time like 4PM staff must come open this mail weekly call tree mail and take requested action. I believe this reminder is achievable in Power Automat generated weekly call tree mail. as much I read your articles on the subject. I would be delighted to receive your contextual guidance which will be of great inspiration to my colleagues not to give up.
Warm Regards,
United Nations, RSC, Entebbe Uganda
LikeLike
Hello Mohamed,
Thank you for your message. You may want to consider using the “Flag email” action in Power Automate. This will require the Message ID from the sent messages. Use the Get Messages actions to get the Message ID.
I hope this helps and thanks for reading!
NY
LikeLike
Hi Norm, I have added the and Status ne ‘Completed’ – my field is called status and I have a completed option, but it is still sending reminders to users even though they have completed their actions.
LikeLike
Hi Sumayya,
Make sure the case if the same in the column and the ODATA filter.
You can share screen shot to ny at normyoung dot ca.
NU=Y
LikeLike
Hi Norm
can you help me I have a list of some users with their mail ID and their Task on different different date in Excel sheet . I want to send them reminder on same date can you help me to show how can i set a flow in Power Automat so they can get their task reminder on same day
LikeLike
Hi Raj,
Use the same Flow pattern only replace the SharePoint Get Items with Excel List Rows. The filtering will be a different story but I am sure you will find good references on the web for that.
Hope that helps.
NY
LikeLike
Thanks!
LikeLike
How can I send an email automatically when a new issue is assigned to a specific person?
Not a reminder that their action or issue is due on xx days. But something like: “A new issue has been assigned to you” from the moment I added a new issue. Currently, they don’t receive notifications when I assigned task to them. Thank you!
LikeLike
Hi Fox,
Create a new automated Flow using the “When an item is created” trigger. Add a “Send an email” action and set “To” to “Person or group the issue is assigned to Email”.
I hope this helps and thanks for reading.
NY
LikeLike
That worked! Thanks!
LikeLiked by 1 person
Hi Norman,
Is there a way to change “From (Send as)”? As of now, by default it will come from me. Is it possible to change that to whoever logged the issue?
Thanks,
Fox
LikeLike
Hi Rahima,
Yes, you can two different ways.
1) Use the “From (Send as)” option in the “Send an email (V2) action but be aware that you will need permission to use this account.
2) Create a “service account” for your Flow (connections) that that will be the default sender. This is my preference for a number of reasons including that it can be configured not to have a password expiration policy, shows recipients that the message is system generated etc.
I hope this helps and thanks for reading!
NY
LikeLike
Hi Norm, for the ‘Link to Item’ part of the email would it be possible to link to a tab in a channel in MS Teams? I run my Issue Tracker in a channel tab under the MS Lists app in Teams.
LikeLike
Hi Sean,
I don’t think you can pull the Teams version of the Lists link dynamically using the out-of-the-box functionality. I think you could kludge it together using Graph API calls but I don’t know if it would add value to the user’s experience. The Lists experience across SharePoint, MS Lists and MS Teams are not consistent yet so there is jumping between apps.
It’s a great idea and I know my users would appreciate a consistent experience.
Thanks for reading!
NY
LikeLike
Hi
love the article as it has provided a much needed example of real flow.
Question more in context of the Issue Log Tracker – how to do stop the count of number of days outstanding (Days Old) when the status is complete? At the moment in my tracker the count keeps going even though the item is complete.
Thanks
LikeLike
Hi Amardip,
Check out this blog post on how to change the dates old column: https://normyoung.ca/2020/08/11/microsoft-lists-updated-issue-tracker/
Thanks for reading!
NY
LikeLike
Hi Norm, when I add my column to the Get Items step, I get the following error message “Column ‘DueDate’ does not exist. It may have been deleted by another user.” The column does exist, so is there a reason flow thinks it doesn’t?
LikeLike
Hi Chelsea, “DueDate” is a custom column that I added to the Issue Tracker template. You can see all of the customizations in this article: https://normyoung.ca/2020/08/11/microsoft-lists-updated-issue-tracker/
I hope that helps. Thanks for reading!
NY
LikeLike
Hi Norm,
I must be missing something but I’ve set the column as listed in the link and I’m still receiving the same error as Chelsea.
Thank you for this tutorial.
LikeLike
Hi Ron,
Make sure that the “DueDate” column spelling in the OData filter matches the internal column name for “DueDate”. If you don’t see DueDate then create it using this post: https://normyoung.ca/2020/08/11/microsoft-lists-updated-issue-tracker/.
If that doesn’t help, let’s book some time to sort this out.
https://calendly.com/norm-young/sharepoint-power-automate-help
Thanks for reading.
NY
LikeLike
Hi Norm, I tried following logic but looks like I am doing something wrong. For example we have 12 reminders to be sent tomorrow to different people (some are repeating so 4 people needs to get their reminders). When I add these emails they all get reminders, for their issues and from others. is there a possibility that person responsible gets reminder for their issues only? and not all what is due that day?
Thanks for helping
LikeLike
Hi Borisa,
Make sure that varReminderDate is set correctly and the SharePoint Get items action has the OData filter query set to: DueDate eq ‘@{variables(‘varReminderDate’)}’
Also, make sure that the “Person or group the issue is assigned to” column only has the person responsible listed.
If that doesn’t help, let’s book some time to sort this out.
https://calendly.com/norm-young/sharepoint-power-automate-help
Thanks for reading.
NY
LikeLike
Hi Norm,
big thanks for support, I managed to realize what were the issues. I had 2, first was related to Date wasn’t formatted like in your guide, and 2nd was some email issue in my responsible column. With this fixed reminders are working.
I am now moving to next task which is to send reminders to items that are already passed. For example for items that had Due Date before today. In both cases after status is ‘Completed’ it shouldn’t send any more reminders.
Borisa
LikeLike
Hi Boris,
The SharePoint Get items action ODATA filter should be:
DueDate le ‘@{utcNow()}’ and Status ne ‘Completed’
I hope this helps.
NY
LikeLike
Hi there,
Just a question, while what you have covers most of what I am looking for, do you know how I would go about doing a summery email with all the tasks that person has due that day. Some may have quite a few tasks due on one day and it would be nice to have then all in one email and not send out 4 or so emails?
Thank you 🙂
LikeLike
I should add, one email per person with their tasks due listed.
LikeLike
Hi Daniel,
Check out this article: https://normyoung.ca/2020/12/21/send-a-single-reminder-for-multiple-items-using-lists-and-power-automate/
Let me know if that works for you.
Thanks for reading!
NY
LikeLiked by 1 person
Hi Norm,
Thank you for the link to the post, that is what I been looking for. I did run into a few issues but left a comment there 🙂
Thank you again 🙂
LikeLike
Hi, I’m working with Lists (app) in one of my Teams channel, what do I need make my list shoot emails – i.e., implement the above in Teams? Thanks.
LikeLike
Hi Ricardo,
You can follow the steps outlined in the blog post and it will work in Teams. Be sure to get the underlying SharePoint site URL from the Team by going to the Files tab and then clicking the Open in SharePoint button.
If your requirements are simple you may want to consider using List Rules. Check out this blog post: https://normyoung.ca/2021/01/21/a-first-look-at-rules-in-microsoft-lists/
Thanks for reading!
NY
LikeLike
Hi again Norm,
I couldn’t find the “Automate” menu in my Teams-channel tab – the interface is not the same as the one in your blog. Were you using a separate product/service that I should subscribe to?
LikeLike
Hi Ricardo, perform the steps from SharePoint or Lists and you should be fine.
Norm
LikeLike
Hi Norm,
Firstly, my thanks for all the great info – I am just beginning to wade into SP Lists, Flows, etc, so this blog is extremely helpful!
Is this flow specific to Issue Lists, or can it work against a Work Progress Tracker List? It instantly fails with the following message: “The expression “DueDate eq 2021-03-06″ is not valid. Creating query failed.”
(Incidentally, my original requirement was to send reminder emails beginning 5 days before the due date – I will check other blog posts of yours to not send them if the status is Completed.)
Thanks in advance!
Scott
LikeLike
Hi Scott,
The Flow was built around the MS Lists Issue Tracker template but can be easily updated to match other List designs.
Does your list have a DueDate column? If not, change DueDate to your column and go from there. If you share your List schema (column names, types) and the date column you want to trigger on I can provide more guidance.
Thanks for reading!
NY
LikeLike
Hi Norm – thank you very much for your reply,
My list does have a Due Date column (I created it after building the List using the work progress tracker) – after looking more closely, it seems the issue I am having is with the date itself.
After following all of your steps (I chose 5 days versus 30 days), when I run the flow, it fails on the filter query, as it appears to be looking for a date 5 days from now – I ran this on Feb 22nd: “The expression “DueDate eq 2021-02-27 and Progress ne ‘Completed'” is not valid.” (I use Progress, not Status).
When I write a very basic version of this flow (recurrence, get items, send mail), where the only thing in the query is Progress ne ‘Completed’, the flow runs fine – of course, I lose the ability to send reminders for items beginning within 5 days of being due (and continuing until Progress is marked Completed).
Any thoughts why I am not able to get the two going together? I tried adding less than/equal to 5 days, but that didn’t help lol.
Thanks for your help! I really appreciate it (we all do!). Be well –
Scott
LikeLike
Hi Scott,
In the post I was working on the assumption that a reminder would be sent once when the due date is 30 days from the current date.
Check your single quotes in the Flow. WordPress converts ‘ to ‘.
I hope this helps.
Norm
LikeLike
Hi Norm,
I believe I have solved my own issue – for my purposes, I used DueDate le and now the Flow only runs against the list items that are due in 5 days or less (or past due, if they have not been marked Completed).
The next thing I will probably try is creating a flow that adds emails to CC each day closer to the due date (as a form of escalation) – is that sort of flow possible, or would I have to create very specific flows ie for items due today, due tomorrow, etc?
Thanks again for all your help!
Scott
LikeLike
Hi Scott,
Yes its possible just use a date range in the filter query. DueDate ge “date range 1” and DueDate le “date range 2”.
Thanks for reading!
NY
LikeLike
I got a great tip for making sure the column name is correct — always go to list settings and copy the column name directly from the URL. It covers name changes and the %20 typos
LikeLike
This is great. So easy to follow! One question I have: I have a row with multiple people in the “assigned to” column. Is there a way that each individual can check a status column saying that they’re done with the particular task so they don’t get the reminder email, but the others still do if they haven’t updated the status to completed?
If this doesn’t work, can I add an extra column that works as an exception column, so it’ll email “assigned to” except for those in the new exception column (folks from the “assigned to” column that completed the task).
LikeLike
Hi Bob,
Thanks for those kind words, I appreciate it!
You would need columns that represent the task and the owner. For example, Task1, TaskOwner1, Task2, TaskOwner2 and so on. The Flow would have to be expanded to include multiple “Get items” and “Send emails” for each Task and Owner. It’s not a hard Flow to put together, just repetitive and assumes that the process has the same (or less) number of steps.
You could build something more dynamic but that would lead to more complexity that may outweigh the effort of a “quick and dirty” Flow.
I’m happy to help further if you provide more details on the process.
Thanks for reading!
NY
LikeLike
I am getting: The expression “DueDate eq ‘2021-03-07” is not valid. Creating query failed.
I don’t know where that actual date is coming from as I don’t have that in the DueDate field.
LikeLike
Hi Brian,
A couple things. I updated the Issue Tracker to include a due date. It’s called out at the start of the article: https://normyoung.ca/2020/08/11/microsoft-lists-updated-issue-tracker/. It looks like the single quotes are wrong. ‘2021-03-07‘ vs ‘2021-03-07’. WordPress is converting quotes from ‘ to ‘.
Let me know if you need more help and thanks for reading!
NY
LikeLike
Great post. covers all the bases and easy to red
LikeLike
Thank you, Patricia and thanks for reading!
NY
LikeLike
Hi Norm, have found this really useful. However, after following the instructions I get the following error message when testing the flow – The expression “DueDate eq ‘DueDate eq’2021-03-31”” is not valid. Creating query failed.
clientRequestId: 55f0b83a-dcda-40de-bf8c-f45ad6c01dc7
serviceRequestId: 55f0b83a-dcda-40de-bf8c-f45ad6c01dc7
Any help on this would be really appreciated.
Thanks
LikeLike
Hi TM Robinson,
The expression should be DueDate eq ‘2021-03-31’ where the date should be the variable created in the previous step. The single quotes are very important. Unfortunately, WordPress has been converting ‘ into ’.
Let me know how you make out and thanks for reading!
NY
LikeLike
Hi Norm,
I tried to run the flow using calculated Expiry Date , but shows following error
The field ‘Expirydate’ of type ‘Calculated’ cannot be used in the query filter expression.
LikeLike
Hi Bhalchandra,
Try putting “Expirydate” into a variable and try the OData filter again. Let me know if that worked.
Thanks for reading.
NY
LikeLike
Hi Norm
Facing the following error, could you pls help
The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@outputs(‘Get_items’)?[‘body/value’]’ is of type ‘Null’. The result must be a valid array.
LikeLike
Hi Abhishek,
Make sure you are getting results back with the SharePoint Get Items action.
Thanks for reading!
NY
LikeLike
hello! I am getting lost on the step “Set “To” to “Person or group the issue is assigned to Email”. Power Automate will put this into a “Apply to each” loop so it can send multiple emails based on the previous “Get items” action.”
What specific actions do I take? I’ve tried typing in “Person or group the issue is assigned to Email” in the To field, in the Dynamic content field, in the Expression field – and coming up blank. Also in the Subject and Body, can you please provide what I should copy and paste and into where?
LikeLike
Hi Dave,
The “Person or group the issue is assigned to Email” comes as a result of the SharePoint Get Items action. If your List does not have the “Person or group the issue is assigned to” column you won’t find it in the results. You can substitute with any Person column of your choosing.
I hope that helps and thanks for reading!
NY
LikeLike
Hi Norm, what if instead of an email for each task we would like to group all tasks in one table and send it to the person responsible?
LikeLike
Hi Perdo,
Great idea! Check out this post: https://normyoung.ca/2020/12/21/send-a-single-reminder-for-multiple-items-using-lists-and-power-automate/.
Thanks for reading!
NY
LikeLike
Hi Norm, many thanks! This was exactly what I was looking for!!
LikeLiked by 1 person
Hello Norm,
Great flow! I am trying to test it and getting an issue that says:
“The expression “Project End Date eq ‘2021-07-24′” is not valid. Creating query failed.”
Project End Date is the date column in my SharePoint list that I want Power Automate to pull from. Do you happen to know why this error is occuring?
LikeLike
Hi Peter,
Use the internal column name. I’m guessing it’s ProjectEndDate. Also make sure your single quotes are ‘.
Thanks for reading!
NY
LikeLike
Thanks for your quick response!
Now it says:
Column ‘ProjectEndDate’ does not exist. It may have been deleted by another user.
The column in my sharepoint list is “Project End Date”. Not sure why it won’t pick it up. Any thoughts?
LikeLike
Hi Peter,
Flow can’t find the column. Go into List settings and confirm the internal name by clicking the column and look for “Field=” at the end of the URL path.
I hope this helps.
NY
LikeLike
Hi Norm,
My flow does not trigger automatically, I need to manually use the Test function each time and then it works. I have sent the value for “At these hours” 10 so in my preview, it shows “Runs at 10:00 every day”. Please help, thank you.
LikeLike
Hi Rodney,
That is odd behaviour. Are you using the Recurrence action as described in the article?
Thanks for reading!
NY
LikeLike
Hi Norm,
Thank you for your reply, it is working now. Followed your article, works well. Thank you so much!
LikeLike
Hi Norm,
Like some others, Im getting this error:
The expression “Retest_x0020_Date eq ‘addDays(utcNow(), variables(‘varNumDays’), ‘yyyy-MM-dd’)'” is not valid. Creating query failed.
The column name is copied form the end of the column URL.
Any ideas for me?
LikeLike
Hi Paul,
In your ODATA filter, the single quote should be a straight single quote and not opening/closing single quotes. WordPress formats my straight single quotes to opening/closing single quotes.
Try that and let me know how you make out.
Thanks for reading!
NY
LikeLike
Hi Norm,
I’ve made sure they are straight single quotes and I’m still getting the same error.
Does the ‘yyyy-MM-dd’ format in the expression make a difference if my column settings say “Enter date in D/M/YYYY format” ?? I’ve tried changing the expression to ‘dd-MM-yyyy’ and it doesn’t work either
LikeLike
Hi Paul,
What are you regional settings so I can test on my end?
NY
LikeLike
Hi Norm,
Time zone: (UTC + 12:00) Auckland, Wellington
Locale: English (New Zealand)
Sort Order: General
Calendar: Gregorian
LikeLike
Hi Paul,
I tested with the same settings and it works as expected. You should be able to specify any date format so long as it meets these reqs: https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings.
Are you adding/clicking the variable or typing in the variable name?
NY
LikeLike
Hi Norm,
Great article. I was wondering how you would go about applying this same logic but to send a reminder 30 days after a due date.
Thanks in advance
LikeLike
Hello Thierry,
Change the variable from 30 to -30 and it should work.
Thanks for reading!
NY
LikeLike
Hi Norm, thanks for your piece.
I have tried several times (probably 30 +) to create, adjust, modify your flow to fit with my requirements which is very similar, however I always get an error about the filter query not being valid.
I have the ff fields in my list (Activity, Patch Date & Time, Client, Completed), I just want the flow to send me a reminder email of activity(ies) that will be Due in 3 days (reference from the ‘Patch Date & Time’ column) with value of ‘No’ stated in the ‘Completed’ column.
Pls how can you help, I am loosing my mind.
LikeLike
Hi Frego,
There could be a couple of issues. First, ensure that you are using straight single quotes and not curly single quotes. Second, make sure you are using the internal column name for Patch Date & Time. To find this go into List Settings, click “Patch Date & Time” and look in the browser address bar for the column name.
Let me know if you are still stuck and we can connect and work on this together.
Thanks for reading!
NY
LikeLike
Hi Norm, Thank you so much for taking your time to respond.
I have taken note of these in the past (after doing several research) and even though I made incorporated these changes, oit still does not work.
I am still very much stuck, pls let me know how you can help further.
Many thanks.
Fego
LikeLike
Hi Fego, send a screen print of the error to ny at normyoung dot ca.
LikeLike
Hi Norm, I’m very inexperienced with MS Lists / Power Automate. I have applied your instructions to a list that contains various columns with dates in. I set it up just using one of the column names in the ODATA filer query box. Therefore, changing where you put ‘DueDate’ to ‘IWA (Expiry Date)’ I figure I will need a flow for every column that has dates. I need to have an automation whereby when any of these dates are due in 60 days an e-mail is sent to a line manager (I have a “line manager” column) I have set up a test row within my list and run the flow but it keeps failing. Any ideas?
LikeLike
Hi Julie,
It is possible to do all of this within a single Flow. Use the Flow pattern as sections for each date where you are getting the rows needed for each date and then executing the action. I have done this before and it works well. Be sure to rename your like actions so they are meaningful to you – instead of Get item 2 use something like Get item – IWA (Expiry Date). This will help you when referencing outputs in expression building.
I hope this helps and thanks for reading.
NY
LikeLike
Hi Norm.
Thanks for an great guide. It works like a charm 🙂
I was wondering if you could help me.
I am trying to create a flow where I have 2 dates. One date where a service expires. And one date where I should be reminded.
If I set the varNumDays to 0. Then I get the notification on the selected date. But I can’t get it to send the “date2” in the email.
I want an email lige. varReminderDate expires on “date2”
Thanks in advance
/Rasmus
LikeLike
Hi Ramus,
You should be able to reference date 2 from the outputs from the previous actions within the email message. If that is not clear I’ll be happy to send you a sample.
Thanks for reading!
NY
LikeLike
Hi Norm,
I followed the instructions step by step but keep getting this error when I test the flow in the Get Items section:
{“odata.error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”The expression \”DueDate eq ‘addDays(utcNow(), variables(‘varNumDays’), ‘yyyy-MM-dd’)’\” is not valid.”}}}
Any ideas?
Melissa in OK
LikeLike
Hi Melissa,
The expressions looks correct. Two things to confirm. variables(‘varNumDays’) is added as a “Dynamic value” and not typed in. Also, ensure that all of your single quotes are straight single quotes. WordPress messes that up one me.
Thanks for reading!
NY
LikeLike
Norm,
I do have it in Dynamic value… any chance it is failing because of the date format?
Thanks,
Melissa
LikeLike
Hi Melissa,
Yes I suppose it is possible. See this article for more information: https://support.microsoft.com/en-us/topic/how-to-customize-format-date-and-time-values-in-a-flow-34a91243-2961-d98c-c010-e385858b19cd#bkmk_resource
Thanks,
NY
LikeLike
Thank you for your very well detailed guide to do this. This is my first time using Power Automate, so please bear with me.
1. I get an error running this – “The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@outputs(‘Get_items’)?[‘body/value’]’ is of type ‘Null’. The result must be a valid array.”
2. The other issue I worked around a bit is for some reason the Get Items action doesn’t come up with any dynamic content in the filter query…but if I add a second Action it works (it seems like one action required for getting the sharepoint list, and then it sees the items in the second Get Items action and I can filter there.
Thanks for any help you can provide!
Brian
LikeLike
Update: I got this to run “successfully” if I add ID in the body of the email message. But I did not get any emails (just set it up to send to myself to test). There are at least 2 due dates less than 30 days in my list. Any input on what’s going wrong/suggestions would be greatly appreciated.
Thanks!
Brian
LikeLike
Hi Brian,
I packaged this up into a file that you can use. Get it here: https://github.com/pnp/powerautomate-samples/tree/main/samples/teams-adaptive-card-reminders-from-lists.
Let me know if you need more help and thanks for reading!
NY
LikeLike
Thanks. The Teams card reminders wasn’t exactly what I was looking for, but I tried a few times to import this and got this error for the flow during the import process – *” **Flow save failed with code ‘DynamicOperationRequestClientFailure’ and message ‘The dynamic operation request to API ‘sharepointonline’ operation ‘GetTable’ failed with status code ‘Unauthorized’. This may indicate invalid input parameters. Error response: { “error_description”: “Exception of type ‘Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException’ was thrown.” }’.”* I am doing this on a work account, and may not have the necessary permissions for this, I’m not sure.
For the flow that I was working on (not the import file) based on your post (for send-email-reminders-from-microsoft-lists-using-power-automate), I get an error with Get Items: [image: image.png] As I mentioned before, for some reason I can’t get dynamic content in the filter for the first Get Items (named Get Items 2). When I added a 2nd Get Items (Apply to each 2- Get Items), then I’m able to get dynamic content and filter for due dates: [image: image.png]
[image: image.png]
I saw your post about straight single quotes- I tried replacing what I copied from your post with what I believe are straight single quotes, but that didn’t work either.
Thanks for any help you can provide!! Brian
LikeLike
Hi Brian,
The first error sounds like a permissions issue. The second issue sounds like an issue with the OData filter query.
We can try to connect via Teams if we are in compatible time zones. Otherwise, email (ny at normyoung dot ca) me detailed screenshots of the Flow and the steps.
NY
LikeLike
When we export simple powerapp and one flow with it and import in client environment we get Flow save failed with code ‘MultipleErrorsOccurred’ and message ‘The dynamic operation request to API ‘sharepointonline’ operation ‘GetTable’ failed with status code ‘Unauthorized’. This may indicate invalid input parameters. Error response: { “error_description”: “Exception of type ‘Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException’
LikeLike
Sounds like a permissions issue. Also, a number of Flow triggers will only work in the Default environment.
LikeLike
HI Norm, Thanks for this guide. I am trying to get flow to send an email if an item has not be modified in 14 days, which Filter query should I be using?
LikeLike
Hi Kay,
Set varNumDays to 14 and you should be good.
Thanks for reading!
NY
LikeLike
Hi Norm, possible to change the flow that a reminder is send 30 days after the due date? Thank you 🙂 Greetings, Sebastian
LikeLike
Hi Norm, possible to change the flow that a reminder is send 30 days after the due date? Thank you 🙂 Greetings, Sebastian
LikeLike
Hi Sebastian,
Sorry for the delayed response. It is possible. Change the “Get items” filter query to the following:
DueDate eq ‘addDays(utcNow(), 30, ‘yyyy-MM-dd’)’
addDays(utcNow(), 30, ‘yyyy-MM-dd’) is an expression.
I hope this helps and thanks for reading!
NY
LikeLike
Every time i enter:
DueDate eq ‘@{variables(‘varReminderDate’)}
I get this error “The expression is invalid.”
Any ideas?
LikeLike
Hi Ryan,
Sorry for the delayed response. The reminder date variable should have open and closed single quotes. You shouldn’t need to enter an expression, just add the variable as a dynamic value.
DueDate eq ‘variables(‘varReminderDate’)’
I hope this helps and thanks for reading!
NY
LikeLike
Hi Norm
The issue was actually that the column I was using (dispute being labeled ‘due date’ was not called ‘due date’ in the column settings. This was super helpful though thanks!! Would love to pick your brain about other flows sometime 🙂
LikeLike
Hi Ryan,
It’s the little details that trip us all up. I’m happy to chat about Flows any time.
Thanks,
NY
LikeLike
Hello,
How can I resolve this error “Flow save failed with code ‘OpenApiOperationParameterValidationFailed’ and message ‘Input parameter ’emailMessage’ validation failed in workflow operation ‘Send_an_email_(V2)’: The parameter with value ‘”@items(‘Apply_to_each_4’)?[‘AssignedPerson’]”‘ in path ’emailMessage/To’ with type/format ‘Array’ is not convertible to type/format ‘String/email’.’.”
LikeLike
Hi CK,
You should be using the AssignedPerson – Email value. If you don’t have that then the column is probably not a Person column. Confirm and let me know how you make out.
Thanks for reading!
NY
LikeLike
Hello Norm,
Thanks for your help. When I try with the “Assigned Person – Email” value, I get a “The execution of template action ‘Apply_to_each_2’ failed: the result of the evaluation of ‘foreach’ expression ‘@items(‘Apply_to_each’)’ is of type ‘Object’. The result must be a valid array.” error when I try to run the flow.
LikeLike
Hi CK,
Tough to debug over post comments. I would double check all dynamic values and ensure they are coming from the expected outputs. We can try to connect via a Teams call if you can’t get past this.
Thanks,
NY
LikeLike
Hi Norm
I am struggling with the codes, My get Items flow is just saying error.
The expression “DueDate1 eq ‘2023-01-29” is not valid. Creating query failed.
clientRequestId: c7a33bbb-2ca3-4833-bc24-d56bb6abc078
serviceRequestId: c7a33bbb-2ca3-4833-bc24-d56bb6abc078
What should I do? or what am I not doing right?
LikeLike
Hi Abraham,
It doesn’t look like you closed the single quotes around varReminderDate.
I hope this helps and thanks for reading!
NY
LikeLike
Hi Norm,
thanks for the great guide – that helps a lot!!
I found out that sending a mail as mentioned above only works if multiple selection is disactivated in the “Person or group the issue is assigned to Email” column.
Is there another possibility to assign one task to several persons (possible in MS Lists with “admit multiple selection”) AND also send the mail to several persons or do I have to set the same task several times each for one person?
Best regards
Max
LikeLike
Hi Max,
Check out this solution: https://powerusers.microsoft.com/t5/Building-Flows/Send-one-email-to-list-of-multiple-email-addresses/td-p/787281
It worked for me and I will update the blog post to reflect it shortly.
Thanks for reading!
NY
LikeLike