Send periodic emails based on date column in SharePoint

แชร์
ฝัง
  • เผยแพร่เมื่อ 4 ม.ค. 2025

ความคิดเห็น •

  • @jaycark
    @jaycark 7 หลายเดือนก่อน +3

    This was super informative. Thank you!

  • @mletormd5231
    @mletormd5231 9 หลายเดือนก่อน

    thank you so much this was super helpful but wondering if there is a way to compile one email with all of the data rather than a separate email for each individual task.

    • @cvkealey
      @cvkealey  9 หลายเดือนก่อน +1

      Yes, this is possible, but it gets a little complicated. What you need to do is get the whole list of tasks, then extract from that a list of unique assignees. You would then add an apply to each loop to iterate over the list of assignees. Within the loop, you would filter that original list to the tasks assigned to that user, create an html table of the data and send the email.

  • @lindamargrie7751
    @lindamargrie7751 9 หลายเดือนก่อน

    Hi Chad, First off great tutorials. I am wondering if we can go one step further. I have a list with a due date - I want to send a reminder email to the person its allocated to on the due date - your tutorial taught me how to do that MY NEW QUESTION is can I embed a calendar or questions into the email space where the person can enter the date they completed the task - which will then automatically update the Completed Date column on the list. I also want them to be able to set the new due date (e.g. if it happens every quarter they can put the new due date) and the process continues

    • @cvkealey
      @cvkealey  9 หลายเดือนก่อน

      You may be able to do that (give them a way to set the "date complete" using an adaptive card. I know there are lots of cool things you can do with those, I've just not worked with them enough to say for sure. What I would do (because I know how to do it) is send them a link to the item so they can access it and update the date. It's less convenient, yes, but much simpler to configure and likely more reliable.

  • @annieiden8742
    @annieiden8742 ปีที่แล้ว +1

    Great video! I got it to work... but have one additional need. How can I incorporate an additional condition based on whether another choice field in the SharePoint list is a specific selection. The use case I have is needing to send notifications to new hires one week after the start date, two weeks after the start date, and three weeks after the start date, but ONLY IF they have not marked their onboarding tasks as complete. So if they are still marked "In Progress" on the SharePoint list, I want the email to be sent. If it is marked "Complete", I don't want it to send the email notification. This means once they complete their onboarding tasks, they stop receiving the notifications.
    The reason I need to use this date variable is that people get added to the list as soon as they accept an offer, so I don't want emails triggered from new item created or modified in the list. I want the emails to start once their start date has passed.

    • @cvkealey
      @cvkealey  ปีที่แล้ว +2

      You could add other filters to the Get items action. If that "status" column (or whatever it's called) is a single-select choice question, you would use something like eq 'In Progress'. Replace with the internal name of your status column and be sure to use single quotes around 'In Progress'. When using multiple filter criteria, you need to join them with "and" or "or" as appropriate. These pages are excellent resources for learning how to use the Filter query field effectively: sharepains.com/2021/01/19/filter-query-get-items-power-automate/# and citizendevelopmentacademy.com/blog/power-automate-filter-query/

  • @angelogilio9565
    @angelogilio9565 10 หลายเดือนก่อน

    Hi MAN! First of all THANKS for your help you very help full! I have a question for you. Is possible to re-set a date list filed according month? For example i need that after having approve by email a job the specific date reset itself one year later or two months later and so on...

    • @cvkealey
      @cvkealey  10 หลายเดือนก่อน

      Sure. If you're using an approval flow, after the approval completes, you just update the item to update the date field. To do the calculation, you can either use the addDays expression or an add to time action.

  • @MakeForLifeWorkshop
    @MakeForLifeWorkshop ปีที่แล้ว

    I'm a bit new to this, but would it work to rewrite the expression to countdown days instead of months by replacing the "6" with "180" and the "Month" with "Day"?

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      If every month were exactly 30 days, that would work. But they're not.

  • @edgarpinia3564
    @edgarpinia3564 ปีที่แล้ว

    Hello, I have a question, can this reminder be made with a start date and send you the reminder for seven days only? with an additional condition?

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      Sure...you could do that a few different ways, depending on what you actually want or need to do. The simplest option would be two columns (reminder start date and reminder end date). Then, the daily recurring flow would look for any item where start date is less than or equal to the current date and the end time is greater than or equal to the current date.

  • @joaopedrolealrovedodemello1434
    @joaopedrolealrovedodemello1434 2 หลายเดือนก่อน

    Good night from Brasil Chad! Hope you´re well and still seeing the comments after one year hahaha. I d like to know if I can use the same with an Exel table?

    • @cvkealey
      @cvkealey  2 หลายเดือนก่อน

      Yes, it's possible, but I don't have a video on it. Generally speaking, I really like Excel as a tool for recording and viewing data, but for making it actionable, SharePoint/Microsoft Lists is a better choice. The tooling (connectors and actions) in Power Automate for Lists make the process much easier. To do the same with an Excel file, you need to ensure it's in a table, then get the file, get the table, list the rows, etc. There's more overhead involved. BUT, if you're committed to keeping the data in Excel, this video should show you what you need to know: th-cam.com/video/1g7NA5hYYSo/w-d-xo.htmlsi=8KGJdNAmvZnv9ZpL

  • @belizish2004
    @belizish2004 11 หลายเดือนก่อน

    Still new to Power Automate. Was hoping to use this tutorial for a similar need. Looking for guidance on setting up quarterly email to go out on specific dates 4/15, 7/15, 10/15 to users in email distribution list. Message subject and due dates will change quarterly. Recipients are not avid Teams users and have requested quarterly reminder emails. I have created a SharePoint list with Title as the Quarter, EmailGroup, and Date (when email will be sent). TY

    • @cvkealey
      @cvkealey  11 หลายเดือนก่อน

      The easiest/most reliable way to do this would be a scheduled flow that runs once a day and checks that list of any items where the Date is the current date. The only slightly complicated part is the expression for getting the current date. Assuming your date column is called "Date", this filter will work:
      Date eq 'formatDateTime(utcNow(),'yyyy-MM-dd')'
      The "formatDateTime" has to be added as an expression.

    • @cvkealey
      @cvkealey  11 หลายเดือนก่อน

      This video is going to get you closer to what you want to do: th-cam.com/video/4uO1wedT73I/w-d-xo.html

  • @pro-gradetech9155
    @pro-gradetech9155 6 หลายเดือนก่อน

    Thanks for the video! I was wondering what if the User column was just a list of emails? I need to send a reminder email to new hires 7 days after their hiring date according to hiring date column only once. How to do that?

    • @cvkealey
      @cvkealey  6 หลายเดือนก่อน

      In this example, the User column - specifically the "Email" property of it - is used to get the manager's email. If your "User" column contains simply the text of their email, then you would just use that to address the message to them. Your case is simpler, so you don't need the array variable shown here, you just need to apply an odata filter to the "Get items" action. The format of the filter would be:
      columnName eq 'addDays(utcNow(),-7,'yyyy-MM-dd')'
      The part within the single quotes needs to be entered through the expression/formula editor. Also, your date column needs to be JUST a date value, no time associated. SharePoint date columns store values in the yyyy-MM-dd format, which is why you need to set that format as a parameter in the addDays expression.

  • @OscarAlvarezoscalv
    @OscarAlvarezoscalv 7 หลายเดือนก่อน

    Hei, would not it simplify things if you created a calculated column in the sharepoint list with the +6 months and build the flow based on that calculated column? how would you build the formula then?

    • @cvkealey
      @cvkealey  7 หลายเดือนก่อน

      It depends on the requirements, specifically whether the time span is subject to change. If it is, then a calculated column would make it easier to modify later (easier to edit a formula than change the flow). And I'm not sure which formula you mean? The one for the calculated column or the expression in the flow?

    • @OscarAlvarezoscalv
      @OscarAlvarezoscalv 7 หลายเดือนก่อน

      ⁠the expression in the flow. MS Lists already has a built in reminder in the automate button but is limited to set days prior to a due date; however it does not work on calculated date columns; I need a reminder that triggers 6 months from item creation but I am not able to tweak the flow to work; I would assume your formulas to be simpler on a calculated existing column.

    • @cvkealey
      @cvkealey  7 หลายเดือนก่อน

      @OscarAlvarezoscalv you should be able to tweak the built in reminder flow to do this. Set it up with any date column you have, then in the Get items action, replace the column name in the odata filter. You'll need to be sure that your calculated column is configured to output a date value.

    • @cvkealey
      @cvkealey  7 หลายเดือนก่อน

      Actually, I'm wrong. It's not possible to use a calculated column in an odata filter query. However, if your requirement is to send based on # of days after the item is created, you can edit the flow to replace whatever column you select when you create the flow with "Created".

    • @OscarAlvarezoscalv
      @OscarAlvarezoscalv 7 หลายเดือนก่อน

      @@cvkealey I see. Would that be the same flow you built? or something else?

  • @milinkhabya8309
    @milinkhabya8309 5 หลายเดือนก่อน

    Hi Chad in the filter array part I am getting option of body/value instead of body which is causing issue in further steps can you please help?

    • @cvkealey
      @cvkealey  5 หลายเดือนก่อน

      The terminology changed a bit from the "old" designer to the "new" one. The input for the filter array action should be the "body/value" (which would be just "value" in the old designer). Or, switch the interface to use the old designer and things will look like they do in the video.

  • @trialnterror
    @trialnterror 10 หลายเดือนก่อน

    I would like to create a list of items and within them items each one rescinds at 120days out. Do you have a video that does that

    • @cvkealey
      @cvkealey  10 หลายเดือนก่อน

      I'm not sure what you mean by "rescinds"? That being said, I doubt I have a video for that specific scenario. While most of my content does use some sort of fictitious (but hopefully realistic) use case, I try to focus on the elements involved so you can adapt them to your specific use case.

  • @ST-sr5pu
    @ST-sr5pu ปีที่แล้ว

    I'm trying to do something similar with sending out email reminders to fill out a new hire survey. In my Sharepoint list I have a column with the date that the email should go out on (its different for everyone, so I need the survey to go out once it hits that date). Do you have a video specifically for this?

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      No, but the concept is the same. Have it run once a day to retrieve any items where that date column is the current date, then process the items returned in an apply to each loop.

    • @ST-sr5pu
      @ST-sr5pu ปีที่แล้ว

      @@cvkealey Sorry, I am very new to power automate so apologies, but would i still do the initialize variable? or instead use the "get items from sharepoint list" one?

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      @@ST-sr5pu No, you wouldn't need the variable. Just a filter query to retrieve items from the list where the current date appears in the date column in your list. You would need an expression to get the current date in your time zone in the format of 'yyyy-MM-dd' (to match the format that SharePoint uses for date-only columns). The expression would be "convertfromutc(utcnow(),'','yyyy-MM-dd') You can get your time zone name from learn.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-time-zones?view=windows-11

  • @loneranger143
    @loneranger143 ปีที่แล้ว

    Thanks for the video, but I'm a total beginner, and this is my very first attempt at making a power automate, so I'm having a hard time following up on your tutorial because I saw you skip a lot of basic steps. I need to figure out how to look for each command and even how to create a list but I managed to do that part.
    But I'm stuck on the "apply to each date" part. Every time I type item(), ['Date'], it doesn't change to date. It just stays as a text; can you help?

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      You need to enter that as an "Expression". See powerautomate.microsoft.com/en-us/blog/use-expressions-in-actions/

  • @mulleyd
    @mulleyd ปีที่แล้ว

    Real newbie here, what would the expression look like if we are not using EST but keeping UTC as a timezone? Thanks!

    • @cvkealey
      @cvkealey  ปีที่แล้ว +1

      If you mean the "subtractFromTime" expression, you'd just remove the "convertFromUtc..." chunk. So, to get 6 months before the current date, it'd be subtractFromTime(utcNow(),6,'Month','yyyy-MM-dd'). Replace the number and unit as needed for your scenario.

    • @mulleyd
      @mulleyd ปีที่แล้ว

      @@cvkealey brill thank you!!

    • @mulleyd
      @mulleyd ปีที่แล้ว

      Not sure if i'm doing something wrong but getting an error when testing the flow: The variable 'varDates' of type 'Array' cannot be initialized or updated with value of type 'String'. The variable 'varDates' only supports values of types 'Array'.

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      @@mulleyd It sounds like the format of your array is a bit off. See about 2:49 in the video for the proper format. Power Automate is quite picky about that. Arrays need to start and end with square brackets ( [ and ] ) and each element within needs to be within curly braces ( { and } ) and separated by commas. Also, keys and (string) values need to be within double quotes ( " ) - integer values don't need quotes.

  • @natalieoneal3752
    @natalieoneal3752 ปีที่แล้ว

    Hi, Im wondering if you could help me out. This is for es tax reminders
    Im trying to send out emails on a date in an excel spreadsheet. The email will contain a value in the same excel spreadsheet. The title of the columns are the dates and the value is in that column based on each client.

    • @cvkealey
      @cvkealey  ปีที่แล้ว +1

      It sounds like the structure of your data is going to make this very complicated. Reading date values from Excel is tricky (due to the fact that Excel stores dates as numbers), but if the dates are table headers, that makes it even worse. I know you won't want to hear this, but I'd suggest restructuring it so that each reminder is a unique row in a SharePoint/Microsoft list. The value you want to include could be the Title column and the date to send would be a date column (e.g.: Send date). Then you would just need to filter the list based on that Send date column to retrieve those that need to be sent. I mean, you could also do it in Excel - as long as you rearrange it so that each reminder is on a separate row. Granted, there are lots of different ways you could do this, I'm just telling how I would do - not that it's the "best" way overall, just the best way I can think of to do it.

  • @slynchnuovo
    @slynchnuovo ปีที่แล้ว

    I have a list in SharePoint structured similar to how you have your list in this video. Mine is for tracking fleet vehicles. I'm trying to set up an automation where the employee assigned to the vehicle will get an automated email 2 months and 1 month prior to the Renewal date. I've been unable to fin any video setting up something like this. Can you help?

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      The example in the video can be adapted to your scenario. Basically, what you want to do is have the flow run daily, retrieve vehicles where the renew date is at the specified interval, then cycle through the list that's returned. I would build it out for the one month scenario, put all those steps into a scope, then copy that and paste it into a parallel branch and modify it for two months.

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      See this video for another method that's a bit less complicated and should be a little easier to adapt to your scenario: th-cam.com/video/4uO1wedT73I/w-d-xo.html

    • @slynchnuovo
      @slynchnuovo ปีที่แล้ว

      @@cvkealey I've tried with the link you supplied and I'm still getting Errors. Its not recognizing the Registration Renewal Date Column under "Get Items"

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      @@slynchnuovo You need to use the internal name of the column in the Odata filter. There are several ways to find that, but the simplest is to go to the list settings, click on that column (in the Columns section), then look in the URL. At the very end, you'll see an = with the internal column name after it.

  • @IgoCaNo831
    @IgoCaNo831 ปีที่แล้ว

    Hi, I am getting an error: 'Get_manager_(V2)' inputs at line '0' and column '0': 'The template language expression 'item()?['Contact']?['Email']' cannot be evaluated because property 'Email' cannot be selected. Array elements can only be selected using an integer index.

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      It sounds like your "Contact" column is a person column allowing multiple selections. That means the data is always in the form of an array (even when just one person is selected).

  • @aniaimichax2150
    @aniaimichax2150 6 หลายเดือนก่อน

    can you show how to create a daily notification with HTML table showing documents name, modification date and link to the document that has been added or modified in SharePoint?

    • @cvkealey
      @cvkealey  6 หลายเดือนก่อน

      While this is possible, and not terribly difficult, I'd suggest taking a look at "alerts" in SharePoint (support.microsoft.com/en-us/office/create-an-alert-to-get-notified-when-a-file-or-folder-changes-in-sharepoint-e5a79e7b-a146-46da-a9ef-d65409ba8918). They are very quick and easy to set up and may provide the information you want with very little effort.
      If the alert method doesn't meet your needs, you would need to create a recurring flow that runs daily and use a "Get files (properties only)" actions that retrieves all files where "Modified" is greater than or equal to the current date and time minus 1 day (Modified ge addDays(utcNow(),-1) - this must be added as an expression). Then, use a select action to retrieve the columns you want from the output of the "Get files" action and a "Create HTML table" action to format that as a table.

    • @aniaimichax2150
      @aniaimichax2150 6 หลายเดือนก่อน

      @@cvkealey unfortunately the format os SharePoint alert is not user friendly it lists every line the document was amended in while I just want to know that it has been amended otherwise the notification is pages long listing everything

    • @cvkealey
      @cvkealey  6 หลายเดือนก่อน

      @@aniaimichax2150 Then the second part of my reply lays out a pattern you can follow to create the flow you need. The tricky part is writing the expressions for the odata filter in the Get items action. There are lots of tutorials and blog posts out there for guidance. This is good one: citizendevelopmentacademy.com/blog/power-automate-filter-query/#:~:text=How%20to%20Use%20Filter%20Query%20in%20Get%20Items%20Action%20in%20Power%20Automate

  • @azizbahaduri
    @azizbahaduri ปีที่แล้ว

    Appreciate sharing this video.
    Using the same scenario, when I try to add a "Link to item" inside the body in order to send an email with a link to the Sharepoint item in the list, it creates an additional "Apply to each" where I receive duplicate emails for each item. How could I avoid this. Thanks in advance.

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      The short answer is that the dynamic value you're using is an array, so the Apply to each is being added to iterate through each value in the array. To give a more precise answer, I'd need to see how your flow is configured. I'd suggest posting a message in the Power Automate community (powerusers.microsoft.com/t5/Building-Flows/bd-p/BuildingFlows) with a clear description and screenshots of how it's set up. You can tag me there (@ChadVKealey) and I'll take a look. There are lots of other folks patrolling those forums, so they may beat me to the answer.

    • @azizbahaduri
      @azizbahaduri ปีที่แล้ว

      @@cvkealey Thanks for your reply, I finally found a solution to avoid adding an additional loop inside existing loop by writing directly the LINK expression "@{items('Apply_to_each')?['{Link}']}" in Body. Thanks indeed.

  • @MattyauRoss
    @MattyauRoss 3 หลายเดือนก่อน

    Can you do the same thing using Microsoft lists??

    • @cvkealey
      @cvkealey  3 หลายเดือนก่อน

      Yes, "Microsoft Lists" are "SharePoint Lists". The only catch is that if it's a personal list (it lives in "My Lists"), the SharePoint actions in Power Automate won't "see" it. Your personal lists live in your personal SharePoint site (where your OneDrive files are stored). The site URL you need is drexel0-my.sharepoint.com/personal/username_domain_ext/. (replace "username_domain_ext" with the email address you use to sign into M365, replacing the @ and . with underscores).

    • @cvkealey
      @cvkealey  3 หลายเดือนก่อน

      See this video: th-cam.com/video/VSx9Ho1m4pU/w-d-xo.html

  • @shubhabratadey
    @shubhabratadey 2 หลายเดือนก่อน

    What if I want the Manager to get 1 single email for all of his employees instead of separate mails?

    • @cvkealey
      @cvkealey  2 หลายเดือนก่อน

      This is possible, but can get a little complicated. Basically, you need to get a list of all of the employees to which an email should be sent, then a list of unique managers, then you can filter the list of all emails to get a list per manager. Finally, you can create an html table using that list of emails to include in the email to the manager. Really, it's a lot of shaping and filtering of array data.

  • @JunedAhmed-rs2hb
    @JunedAhmed-rs2hb ปีที่แล้ว

    Hello, I have created a scheduled flow that runs monthly. I have two excel file in SharePoint, and I created the flow that will pull data from one excel file (let's say Excel 1) and paste the data to ther excel sheet (Excel 2). So, my flow is : Recurrence>List rows in a Table> Add a row in a Table. So for the very first month (January), it runs well, however when my flow runs for February, it also pulls January data again along with February data. How can I stop getting the data that is already pulled by the previous run? Please help me out.

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      If there isn't already a column in Excel 1 that you can use to get only the February entries, you could add a new column (e.g.: "Copied" or "Done") and update that after writing the row to Excel 2.

  • @sohadaneen7639
    @sohadaneen7639 10 หลายเดือนก่อน

    Hi Chad, Nice Vedio, I have an requirement like , I have sent Initial mail and after needs to send update on top of it initial mail, how can we achieve this? My source data is Share point excel sheet where I have values like Initial ,Update, Skip, Resolution where ever I changed the status I need to send an email on top of it Initial mail only.

    • @cvkealey
      @cvkealey  10 หลายเดือนก่อน

      It *might* be possible to do this with Excel as the data source, but it'd be easier with SharePoint. Basically, you'd have one choice column with those values and then build a flow that ran when that column value changed. See powerautomate.microsoft.com/en-us/blog/run-a-flow-when-a-sharepoint-column-is-modified/ for details on that. Within the flow, I could create a Switch with a Case for each value.

  • @rebeccamayer3602
    @rebeccamayer3602 8 หลายเดือนก่อน

    Great video! I need my power automate to count the number of rows that have a specific date inputted and then email the count of those daily… however when I try and use ‘length()’ function to count my power automate automatically says “length(outputs(‘filter_array’)[‘body/value’]) then when I run this I get the error saying ‘[‘body/value’] doesn’t exist?! Please can someone help me?

    • @cvkealey
      @cvkealey  8 หลายเดือนก่อน

      Change ['body/value'] to ['body']. In the new designer, the dynamic content output of a filter array action only lets you select "Body (list of items)", which is ['body/value']. The classic designer shows just "Body". I'm not 100% sure what the difference is between "Body (list of items)" and "Value (list of items)", but I know that using just ['body'] will get you a count of items returned from the filter array action.

    • @cvkealey
      @cvkealey  8 หลายเดือนก่อน

      This page (powerusers.microsoft.com/t5/Building-Flows/What-is-difference-between-value-and-body-from-Get-Items/td-p/666706) contains a bit more info on the "body vs body/value" thing, but it's not super helpful. I think in this case, it's a bug in the new designer that's grabbing/showing the wrong dynamic content from the filter array action. There are more than a few bugs and I honestly just switch back to the old designer a LOT. Not that it doesn't have some bugs & quirks, but it's "the devil I know".

  • @pablobaculinao862
    @pablobaculinao862 ปีที่แล้ว

    How to include img on this type of flow?

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      It depends. Static image? Dynamic image? In either case, you'd need to add appropriate HTML markup to display the image in the email. If it's a static image, this is pretty simple, assuming the image is hosted somewhere that's accessible to the recipient(s). If it's dynamic, then it's a little more complicated because you need to incorporate a means of getting the image or link to include in the HTML. So, yes, it's possible, but there are a number of variables involved, so there's no simple "this is how you do it" answer.

  • @alejandrogomez4129
    @alejandrogomez4129 ปีที่แล้ว

    Se escucha bien, solo que al inicio se ve muy borroso. Solo los ultimos 2 minutos se ve clara la imagen :(

    • @cvkealey
      @cvkealey  ปีที่แล้ว

      The image quality looks fine to me, but it might be you're viewing it in a lower resolution. Click the gear icon in the lower right corner and then click Quality. Be sure you're viewing it at 720p or 1080p. It's actually recorded at a resolution right in between those two (1600x900).