Awesome tutorial. You not only just gave the steps to copy, but also gave background items to look for that could trip it up. I was struggling with one, it had to do with having the correct name for the column in the flow. Thank you.
Hi Richard, thank you SO much for video I have been scouring the internet on how to do this for the past 2 weeks at work and this video allowed me to accomplish what I needed in 15 mins. Cheers!!
You made me look like a genius today! Thank you!!! I did encounter that apostrophe issue and never would have figured that out. Now what I need to do in addition is provide a link of the views of the almost due and overdue tasks to the end user and manager
Awesome Steve! If you want to show a range of dates - just change your odata filter from "duedate eq 'thedate'" to something like "duedate lt 'thedate+10' and duedate gt 'thedate-10'" or however you want to set up your date range.. Hope that makes sense!
Thank you Richard that is really helpful. I had been struggling to do this until I found your video. Your explanation is very clear for each step - much appreciated!!
Hi Brian, can you let me know your flow has run or not after perform via Richard's video. In "send an email" in the 'To' field, I have performed as your instruction but I can't expand it when I run Test, any idea?
This so great Richard! It’s simple and easy to understand! 😊 just wanted to know how to format the date to let’s say ‘dd-mm-yyyy’ bc what it being returned it the default system date which is yyyy-mm-dd. Thank you!!😊
Hi Francis - you can use an expression for this if you wanted to or the convert time action. Convert time is still very usa date format but if you were ok with a format like Wed 16th August that might work. Else using the expression editor would help like this formatDateTime(items('Apply_to_Each')?['YourDateField'], 'dd-MM-yyyy') just add in whatever the item is that you want to convert.. Hope this helps! Rich
sure is - just change the filter to get anything that is overdue - so duedate lt today.. lt less than gt greater than - wanna go with due date less than today I think. Good luck!
Hi - please can you let me know if you are seeing any errors in the workflow runs? or if the workflow just isnt finding the correct items? or something else? If you let me know as much as you can - I'll be able to give you some help. Thanks!
Thank you Richard that is really helpful. In "send an email" in the 'To' field, I have performed as your instruction but I can't expand when I run Test, any idea?
Hi - it sure is. Click on the advanced settings of get items and then you can add odata filter text into the filter field eg Title eq 'Test' or Status ne 'Completed' odata will support many filter conditions like a date range and status - but it wont support calculated column values in sharepoint - so it will mostly work from text or date or choice fields. Hope this helps!
Thanks richard! Can i check, what should i add in my filter if i only want to send emails to those that are not completed? Is this expression correct? Duedate eq '2021-10-21' and Status ne 'Completed' I tried but not working
Hello Richard! Very nice and informative video as always! I did encounter a problem near the end though. I get the following message 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)?['Samordnare!]" in path emailMessage/To' with type/format Array' is not convertible to type/format 'String/email! I get this message when i pick my column called ”Samordnare” which is a People and Groups column where you fill the name from an organization (containing email too) on the ”Send an email (V2)” function. You have any idea what the problem is? Im very appreciative of your help. Thank you very much!
Great tutorial and thank you! I have run into a following problem, I keep receiving emails after the due date has passed. I only need a reminder on the dates. What shall I add/modify to have only one reminder on the due date?
Hi there - if you wanted to post your odata filter from your get items action in the workflow I could look at that. But essentially you will want to ensure you are using an 'eq' or equals operator - so get me all items where myduedate eq '2023-01-21' with the date being calculated everytime the flow runs so it works out whatever the dynamic date should be. This way you will only get the items that are relevant and not past dates - hope this helps - happy to look at your filter - just post here.
Richard - in Apply to each // Send an email (v2), can I copy my MS Teams channel email into the *To section? Doesn't seem to work. Thank u very much / great video.
Your videos are really helpful. I'm actually looking for creating tasks in planner using excel and then looking for triggering email wen a bucket is completed or nearing completion to motivate team for completing targets. Please suggest ways for it..I'm new to flow..thanks in advance
Thank you for walking through the entire flow, Richard. I need to add a condition that a reminder should run based on the value in a certain column. How do you think I should do that?
Hi Niranjan - there are actually three 3 methods I could use for this - either set a trigger condition (in settings of the trigger - syntax like - @equals(triggerBody()?['PA_Completed_WF_Run']?['Value'],'No') ) OR have a Power Automate condition control to check the value of the column then run the reminder in an apply to each control OR change the odata filter in the get items to extend eg Title eq 'Test' and Date eq 'Overdue' - so lots of ways to do it.. hope this helps!
Thanks for the great tutorial. However,just a quick question, is this PAD able to run even when the laptop is in sleep mode or even shut down?(Assume Scheduled Flow is chosen)
Hi - by PAD you refer to Power Automate Desktop? The clip I'm showing is for a Cloud Flow - so this is always online and will run on the schedule as its cloud based. For Power Automate Desktop - if you have created a scheduled cloud flow that is calling online services such as o365 apps or dataverse or using a data gateway to connect to a file server etc then these will also work - if you have created an attended or unattended desktop flow the device these are running on do need to be on/available. Hope this helps!
Kia ora, thank you,!! This is one of the easiest "how to" videos I have seen on this. Are you able to help? I am needing to reference this from a library, not a list and am stuck at the "Get items" as it asks for a list name. I have created a library of policy documents and want to add an email reminder to the owners of the documents 60days and 30days before the review date. Any guidance on this would be fantastic. Thanks in advance
Hi Kiela-lee - thanks for the feedback - a document library uses a different action to a list - so rather than Get Items - we use Get files (properties only) then you can apply the same odata filter on your library columns as well - hope this helps!
Thank you, this has been really helpful! Is it possible that the reminder would be sent in one mail and not individually? For example, I have 3 tasks due tomorrow. Can this be sent in one mail only? Thank you!!
I would really like to know this as well. I have been trying to work with arrays but I don't get the filter array option in my account of power automate. So, I can't finish the flow. Maybe this will be useful for you @Bianca Francisco: powerusers.microsoft.com/t5/Building-Flows/Reminder-Flow-for-once-a-week-for-Overdue-List-Items/td-p/330694
@@biancafrancisco941 were you able to accomplish what you were going for? i think i might be trying to do the same thing that you are... any guidance would be great!
Hello Richard, thanks for the wonderful video, all the steps worked for me except when it reaches send email I am unable to see the records and the email is not getting triggered. Can you please help here to get the email triggered
Hi Raghu - this sounds like your filter isnt picking up any records? I'd suggest adding a compose step after you get items in an apply to each step to output the id or title of the email to check whats happening?
Hi Azizur - you'll need to use some variables and expressions to achieve that functionality - I dont have a clip for it yet - but check out this guide which should help you - powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Calculating-working-days-in-Power-Automate/ba-p/557278
Hi Erin - yes it is - just need to use Get Files (Properties Only) vs Get Items for your SharePoint connection - other than that - everything works the same! Good Luck!
This video is very helpful. How do I add a reminder for items not completed before due date? If my list has a progress column with dropdown selections and I only want the reminder to go out to those who have not completed the task before the due date.
Hi Nasmee'a - for this requirement i suggest adjusting the odata filter on Get Items to ensure you only pull back the people you want to remind. - Hope that helps!
Hi Richard, thanks for sharing this video. I am using the exact steps to send reminders a day before the due date. My flow ran fine for two days and stopped/did not notify me on the third day. Upon checking the flow, it seemed that the 'Get Files' action is not returning any value. However, the DueDate column has items in there. Any help in understanding the issue will be much appreciated.
Hi there - if the workflow isnt returning any values - I would take a look at workflow run and expand the get items action and check the date that is being looked for and any other values in the odata query - then check your data source and do a comparison. If you think something should be getting returned we can look at your odata query and validate it then adjust as needed.. Let me know how you are getting on?
@@Richie1902 Thank you so much for the guidance. I realized the flow was not returning values because I set it up on the Document library. I created a new library and applied the flow there, and it ran fine. Thanks for sharing valuable content that broadens our knowledge about workflows and power automation.
Hi Richard, I've tried your tutorial and it works splendidly. Since my list is built upon Excel in Onedrive, I made some slight tweaks and it still worked. Just wondering, is there a way to lump several rows of SharePoint in an email reminder instead of sending them individually?
Hi Syazwan - yes - you can do this by building an Array / Table in your workflow to capture all the relevant messages for each person you want to send to - you'll need to initialise the array variable, then use a get items/rows action, them a for all loop and append to array variable action inside the for all loop - hope that is enough to get you started!! I have a clip on using an array for something similar - let me know if that helps!
@@Richie1902 Hi Richard, thanks for getting back to me! Do you mind linking the video for Creating Arrays that you mentioned? I tried flicking through your channel but I couldn't find the right one (not so fluent in Power Automate yet, it could have gone past me while searching)
Hi Richard! Love this video - but I am running into issues in the filter query step. it says that my column does not exist in the error, but it does exist. Any ideas how to solve this? I am desperate!
Hi Isabela - a filter wants the system/hidden name of a column often this can be different if you've renamed a column - best thing to do is go to sharepoint and open the list or library settings and click on the column you want to check - on clicking a new page will open - check the end of the url for this page as this will show your 'real' column name you need to use in an odata filter - hope this makes sense!
Richard - this is a great video. It is almost exactly what I needed. In my situation, my due date column is calculated so I get an error that the "type 'Calculated' cannot be used in the query filter expression." Is there a solution for this?
Hi Sarah, currently calculated columns are not supported as a column to look up to in Power Automate. Workaround is to either calculate the date within power automate as the workflow runs OR on an item created or modified you use power automate to recalculate the date then update the item. If updating an item - you have to be careful to not kick off an infinite loop so the workflow keeps running.. hope that helps?
How do you make these due date notifications for only items that are open/in progress? Is there another action to tell the routine not to run if the status is closed?
Hi Taylor - yep - when you use the get Items action in Power Automate - you can toggle the odata filter to pull back whatever data you need - so if you have specific values you need to pull back thats where you would do it. So due date eq today and status eq open type thing - then you only get the data you need. If you need to pull different status types back - you but only run the notification on a particular status you could either use the data operation of Filter to reduce the items down to just what you want OR you could use Apply to Each and have a condition within the apply to each step - so if a value eq the value you want then do something. Filter is a bit easier/efficient Let me know if this helps!
Richard thank you for this great tutorial! My work colleagues are impressed ! Can I make the flow dependent on the "Status" column in addition to the overdue date ? I am trying a few things right now, however I keep getting error messages. The mail should be sent only to those where the status is not "completed". Do you have some tips ?
Hi - thanks for the feedback - yes you can use multiple filter conditions in a get items for a not completed you would want StatusColumn ne "Completed" and DueDateColumn eq "2023-02-07" for example.
Hi Richard, this is great. I tried get around the weekends issue i.e. only send on business days by having 3 calculated date fields in Sharepoint off my First Follow Up date field. i.e. First Follow up = Day 1, then calculate 3 business days, 5 and 7 business days from these. Then on those dates send a reminder. However looks like ODATA query doesn't like calculated columns I get an error. Is there a better approach do you think?
Hi mkavo - you are correct - there is no support for calculated columns in odata filters at present in Power Automate - SharePoint connector. You could move your logic to create the dates into Power Automate potentially? - there is an action called get future date - so on new item creation call power automate and use get future date to 5 and 7 days and then pop these values into an update item sharepoint action! Hope this makes sense!
@@Richie1902 Hi Richard, i'll have to calculate business days somewhere in there, I'm sure there is a formula but already have that part working in SharePoint. I changed my ODATA filter fields to date fields they enter data into and it works fine i.e. Date 1 ne null and Date 2 eq null, this still gets the items in scope down drastically, then i can use my calculated due date fields in conditions and look for where they equal today and if so send the appropriate email and do appropriate updates.. Your video helped immensely in getting to this stage to try things so thanks!
Thank you Richard, this is brilliant. I've built the flow to send a reminder email 14 and 7 days prior to a licence expiry and it picks the expiry date from a SharePoint list column. But, it is still looking at whether the date is 14 days from the date I created the flow (yesterday) and not the current date. I set the timezone to my own (Brisbane). What am I doing wrong?
Hi Erica - if you want to send me some screen shots of your workflow - expanded to see filters or date actions I should be able to help you correct the issue. Date wise - you'll want to get current date then convert the date and make sure you use the converted date action in the formula for any filters - hope that helps - email is richard@brighterdays.co.nz
Dear Richard, at 10.07 you mention you can specify a column in the get items. So you can send an email to those people. Could you let me know how to do that? running into issues with this last step.
Hi Audrey, within each Get Items action you can expand the action to view Advanced Settings and use the Filter Query option to use an Odata filter to limit the items returned. Odata is a specific syntax and calls the system name of the column you want to filter on - normally the syntax would be similar to Title eq 'Example' or NumberColumn gt 5. However if you are using SharePoint and have renamed columns in the list you are wanting to filter - you must still need the original name of the column not its renamed value, plus if you have spaces in your column names you'll need to use the correct sharepoint name value - so rather than My Column Name eq 'Test' it would could possibly be My_x0020_Column_x0020_Name eq 'Test - hope this helps!
Thanks Richard, this is very helpful. Unfortunately I keep running into issues in the last step (sending the email) and don't understand why. Issue: low 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')?['k21v']"' in path 'emailMessage/To' with type/format 'Array' is not convertible to type/format 'String/email'.'.
now it seems to stick with error: 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.
Hi Audrey is the column k21v a person type field? If so you'll want to use syntax like this k21v/EMail to just get the email and not the whole person column array - note the capitals on EMail :) - Hope that helps - ping me on richard@brighterdays.co.nz with screenshots and column information if you are still stuck..
Hi, I get an error when running this flow, the date format is does not match yet I am using the same Australian/UK format as you are and have copied your instructions to the comma..? Can you assist with why this is not working for me..? I can't see where I need to format the date code but you didnt need to do this..?
Hi Rebecca - can you look at the flow run with the error and see the format that the date has come through as? Also - can you let me know if the sharepoint column you are using is a date only or a date + time type? Happy for you to send screen shots of what you are seeing / your workflow to rich@brighterdays.co.nz
@@Richie1902 thanks Richard, I ended up figuring it out by watching a different tutorial. One detail which I don’t believe you mention is if the column name has spaces on your SP site, you have to remove these as SP does not read spaces. That was my issue! So simple! But thank you as your video helped enormously 👍🏻😊
Hi - as long as you are recording when a task is due you can use the methods shown in this clip to send notifications at any stage before the due date - this clip shows how to do it for 7 days before or 14 days before - I hope this helps
Thanks you.... but what can we do when in SharePoint list expiration date column( Calculated column) have 2042 and set due date reminder on calculated column.
Hi Kapil - calc columns are not yet supported in power automate - I'd suggest having another due date column and use power automate -'get future date' action to define the due dates on item creation etc - if you do it on edit of items - make sure you dont create an infinite loop on your workflow!
It's really good and helpful for me!! Thanks! What if I would like to add one more condition like specific value in same sharepoint list with the 'Due date' column? is it working if I modify on the query value like - DueDate eq 'substring' && Status.Value eq 'Submitted'?
Hi - yes you can extend the odata filter against other columns - if your sharepoint column is date only it will be in a format like yyyy-mm-dd or if datetime it will have hours and seconds - but you could do a left(duedate,0,10) expression to just get the yyyy-mm-dd part - hope you get it working!
Hi, I tried to add an additional query item, but got an error. I want to only filter on 'Open' items to send the email reminder e.g. Next_x0020_Review eq 'substring' && Current_x0020_Status.Value eq 'Open' Any suggestions why it wont work would be greatly appreciated?
Hi - if you need to create a list in sharepoint - this tutorial should help - ua-cam.com/video/k0vLHYkiBIY/v-deo.html or if you are trying to figure out how to connect your app to a list - this is done via Connectors in the power platform - this guide will help understand how to set them up/manage them - docs.microsoft.com/en-us/powerapps/maker/canvas-apps/add-manage-connections
Thank you Richard. I used your instruction to apply to an entire document library & it works - sending due date reminders to document owners. However, if I put in more than one document owner in my library, the flow fails. The error reads 'Error details: 'The API operation 'GetItems' requires the property 'body/value/0/Documentowner' to be of type 'Object' but is of type 'Array'.' Do you know how I can just this flow for multiple document owners? instead of just one
Hi Sarah, I recently helped someone else with this - so planning on doing a clip to show this - essentially (my easy option - there are other ways to do this..) - you will want to create a string variable then use an apply to each on the multi select person field and for each email found use append to string variable to add the email and a semi-colon - then use this variable in the email's to field - I'm hoping to do a clip next week - but maybe this info will help until then!
Great video. I am trying to do the same but the only difference is I want to send reminder emails based on due dates but when Status column value is in progress. I have task list in SP and do not want to send reminder emails if task status is Completed. Can you please help? My flow is scheduled to run at 8am everyday but it sends emails for tasks no matter what the status is.
Hi! in your Get Items action, you should be good to add in the odata filter like so 'and Status ne 'Completed' this will then ensure all the completed items are not pulled into the array of items to be emailed. let me know how you get on - this assumes that the Status column is a text or choice column - but it should be straight forward to implement. Good luck!
Thx Richard for your amazing sharing. However, when I tried to use the field name in the expression of Field Query, the test outcome shows that 'Column '_x9884__x8ba1__x5b8c__x6210__x65' does not exist. It may have been deleted by another user.' I am really confused now.
hi - so 2 things to try - go to the sharepoint column via list or library settings and then click on that columns name - in the url you will see the system name of the column - copy that name and put into your filter - sometimes the names do get cut off a bit.. second check - do a get item - put in an id with no filter - with a ID value you know has a value on this column - run the workflow and then view the results of the run expand the show outputs area and compare the column name to see if different.. Hope that helps you find the column!
Great video! I run into an issue when I try to have multiple people listed under the "assignedto" column. If I only allow for one person to be listed, I have not error but when I allow for multiple people to be listed, I get the error that the data is an array, and not an object. Is it possible for this workflow to work when multiple names are listed under the "assignedto" column? Thank you!
Hi Valentina - in this scenario an option you can use is to create a String Variable - then use an Append to String Variable action and add the dynamic email plus a ';' semi colon within an apply to each/for each based on the email column you are working with - then use the variable in the To field of your email action. There are other methods to do this as well - using the Array Variable, then parsing the Array and again using an apply to each on the email part of the array. Hope this helps!
Hi Richard, great video and very helpful. I am running into a problem with my emailing though. my contacts are in "sitecontact" column in sharepoint as a name which links to directory, so emails aren't listed. when I create send an email, it lets me select "sitecontact" in Apply to each, then "sitecontact email" in sub level Apply to each 2. The flows runs as successful but doesn't return values. it's greyed out assuming it stops at Apply to each then never moved to next level. is there a workaround?
Hi A T - your apply to each will be greyed out as that action cannot find any matches - I would check the get items filter action to check if you are getting any results - if not - check the system name of your sharepoint column - under list settings, click on the list and check the url of the page that loads to show you the column name that the odata filter is looking for.. hope this helps!
Hi Richard Can u help? I m stuck Every month over 100 of companies need to submit a document to me via ms form and I need to chase them to they don’t submit on time , is there anyway can automate the whole process? Now I m doing manual checking to see who has not submit and it takes very long . Appreciate if u can give me some ideas
Hi - using an MS Form might be tricky.. unless you force them to enter something into the form you can validate against? If you could move the solution to a Power Pages instance you could support authenticated sessions and use dataverse to track who has / hasnt submitted in a more seamless way. Using an MS Form - do these companies have a license to your tenant? as anonymous access shouldnt support file attachments. If they are licensed you'll be able to grab the user account of the submitted item in the dynamic content in Power Automate - eg their email. So.. to spit ball an idea - I'd build a list of companies I expect an email from in SharePoint or Dataverse and add a yes/no column for 'Submitted file' or similar, then when a form submission comes in I'd compare the submitted by email to the email for the company and if there is a match, then update the matching sharepoint item column for submitted file and set to true/yes. Then create a couple of scheduled Power automate workflows - 1 to run as the reminder message - so use get items from your list of companies where submittedfile eq false, then send each an email. The second scheduled workflow would be a clean up workflow to run just before your month end to reset all your company submitted attachment values back to false you are prepped for the next month.. you could put in an email action here as well if you wanted to send each company a mail to remind them to submit.. Hope this helps a bit.. main thing is trying to get the match on the company against who has submitted a file.
Thanks, Richard! This is very helpful. I have tested my flow and there are no errors but I didn't get the email. I have a column in my SharePoint list call email. In "send an email" in the 'To' field I use dynamic content from the SharePoint email column. Any idea?
Hi Ruby - first step I'd do would be to review teh history of the workflow and expand each of the actions to see the data flow. Especially expand the email step to see where outlook tried to send the email and compare if that is matching what you want. The other thing to check would be on your filter - if you are using one - I've seen some folks type the name email eq 'test.com' where email is what they think is the name of the SP column - however if they then navigate to the SP list and access its settings then click on the column called 'email' it can be called something like email1 or thbul or some other random name. If you are still stuck - just send me screen shots of your workflow history and we'll see if we can debug it together.
Hi Richard, how would I get the items if my data source is Dataverse? I tried getting it using the get a row by id but it's not showing me the fields to select the dates?
Yes it really helped tremendously. I spent so much time converting time, etc etc....this was awesome. And, thank you for letting me know about the mic @@Richie1902
Hi Richard thanks for your video. I followed your steps and worked perfectly using a "date and time" column but when I used a "calculated" column didn't work and showed error in "Get Items 7" and it is not a speech mark issue. Can this be done using any type of column? Error details: The field 'Start_x0020_Renewal_x0020_Proces' of type 'Calculated' cannot be used in the query filter expression.
Hi Marianne - odata filters will work for most column types in sharepoint - but you are correct - they wont work for calculated column types - but the calculated column equation could be placed into a Compose Action or Variable within Power Automate before the get items? it might help? Thanks for your feedback.
@@marianneweisser2129 ok- this web resource is useful www.odata.org/documentation/odata-version-2-0/uri-conventions/#FilterSystemQueryOption and my buddy Reza does a good job here too - ua-cam.com/video/yeAnuTB85eg/v-deo.html
Hi Chrissie - are you wanting it to be on a specific data AND time? or just the date? SharePoint will still read the date time like 2023-03-12 : 09:34 AM type thing - so if wanting just date you'll need to use the substring to just get 2023--03-12 so you filter on sharepoint will look more like this - startswith(YourSPDateTimeColumn, 'YourSubStringValue') Hope that makes sense!
@@chrissiemungia2514 - happy to chat on this via email or on a teams call if it'll help rich@brighterdays.co.nz If you can look at the dates you are filtering on - so run the workflow then have a look at the green tick items and expand to see the dates selected - then check these in your data source. If you want to send screen shots or any thing over - happy to try to triage with you.
Project Online has connectors in Power Automate already - so that one should be easy to compare project data against today. If your project server is an on premises instance - you will need to use a data gateway, then as project server is mostly sharepoint - you could use the sharepoint connectors to connect to the top level site collection to get this data. personally, never done it, but the theory checks out!!
Hi Adetutu, Planner does expose the Status of a task - so I would add to my filter the date range and the Status ne 'Completed' or similar to avoid any notifications running on already completed items,
Ricard thanks for the video. I wanted a flow to alert staff 7 days before they are scheduled for orientation. I followed your steps and everything worked except the email piece. I added added Name and Start Date as Dynamic content but it did not show Start date in the email when I tested it. Plus when I click on the send email after it runs to check it it's grayed out? Any ideas.
Hi Peter - sounds like your filter may not be finding any results which is why the actions further down the flow are grayed out. You can look at previous runs of the workflows and expand actions to see what values your filter contained - i'd start there. Also - as the filters are using odata - they use the sharepoint system name of the date column you are checking - so another thing to check is to go to the list settings in sharepoint and click on the date column to compare its name with the name you are using in the filter - you'll see the sharepoint column name in the url of the list settings page when you click on a column. If you are still stuck - please send screen shots of the flow or an exported zip file of the flow to richard@brighterdays.co.nz and I'll take a peek..
@@Richie1902 Thanks for getting back to me I was on vacation last week. I re-created the entire flow and it worked! I only need a 7 day notice so I removed the 14 but if a person enters a "DueDate" less than 7 days how do I add that to the flow? It would be like a = to or < less than "DueDate" statement? Thanks Pete
@@peterjukoski3311 hello can you help me how to fix the error? cause i have the same issue as yours. the email is not being send and grayed out. please help me :(
Hi - if its sharepoint column date only it'll come through as yyyy-mm-dd looking at your columns I would maybe create a workflow for get items on your list - limit the returned items to 2 then run it and open up the results / outputs to confirm your column names - the odata filter uses the system name of any column used - so good to confirm the columns are correct first - then move on to checking your filter syntax - hope this helps!
Thanks for tutorial, but I can't get past the Get items part. 'Flow run failed'. Error details are The expression "Due date eq '2023-01-04'" is not valid.
@@WayneCurran Hi - yep thats correct. You could use power automate to calculate a value for a date column instead of your calculated column? so make due dates based on last modified or created date etc. Hope that helps!
Excellent video and just what I was after as I am a compete novice when it come to Power Automate. The only issue I am having is that because I am using a ' Calculated (calculation based on other columns)' I keep getting the error - The expression "Next Review Date eq '2022-09-21'" is not valid. Creating query failed. clientRequestId: 91296c87-4b8e-4b43-8b43-577eedaffd0b serviceRequestId: 91296c87-4b8e-4b43-8b43-577eedaffd0b How do I create the part of the flow that will stop this from happening? Any advice/guidance would be greatly appreciated. MT Robin
Hi Robin - unfortunately calculated columns are not supported in power automate at this time. So you'd be best to try to recreate the calculation within power automate if you can - eg a date range or something like that. Also - check the 'real' name of your column - in odata it is likely to be NextReviewDate or maybe New_x0020_Review_x0020Date - - best way to check is to go to the sharepoint list and check List Settings - click on the column name and in the url of the page you will be sent to - you will see the actual system column name.. this is the name odata works with.. might help!
Under my Get items I'm getting an error: "The expression "Expiration Date eq '2021-07-01'" is not valid. Creating query failed. clientRequestId: a669c26b-6a9e-4fc8-b2f9-ada0f0f207a3 serviceRequestId: a669c26b-6a9e-4fc8-b2f9-ada0f0f207a3" I know for sure that one of the dates in my field is 07/01/2021. Is it because of my date format?
Hi Elizabeth - this is a SharePoint date only column? If so the date format looks good "2021-06-21" yyyy-mm-dd is your standard syntax can you check that your column isnt date + time - as if so you may need to substring the column to get just the date and exclude the time out. Hope this helps!
Hi Ruby - so you need to filter your list data and use ge for greater than or equal to and le for less than or equal to filters. in Power Automate I would set up a scheduled workflow to run daily and then use the action get future time and configure that for 7 days, then I would use an expression or substring to set that value to a utc format just on date eg 2024-12-25 then repeat and do the future date for 14 days. So then you would have 2 dates 7 days and 14 days from now. called 'My7Days' and 'My14Days' Then in your datasource if we had a column called duedate. You could then run a parallel branch one to find the 7 days items and one to find the 14 days items. the filter would like like duedate le 'My7Days' I'll see if I can get time to show this in a tutorial to show how to do this for you. Hope this helps!
Thank you Richard. I follow your step until get items 7 ERROR prompt out. error details the expression "DueDate eq 2021-04-15" is not valid. creating query failed. How to fix? DueDate formatting change to date and time. Custom list DueDate is 23/05/2021 00:00.
Hi Alya - the apply to each action will be greyed out if no items match your filter conditions in the get items prior to it. I got your email - did you want to send through an update with the filters you are using in the get items action - would be good to see the screen of a run workflow so we can see what the filter called - hope this makes sense!
Hi Richard, Is there a way to send 1 summarized email reminder to an individual. Sometimes there might be 50 tasks due and I don't want 50 reminder/emails to hit their Inbox. Please advise.
Hi A P - yep you could do this - its not out of the box as such, but yes you could get items / tasks needed per a person column or assigned to column, build an array of these users, then either clean out so its distinct per user OR only add to the array if they dont already exist in the array. Then per person in the array get open tasks and populate summary data into an html table or similar, then send just one email. So yes its possible to do for sure, but will be fiddly in terms of getting unique users into your array.
Awesome tutorial. You not only just gave the steps to copy, but also gave background items to look for that could trip it up. I was struggling with one, it had to do with having the correct name for the column in the flow. Thank you.
One of the best tutorials I have tried. Clear, to the point, well explained. Thank you so much.
Clear explanation. Thank you. Your audio could be a little louder.
Excellent tutorial. Concise, clear and purposeful throughout. No wasted time or words. THANK YOU 👍
Hi Richard, thank you SO much for video I have been scouring the internet on how to do this for the past 2 weeks at work and this video allowed me to accomplish what I needed in 15 mins. Cheers!!
You made me look like a genius today! Thank you!!! I did encounter that apostrophe issue and never would have figured that out.
Now what I need to do in addition is provide a link of the views of the almost due and overdue tasks to the end user and manager
Awesome Steve!
If you want to show a range of dates - just change your odata filter from "duedate eq 'thedate'" to something like "duedate lt 'thedate+10' and duedate gt 'thedate-10'" or however you want to set up your date range..
Hope that makes sense!
Thank you Richard that is really helpful. I had been struggling to do this until I found your video. Your explanation is very clear for each step - much appreciated!!
Hi Brian, can you let me know your flow has run or not after perform via Richard's video. In "send an email" in the 'To' field, I have performed as your instruction but I can't expand it when I run Test, any idea?
Just wanted to say this is the best you tube I have watched on setting up this type of reminder.
Awesome! Thanks Helen!
I’ve been looking for a tutorial like this for weeks!! Thank you!
Really helpful, thank you. Could you do a video for when a reminder should be sent 14 days after the item was created please?
This so great Richard! It’s simple and easy to understand! 😊 just wanted to know how to format the date to let’s say ‘dd-mm-yyyy’ bc what it being returned it the default system date which is yyyy-mm-dd. Thank you!!😊
Hi Francis - you can use an expression for this if you wanted to or the convert time action.
Convert time is still very usa date format but if you were ok with a format like Wed 16th August that might work.
Else using the expression editor would help like this formatDateTime(items('Apply_to_Each')?['YourDateField'], 'dd-MM-yyyy')
just add in whatever the item is that you want to convert..
Hope this helps!
Rich
Perfect! I got my email alert workflow working in less than 2 hrs
Clear, concise, really helpful. Thanks for this Richard. Cheers!
Brilliant tutorial mate. This was what I was looking for. Thanks again.
Thank you for awesome video !! how about the reminder 7 days before the due ?
Excellent video! Is it possible to send a daily reminder for something that's overdue?
sure is - just change the filter to get anything that is overdue - so duedate lt today.. lt less than gt greater than - wanna go with due date less than today I think. Good luck!
Awesome. Tell me, do you receive email for each item or one email with a single table with all reminder items?
Thaks a lot, i am trying from four day's but not able to do, after seeing your video i have done it. Once again thanks 🎉🎉
Hi - please can you let me know if you are seeing any errors in the workflow runs? or if the workflow just isnt finding the correct items? or something else?
If you let me know as much as you can - I'll be able to give you some help. Thanks!
@@Richie1902 I have found no errors.
Thank you Richard that is really helpful. In "send an email" in the 'To' field, I have performed as your instruction but I can't expand when I run Test, any idea?
One of the best explanation. Good job!! so Helpful
Thank you Richard. Great work. How to do this scheduled follow up only if there is no response on the first email?
Thank you Richard. This was exactly what I was looking for. I'm subscribing for more content.
Clear and to the point explanation, thank you. Is it possi8ble to put if condition in the Filter Query of the Get Items
Hi - it sure is. Click on the advanced settings of get items and then you can add odata filter text into the filter field eg Title eq 'Test' or Status ne 'Completed' odata will support many filter conditions like a date range and status - but it wont support calculated column values in sharepoint - so it will mostly work from text or date or choice fields. Hope this helps!
Thanks richard! Can i check, what should i add in my filter if i only want to send emails to those that are not completed? Is this expression correct?
Duedate eq '2021-10-21' and Status ne 'Completed'
I tried but not working
Hello Richard!
Very nice and informative video as always! I did encounter a problem near the end though. I get the following message
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)?['Samordnare!]" in path
emailMessage/To' with type/format Array' is not convertible to type/format 'String/email!
I get this message when i pick my column called ”Samordnare” which is a People and Groups column where you fill the name from an organization (containing email too) on the ”Send an email (V2)” function.
You have any idea what the problem is?
Im very appreciative of your help.
Thank you very much!
Thank you Richard for the great work, I have one question, if the due date gets updated later will the workflow update the schedule?
Great tutorial and thank you! I have run into a following problem, I keep receiving emails after the due date has passed. I only need a reminder on the dates. What shall I add/modify to have only one reminder on the due date?
Hi there - if you wanted to post your odata filter from your get items action in the workflow I could look at that.
But essentially you will want to ensure you are using an 'eq' or equals operator - so get me all items where myduedate eq '2023-01-21' with the date being calculated everytime the flow runs so it works out whatever the dynamic date should be.
This way you will only get the items that are relevant and not past dates - hope this helps - happy to look at your filter - just post here.
Richard - in Apply to each // Send an email (v2), can I copy my MS Teams channel email into the *To section? Doesn't seem to work. Thank u very much / great video.
Richard - You are just Awesome!!
Your videos are really helpful. I'm actually looking for creating tasks in planner using excel and then looking for triggering email wen a bucket is completed or nearing completion to motivate team for completing targets. Please suggest ways for it..I'm new to flow..thanks in advance
Thank you very much! First explenation of steps including everything I need to know as a Noob.
Yay! Thanks for the feedback!
Good day, I would like to know why can't I use calculated column in the filter query ? How to solve this problem ? Thank you.
Thank you for walking through the entire flow, Richard. I need to add a condition that a reminder should run based on the value in a certain column. How do you think I should do that?
Hi Niranjan - there are actually three 3 methods I could use for this - either set a trigger condition (in settings of the trigger - syntax like - @equals(triggerBody()?['PA_Completed_WF_Run']?['Value'],'No') ) OR have a Power Automate condition control to check the value of the column then run the reminder in an apply to each control OR change the odata filter in the get items to extend eg Title eq 'Test' and Date eq 'Overdue' - so lots of ways to do it.. hope this helps!
Thanks for the great tutorial. However,just a quick question, is this PAD able to run even when the laptop is in sleep mode or even shut down?(Assume Scheduled Flow is chosen)
Hi - by PAD you refer to Power Automate Desktop? The clip I'm showing is for a Cloud Flow - so this is always online and will run on the schedule as its cloud based. For Power Automate Desktop - if you have created a scheduled cloud flow that is calling online services such as o365 apps or dataverse or using a data gateway to connect to a file server etc then these will also work - if you have created an attended or unattended desktop flow the device these are running on do need to be on/available. Hope this helps!
Thanks Richard !!! can the automation captured through power automate be converted to an executable file?
hi richard, do you know why my "send an email" part isn't working? when i run the flow, the flow run successfully but the email part is greyed out
Kia ora, thank you,!! This is one of the easiest "how to" videos I have seen on this. Are you able to help? I am needing to reference this from a library, not a list and am stuck at the "Get items" as it asks for a list name. I have created a library of policy documents and want to add an email reminder to the owners of the documents 60days and 30days before the review date. Any guidance on this would be fantastic. Thanks in advance
Hi Kiela-lee - thanks for the feedback - a document library uses a different action to a list - so rather than Get Items - we use Get files (properties only) then you can apply the same odata filter on your library columns as well - hope this helps!
Thank you, this has been really helpful! Is it possible that the reminder would be sent in one mail and not individually? For example, I have 3 tasks due tomorrow. Can this be sent in one mail only? Thank you!!
I would really like to know this as well. I have been trying to work with arrays but I don't get the filter array option in my account of power automate. So, I can't finish the flow. Maybe this will be useful for you @Bianca Francisco: powerusers.microsoft.com/t5/Building-Flows/Reminder-Flow-for-once-a-week-for-Overdue-List-Items/td-p/330694
@@pinkijs93 thanks so much! I'll check this out
@@biancafrancisco941 were you able to accomplish what you were going for? i think i might be trying to do the same thing that you are... any guidance would be great!
@@subjecttochrist you may check it here ua-cam.com/video/0TpjWPYlxY4/v-deo.html
@@pinkijs93 you can check the answer here ua-cam.com/video/0TpjWPYlxY4/v-deo.html (it's something about appending the string variable)
Hello Richard, thanks for the wonderful video, all the steps worked for me except when it reaches send email I am unable to see the records and the email is not getting triggered. Can you please help here to get the email triggered
Hi Raghu - this sounds like your filter isnt picking up any records? I'd suggest adding a compose step after you get items in an apply to each step to output the id or title of the email to check whats happening?
Thank you Richard, loved your video. I have a question though. How do you ignore the weekends while setting up the 7 day or 14 day reminders?
Hi Azizur - you'll need to use some variables and expressions to achieve that functionality - I dont have a clip for it yet - but check out this guide which should help you - powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Calculating-working-days-in-Power-Automate/ba-p/557278
Interesting but I am wondering what the best approach would be if you want in include specific attachments to these emails that you want to send out.
Hi Eric - i guess you could have a column in the library or another method to isolate the specific attachments via the odata filter.
So helpful! Is it possible to do this off of a document library rather than a list?
Hi Erin - yes it is - just need to use Get Files (Properties Only) vs Get Items for your SharePoint connection - other than that - everything works the same! Good Luck!
This video is very helpful. How do I add a reminder for items not completed before due date? If my list has a progress column with dropdown selections and I only want the reminder to go out to those who have not completed the task before the due date.
Hi Nasmee'a - for this requirement i suggest adjusting the odata filter on Get Items to ensure you only pull back the people you want to remind. - Hope that helps!
Hi Richard, thanks for sharing this video. I am using the exact steps to send reminders a day before the due date. My flow ran fine for two days and stopped/did not notify me on the third day. Upon checking the flow, it seemed that the 'Get Files' action is not returning any value. However, the DueDate column has items in there. Any help in understanding the issue will be much appreciated.
Hi there - if the workflow isnt returning any values - I would take a look at workflow run and expand the get items action and check the date that is being looked for and any other values in the odata query - then check your data source and do a comparison.
If you think something should be getting returned we can look at your odata query and validate it then adjust as needed..
Let me know how you are getting on?
@@Richie1902 Thank you so much for the guidance. I realized the flow was not returning values because I set it up on the Document library. I created a new library and applied the flow there, and it ran fine. Thanks for sharing valuable content that broadens our knowledge about workflows and power automation.
@@anamahmed2765 - awesome!
Hi Richard, I've tried your tutorial and it works splendidly. Since my list is built upon Excel in Onedrive, I made some slight tweaks and it still worked. Just wondering, is there a way to lump several rows of SharePoint in an email reminder instead of sending them individually?
Hi Syazwan - yes - you can do this by building an Array / Table in your workflow to capture all the relevant messages for each person you want to send to - you'll need to initialise the array variable, then use a get items/rows action, them a for all loop and append to array variable action inside the for all loop - hope that is enough to get you started!! I have a clip on using an array for something similar - let me know if that helps!
@@Richie1902 Hi Richard, thanks for getting back to me! Do you mind linking the video for Creating Arrays that you mentioned? I tried flicking through your channel but I couldn't find the right one (not so fluent in Power Automate yet, it could have gone past me while searching)
Hi Richard! Love this video - but I am running into issues in the filter query step. it says that my column does not exist in the error, but it does exist. Any ideas how to solve this? I am desperate!
Hi Isabela - a filter wants the system/hidden name of a column often this can be different if you've renamed a column - best thing to do is go to sharepoint and open the list or library settings and click on the column you want to check - on clicking a new page will open - check the end of the url for this page as this will show your 'real' column name you need to use in an odata filter - hope this makes sense!
Richard - this is a great video. It is almost exactly what I needed. In my situation, my due date column is calculated so I get an error that the "type 'Calculated' cannot be used in the query filter expression." Is there a solution for this?
Hi Sarah, currently calculated columns are not supported as a column to look up to in Power Automate. Workaround is to either calculate the date within power automate as the workflow runs OR on an item created or modified you use power automate to recalculate the date then update the item.
If updating an item - you have to be careful to not kick off an infinite loop so the workflow keeps running.. hope that helps?
Hi, your tutorial works for single email address, what if the names are in Outlook name with more than 1 names and not email?
thanks richard! however, how i do if my data source is from power bi or excel?
Yes... thank you so much for posting. Worked like a charm.
How do you make these due date notifications for only items that are open/in progress? Is there another action to tell the routine not to run if the status is closed?
Hi Taylor - yep - when you use the get Items action in Power Automate - you can toggle the odata filter to pull back whatever data you need - so if you have specific values you need to pull back thats where you would do it. So due date eq today and status eq open type thing - then you only get the data you need.
If you need to pull different status types back - you but only run the notification on a particular status you could either use the data operation of Filter to reduce the items down to just what you want OR you could use Apply to Each and have a condition within the apply to each step - so if a value eq the value you want then do something. Filter is a bit easier/efficient
Let me know if this helps!
Richard thank you for this great tutorial! My work colleagues are impressed ! Can I make the flow dependent on the "Status" column in addition to the overdue date ? I am trying a few things right now, however I keep getting error messages. The mail should be sent only to those where the status is not "completed". Do you have some tips ?
I saw that you have already answered the question :) And it worked! You are great, thanks!
Hi - thanks for the feedback - yes you can use multiple filter conditions in a get items for a not completed you would want StatusColumn ne "Completed" and DueDateColumn eq "2023-02-07" for example.
Hi Richard, this is great. I tried get around the weekends issue i.e. only send on business days by having 3 calculated date fields in Sharepoint off my First Follow Up date field. i.e. First Follow up = Day 1, then calculate 3 business days, 5 and 7 business days from these. Then on those dates send a reminder. However looks like ODATA query doesn't like calculated columns I get an error. Is there a better approach do you think?
Hi mkavo - you are correct - there is no support for calculated columns in odata filters at present in Power Automate - SharePoint connector.
You could move your logic to create the dates into Power Automate potentially? - there is an action called get future date - so on new item creation call power automate and use get future date to 5 and 7 days and then pop these values into an update item sharepoint action!
Hope this makes sense!
@@Richie1902 Hi Richard, i'll have to calculate business days somewhere in there, I'm sure there is a formula but already have that part working in SharePoint. I changed my ODATA filter fields to date fields they enter data into and it works fine i.e. Date 1 ne null and Date 2 eq null, this still gets the items in scope down drastically, then i can use my calculated due date fields in conditions and look for where they equal today and if so send the appropriate email and do appropriate updates..
Your video helped immensely in getting to this stage to try things so thanks!
Thanks for the video but please turn the volume up on the video, i find myself trying to guess what you are saying.
Thank you Richard, this is brilliant. I've built the flow to send a reminder email 14 and 7 days prior to a licence expiry and it picks the expiry date from a SharePoint list column. But, it is still looking at whether the date is 14 days from the date I created the flow (yesterday) and not the current date. I set the timezone to my own (Brisbane). What am I doing wrong?
Hi Erica - if you want to send me some screen shots of your workflow - expanded to see filters or date actions I should be able to help you correct the issue. Date wise - you'll want to get current date then convert the date and make sure you use the converted date action in the formula for any filters - hope that helps - email is richard@brighterdays.co.nz
Dear Richard, at 10.07 you mention you can specify a column in the get items. So you can send an email to those people. Could you let me know how to do that? running into issues with this last step.
Hi Audrey, within each Get Items action you can expand the action to view Advanced Settings and use the Filter Query option to use an Odata filter to limit the items returned. Odata is a specific syntax and calls the system name of the column you want to filter on - normally the syntax would be similar to Title eq 'Example' or NumberColumn gt 5.
However if you are using SharePoint and have renamed columns in the list you are wanting to filter - you must still need the original name of the column not its renamed value, plus if you have spaces in your column names you'll need to use the correct sharepoint name value - so rather than My Column Name eq 'Test' it would could possibly be My_x0020_Column_x0020_Name eq 'Test - hope this helps!
Thanks Richard, this is very helpful. Unfortunately I keep running into issues in the last step (sending the email) and don't understand why. Issue: low 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')?['k21v']"' in path 'emailMessage/To' with type/format 'Array' is not convertible to type/format 'String/email'.'.
now it seems to stick with error: 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.
Hi Audrey is the column k21v a person type field? If so you'll want to use syntax like this k21v/EMail to just get the email and not the whole person column array - note the capitals on EMail :) - Hope that helps - ping me on richard@brighterdays.co.nz with screenshots and column information if you are still stuck..
Hi, I get an error when running this flow, the date format is does not match yet I am using the same Australian/UK format as you are and have copied your instructions to the comma..? Can you assist with why this is not working for me..? I can't see where I need to format the date code but you didnt need to do this..?
Hi Rebecca - can you look at the flow run with the error and see the format that the date has come through as? Also - can you let me know if the sharepoint column you are using is a date only or a date + time type? Happy for you to send screen shots of what you are seeing / your workflow to rich@brighterdays.co.nz
@@Richie1902 thanks Richard, I ended up figuring it out by watching a different tutorial. One detail which I don’t believe you mention is if the column name has spaces on your SP site, you have to remove these as SP does not read spaces. That was my issue! So simple! But thank you as your video helped enormously 👍🏻😊
Thank you. - if i want to send email before due date, can you suggest?
Hi - as long as you are recording when a task is due you can use the methods shown in this clip to send notifications at any stage before the due date - this clip shows how to do it for 7 days before or 14 days before - I hope this helps
Thanks you.... but what can we do when in SharePoint list expiration date column( Calculated column) have 2042 and set due date reminder on calculated column.
Hi Kapil - calc columns are not yet supported in power automate - I'd suggest having another due date column and use power automate -'get future date' action to define the due dates on item creation etc - if you do it on edit of items - make sure you dont create an infinite loop on your workflow!
It's really good and helpful for me!! Thanks! What if I would like to add one more condition like specific value in same sharepoint list with the 'Due date' column?
is it working if I modify on the query value like - DueDate eq 'substring' && Status.Value eq 'Submitted'?
Hi - yes you can extend the odata filter against other columns - if your sharepoint column is date only it will be in a format like yyyy-mm-dd or if datetime it will have hours and seconds - but you could do a left(duedate,0,10) expression to just get the yyyy-mm-dd part - hope you get it working!
Will there be a problem if my due date column is in M/d/yyyy format? And the flow is yyyy-MM-dd?
Hi, I tried to add an additional query item, but got an error. I want to only filter on 'Open' items to send the email reminder e.g. Next_x0020_Review eq 'substring' && Current_x0020_Status.Value eq 'Open' Any suggestions why it wont work would be greatly appreciated?
Thanks for this video.. I have one question.
How to add the data/ table in SharePoint. Could you please help me on this..
Thanks in advance
Hi - if you need to create a list in sharepoint - this tutorial should help - ua-cam.com/video/k0vLHYkiBIY/v-deo.html or if you are trying to figure out how to connect your app to a list - this is done via Connectors in the power platform - this guide will help understand how to set them up/manage them - docs.microsoft.com/en-us/powerapps/maker/canvas-apps/add-manage-connections
can we add this method in microsoft planner?
Thank you Richard. I used your instruction to apply to an entire document library & it works - sending due date reminders to document owners. However, if I put in more than one document owner in my library, the flow fails. The error reads 'Error details: 'The API operation 'GetItems' requires the property 'body/value/0/Documentowner' to be of type 'Object' but is of type 'Array'.' Do you know how I can just this flow for multiple document owners? instead of just one
Hi Sarah, I recently helped someone else with this - so planning on doing a clip to show this - essentially (my easy option - there are other ways to do this..) - you will want to create a string variable then use an apply to each on the multi select person field and for each email found use append to string variable to add the email and a semi-colon - then use this variable in the email's to field - I'm hoping to do a clip next week - but maybe this info will help until then!
@@Richie1902 thanks so much for getting back to me! I’m a bit of an amateur so might wait for the video! Thanks a mill
Wow It works!! Thank you Richard!
Great video. I am trying to do the same but the only difference is I want to send reminder emails based on due dates but when Status column value is in progress. I have task list in SP and do not want to send reminder emails if task status is Completed. Can you please help? My flow is scheduled to run at 8am everyday but it sends emails for tasks no matter what the status is.
Hi! in your Get Items action, you should be good to add in the odata filter like so 'and Status ne 'Completed' this will then ensure all the completed items are not pulled into the array of items to be emailed.
let me know how you get on - this assumes that the Status column is a text or choice column - but it should be straight forward to implement. Good luck!
@@Richie1902 Thank you for direction, it worked. I just had to figure out how to put odata filter with multiple conditions :))
how if all days except sunday? Thanks,😀
Thx Richard for your amazing sharing. However, when I tried to use the field name in the expression of Field Query, the test outcome shows that 'Column '_x9884__x8ba1__x5b8c__x6210__x65' does not exist. It may have been deleted by another user.' I am really confused now.
hi - so 2 things to try - go to the sharepoint column via list or library settings and then click on that columns name - in the url you will see the system name of the column - copy that name and put into your filter - sometimes the names do get cut off a bit..
second check - do a get item - put in an id with no filter - with a ID value you know has a value on this column - run the workflow and then view the results of the run expand the show outputs area and compare the column name to see if different..
Hope that helps you find the column!
Will try! Big thx!!@@Richie1902
Great video! I run into an issue when I try to have multiple people listed under the "assignedto" column. If I only allow for one person to be listed, I have not error but when I allow for multiple people to be listed, I get the error that the data is an array, and not an object. Is it possible for this workflow to work when multiple names are listed under the "assignedto" column? Thank you!
Hi Valentina - in this scenario an option you can use is to create a String Variable - then use an Append to String Variable action and add the dynamic email plus a ';' semi colon within an apply to each/for each based on the email column you are working with - then use the variable in the To field of your email action. There are other methods to do this as well - using the Array Variable, then parsing the Array and again using an apply to each on the email part of the array. Hope this helps!
Hi Richard, great video and very helpful. I am running into a problem with my emailing though. my contacts are in "sitecontact" column in sharepoint as a name which links to directory, so emails aren't listed. when I create send an email, it lets me select "sitecontact" in Apply to each, then "sitecontact email" in sub level Apply to each 2. The flows runs as successful but doesn't return values. it's greyed out assuming it stops at Apply to each then never moved to next level. is there a workaround?
Hi A T - your apply to each will be greyed out as that action cannot find any matches - I would check the get items filter action to check if you are getting any results - if not - check the system name of your sharepoint column - under list settings, click on the list and check the url of the page that loads to show you the column name that the odata filter is looking for.. hope this helps!
Great Tutorial. Helped me a lot.
Is it just me or your voice was too low.
Hi Richard
Can u help? I m stuck
Every month over 100 of companies need to submit a document to me via ms form and I need to chase them to they don’t submit on time , is there anyway can automate the whole process? Now I m doing manual checking to see who has not submit and it takes very long .
Appreciate if u can give me some ideas
Hi - using an MS Form might be tricky.. unless you force them to enter something into the form you can validate against?
If you could move the solution to a Power Pages instance you could support authenticated sessions and use dataverse to track who has / hasnt submitted in a more seamless way.
Using an MS Form - do these companies have a license to your tenant? as anonymous access shouldnt support file attachments. If they are licensed you'll be able to grab the user account of the submitted item in the dynamic content in Power Automate - eg their email.
So.. to spit ball an idea - I'd build a list of companies I expect an email from in SharePoint or Dataverse and add a yes/no column for 'Submitted file' or similar, then when a form submission comes in I'd compare the submitted by email to the email for the company and if there is a match, then update the matching sharepoint item column for submitted file and set to true/yes.
Then create a couple of scheduled Power automate workflows - 1 to run as the reminder message - so use get items from your list of companies where submittedfile eq false, then send each an email.
The second scheduled workflow would be a clean up workflow to run just before your month end to reset all your company submitted attachment values back to false you are prepped for the next month.. you could put in an email action here as well if you wanted to send each company a mail to remind them to submit..
Hope this helps a bit.. main thing is trying to get the match on the company against who has submitted a file.
Please can you assit me to setup a reminder to send the mail not to myself but to the document owner
Richard, do you know of a way to apply a filter in order to grab items that are within 7 days or less? is it " le ##"?
Have u solved it?
Thanks, Richard! This is very helpful. I have tested my flow and there are no errors but I didn't get the email. I have a column in my SharePoint list call email. In "send an email" in the 'To' field I use dynamic content from the SharePoint email column. Any idea?
Hi Ruby - first step I'd do would be to review teh history of the workflow and expand each of the actions to see the data flow. Especially expand the email step to see where outlook tried to send the email and compare if that is matching what you want.
The other thing to check would be on your filter - if you are using one - I've seen some folks type the name email eq 'test.com' where email is what they think is the name of the SP column - however if they then navigate to the SP list and access its settings then click on the column called 'email' it can be called something like email1 or thbul or some other random name.
If you are still stuck - just send me screen shots of your workflow history and we'll see if we can debug it together.
Got it thanks @@Richie1902 :D
@@minispringrolls hello can u please help me how did u make it work? cause i have the same issue as yours
Hi Richard, how would I get the items if my data source is Dataverse? I tried getting it using the get a row by id but it's not showing me the fields to select the dates?
Hi Michael - you'll want to use the List Rows action for Dataverse and apply a similar odata filter on your date columns. Hope this helps.
Thank you so much!
Excellent video, taught me a LOT!!! Thank you. My only feedback is that it was extremely difficult to hear your soft spoken voice :) Thanks again
Thanks for the feedback! Glad it helped! I've sorted my mic in my more recent videos - sorry about that.
Yes it really helped tremendously. I spent so much time converting time, etc etc....this was awesome. And, thank you for letting me know about the mic @@Richie1902
Hi Richard thanks for your video. I followed your steps and worked perfectly using a "date and time" column but when I used a "calculated" column didn't work and showed error in "Get Items 7" and it is not a speech mark issue. Can this be done using any type of column?
Error details: The field 'Start_x0020_Renewal_x0020_Proces' of type 'Calculated' cannot be used in the query filter expression.
Hi Marianne - odata filters will work for most column types in sharepoint - but you are correct - they wont work for calculated column types - but the calculated column equation could be placed into a Compose Action or Variable within Power Automate before the get items? it might help? Thanks for your feedback.
@@Richie1902 thanks for your quick response. Could you recommend any tutorial that I could follow?
Hi - can you let me know the type of filter you are trying to use? and I can see if I can refer you to a tutorial to help. Thanks.
@@Richie1902 I used odata filters. Thanks!!
@@marianneweisser2129 ok- this web resource is useful www.odata.org/documentation/odata-version-2-0/uri-conventions/#FilterSystemQueryOption and my buddy Reza does a good job here too - ua-cam.com/video/yeAnuTB85eg/v-deo.html
If I do have a date + time column, will the substring still allow the flow to get items regardless of their various times due?
Hi Chrissie - are you wanting it to be on a specific data AND time? or just the date? SharePoint will still read the date time like 2023-03-12 : 09:34 AM type thing - so if wanting just date you'll need to use the substring to just get 2023--03-12 so you filter on sharepoint will look more like this -
startswith(YourSPDateTimeColumn, 'YourSubStringValue')
Hope that makes sense!
@@Richie1902 Just the date! I am using the substring as shown in the video and tests are running successfully but no emails have been received so far.
@@chrissiemungia2514 - happy to chat on this via email or on a teams call if it'll help rich@brighterdays.co.nz
If you can look at the dates you are filtering on - so run the workflow then have a look at the green tick items and expand to see the dates selected - then check these in your data source.
If you want to send screen shots or any thing over - happy to try to triage with you.
Hello Richard, how can we use power automate to send alert for task over due from Project Server which is also a site in sharepoint ?
Project Online has connectors in Power Automate already - so that one should be easy to compare project data against today.
If your project server is an on premises instance - you will need to use a data gateway, then as project server is mostly sharepoint - you could use the sharepoint connectors to connect to the top level site collection to get this data.
personally, never done it, but the theory checks out!!
How can I use Excel online as base data instead share point.
Hi, how do stop the flow from running when the task is completed before the 7 days or 14 days. using sharepoint list not planner?
Hi Adetutu, Planner does expose the Status of a task - so I would add to my filter the date range and the Status ne 'Completed' or similar to avoid any notifications running on already completed items,
Ricard thanks for the video. I wanted a flow to alert staff 7 days before they are scheduled for orientation. I followed your steps and everything worked except the email piece. I added added Name and Start Date as Dynamic content but it did not show Start date in the email when I tested it. Plus when I click on the send email after it runs to check it it's grayed out? Any ideas.
Hi Peter - sounds like your filter may not be finding any results which is why the actions further down the flow are grayed out. You can look at previous runs of the workflows and expand actions to see what values your filter contained - i'd start there.
Also - as the filters are using odata - they use the sharepoint system name of the date column you are checking - so another thing to check is to go to the list settings in sharepoint and click on the date column to compare its name with the name you are using in the filter - you'll see the sharepoint column name in the url of the list settings page when you click on a column. If you are still stuck - please send screen shots of the flow or an exported zip file of the flow to richard@brighterdays.co.nz and I'll take a peek..
@@Richie1902 Thanks for getting back to me I was on vacation last week. I re-created the entire flow and it worked! I only need a 7 day notice so I removed the 14 but if a person enters a "DueDate" less than 7 days how do I add that to the flow? It would be like a = to or < less than "DueDate" statement? Thanks Pete
@@peterjukoski3311 hello can you help me how to fix the error? cause i have the same issue as yours. the email is not being send and grayed out. please help me :(
@@alyaharis6555 I recreated my flow, and I would be willing to send you the screen shots of the flow.
Really good explation and useful tips. Thank you
Best tutorials ! Thank you so much
If the date format in my duedate column is M/d/yyyy should i need to use it instead of the yyyy-MM-dd?
Hi - if its sharepoint column date only it'll come through as yyyy-mm-dd looking at your columns I would maybe create a workflow for get items on your list - limit the returned items to 2 then run it and open up the results / outputs to confirm your column names - the odata filter uses the system name of any column used - so good to confirm the columns are correct first - then move on to checking your filter syntax - hope this helps!
Thanks for tutorial, but I can't get past the Get items part. 'Flow run failed'. Error details are The expression "Due date eq '2023-01-04'" is not valid.
Seems I can't use a calculated column
@@WayneCurran Hi - yep thats correct. You could use power automate to calculate a value for a date column instead of your calculated column? so make due dates based on last modified or created date etc. Hope that helps!
Excellent video and just what I was after as I am a compete novice when it come to Power Automate. The only issue I am having is that because I am using a ' Calculated (calculation based on other columns)' I keep getting the error -
The expression "Next Review Date eq '2022-09-21'" is not valid. Creating query failed.
clientRequestId: 91296c87-4b8e-4b43-8b43-577eedaffd0b
serviceRequestId: 91296c87-4b8e-4b43-8b43-577eedaffd0b
How do I create the part of the flow that will stop this from happening? Any advice/guidance would be greatly appreciated.
MT Robin
Hi Robin - unfortunately calculated columns are not supported in power automate at this time.
So you'd be best to try to recreate the calculation within power automate if you can - eg a date range or something like that.
Also - check the 'real' name of your column - in odata it is likely to be NextReviewDate or maybe New_x0020_Review_x0020Date - - best way to check is to go to the sharepoint list and check List Settings - click on the column name and in the url of the page you will be sent to - you will see the actual system column name.. this is the name odata works with.. might help!
Under my Get items I'm getting an error: "The expression "Expiration Date eq '2021-07-01'" is not valid. Creating query failed.
clientRequestId: a669c26b-6a9e-4fc8-b2f9-ada0f0f207a3
serviceRequestId: a669c26b-6a9e-4fc8-b2f9-ada0f0f207a3" I know for sure that one of the dates in my field is 07/01/2021. Is it because of my date format?
Hi Elizabeth - this is a SharePoint date only column? If so the date format looks good "2021-06-21" yyyy-mm-dd is your standard syntax can you check that your column isnt date + time - as if so you may need to substring the column to get just the date and exclude the time out. Hope this helps!
Amazing!!!
Very Helpful. Thank you so much!
How do we include duedates that are equal or less than 7 days or 14 days from now?
Hi Ruby - so you need to filter your list data and use ge for greater than or equal to and le for less than or equal to filters.
in Power Automate I would set up a scheduled workflow to run daily and then use the action get future time and configure that for 7 days, then I would use an expression or substring to set that value to a utc format just on date eg 2024-12-25 then repeat and do the future date for 14 days.
So then you would have 2 dates 7 days and 14 days from now. called 'My7Days' and 'My14Days'
Then in your datasource if we had a column called duedate.
You could then run a parallel branch one to find the 7 days items and one to find the 14 days items.
the filter would like like duedate le 'My7Days'
I'll see if I can get time to show this in a tutorial to show how to do this for you.
Hope this helps!
@@Richie1902wow i will be waiting for that tutorial. I tried days ago to make my flow but i received infinite loops of email after testing.
Thank you Richard. I follow your step until get items 7 ERROR prompt out. error details the expression "DueDate eq 2021-04-15" is not valid. creating query failed. How to fix?
DueDate formatting change to date and time. Custom list DueDate is 23/05/2021 00:00.
Hi - duedate looks like its expecting a string value so you'll need to add single quotes - eg DueDate eq '2021-04-15' cheers
@@Richie1902 Get items 7 - Filter Query DueDate eq '@{outputs('Substring')?['body']}' error prompt
I follow your flow tested run ok till compose. When i add get items 7 error prompt out.
ERROR Details The field 'DueDate' of type 'calculated' cannot be used in the query filter expression.
Filter Query = DueDate eq '2021-04-27' is tally with substring Output 2021-04-27
why does my send an email at the apply to each turns grey? i cannot view the send an email as it greyed out. please help me to fix this
Hi Alya - the apply to each action will be greyed out if no items match your filter conditions in the get items prior to it. I got your email - did you want to send through an update with the filters you are using in the get items action - would be good to see the screen of a run workflow so we can see what the filter called - hope this makes sense!
Thank you. Very helpful indeed.
Great tutorial. Thanks
Many many thanks for this
Very helpful! Much appreciated!
Hi Richard, Is there a way to send 1 summarized email reminder to an individual. Sometimes there might be 50 tasks due and I don't want 50 reminder/emails to hit their Inbox. Please advise.
Hi A P - yep you could do this - its not out of the box as such, but yes you could get items / tasks needed per a person column or assigned to column, build an array of these users, then either clean out so its distinct per user OR only add to the array if they dont already exist in the array. Then per person in the array get open tasks and populate summary data into an html table or similar, then send just one email. So yes its possible to do for sure, but will be fiddly in terms of getting unique users into your array.
@@Richie1902 Thanks Richard for your quick response. Do you have any video created on how to do this?
@@AP-yn2qc not yet - but I'll see what I can do!
@@Richie1902 Please do. I really need to get this done for my team. Will be eagerly waiting for your next video on this. Appreciate your help!
@@AP-yn2qc - here you go - ua-cam.com/video/uddMdvMUZwo/v-deo.html enjoy!