SharePoint List with Workflow Email Reminder Set to Send 30 Days from Created Date

5 Nov

Today I’m going to create a SharePoint 2010 list that has a workflow set to send an email notification.  Below are the requirements:

  • Create list in SharePoint 2010 to track a time driven process
  • Create Workflow using SharePoint Designer to send an email reminder when an item is exactly 30 days from the Created date
  1. First I created a Custom List in SP2010
  2. In the Custom List I created a Calculated Column.
  3. In the Calculated Column (which I named Reminder Date) I used a formula pulling from the Created (date) column. I used the following formula to populate the Reminder Date field based on the Created date field.  =Created+30                                                                   

Click to see Calculated Column screen shot

4. In workflow settings in SPD2010 I checked the box: Start workflow automatically when item is created

5. Then I created a Workflow to send the email reminder 30 days after the Created date using the following logic:

  • Condition: If  any value equals value 
    • For the first value select the fx and then for data source field select Workflow Context
    • Then for field from source select the Association: Item Start on Creation

Click to see Workflow Context

Workflow Context

  • Action: Pause until Date  (Current item: Reminder Date field)
  • Condition: If current item field  (Current item: Reminder Date) equals (is greater than or equals) value (Today) 
  • Action: Email  (To: User who created item)
  • Action: Then log (reminder email sent) to the workflow history list 

Click to see the example workflow

WF

6. Then I checked the workflow for errors, saved it and published it

38 Responses to “SharePoint List with Workflow Email Reminder Set to Send 30 Days from Created Date”

  1. Melo July 6, 2013 at 10:51 AM #

    Hello taking a different approach. please help with formula and step process to set a workflow reminder to send the reminders below:

    i am working on a project with a lot of reports, some are due on Daily, weekly, monthly, quarterly, annually basis. they are all different type of reports and assign to one group of guys.

    I have started by adding three columns: 1) a yes/no column – add reminder 2) an options columns with choice: 4hrs, 1day, 2days, 1week – Reminder Time 3) a calculated column which would calculate Time&Date reminder should go out – Reminder Date

    but my calculated formula below for the reminder date column is not working or allowing me to save it:

    =IF([Add Reminder]=”Yes”,IF([Reminder time]=”4hours before”, [Start Time]-(4/24), IF([Reminder time]=”1day before”, [Start Time]-1, IF([Reminder time]=”2days before”, [Start Time]-2, IF([Reminder time]=”1week before”, [Start Time]-7))

    The point of this formula is to send a reminder out base on what choice the event organizer choose from the reminder time.
    Any help with this formula and finishing the process would be greatly appreciated.

  2. rebeccaettlinger July 6, 2013 at 11:53 AM #

    I am happy to try to help. I need a bit more information. In my example, I used SharePoint Designer 2010 to create a workflow that would send the email notification based on the Reminder Date (SharePoint Calculated Column).

    Can you create the reminders based on the Created Date? If so – I would recommend that you create a Calculated Column for each Reminder Date. Then you can set a SharePoint Designer Workflow to send an email notification out based on that column.

    If this works, using the Created Date (of the list item or document) I will be happy to show you the formulas that might work.

  3. Melo July 6, 2013 at 12:15 PM #

    I cannot do it off the create date. i need the reminder to be sent out before due/end dates: 4hrs before, 1day before, 2days before, 7days before.

    I know i need a calculate reminder column but i dont know the formula to enter in that coumn

    • rebeccaettlinger July 6, 2013 at 12:23 PM #

      So do you know the end dates? For example, if one is due monthly – is it always due on a certain date or day? Like 1st of the month? You could create a Calculated Column based on the due date, then set a workflow that sends the notification 4 hours before, 1 day before, etc.

      > Date: Sat, 6 Jul 2013 16:15:54 +0000 > To: rettlinger@live.com >

  4. Melo July 6, 2013 at 12:57 PM #

    i know the due dates and they are recurrent. so i just need help wirting the formula for the calculated column. i know how to set the workflow already.

    • rebeccaettlinger July 6, 2013 at 1:02 PM #

      Please give me a specific recurring due date as an example and the requirements – I will send it back to you with my approach.

      > Date: Sat, 6 Jul 2013 16:57:39 +0000 > To: rettlinger@live.com >

  5. Melo July 6, 2013 at 1:07 PM #

    the daily report reminder goes everyday 4hrs before 8am(duedate), weekly reports reminder goes everyweek 1day before duedate, monthly reminder goes out everymonth 5&2days before duedate, quarterly go quarterly 7&2days before duedate(same for annual report go once a year 7&2days before due date)

    • rebeccaettlinger July 6, 2013 at 1:24 PM #

      I would do this: create a SharePoint Column called “Due Date” (Date Only Column) that has the due date populated by the user who uploads the item or documentcreate a SharePoint Column called Report Type (Choice Column) that the user selects for the type of report, ie: Weekly, Monthly, Quarterly, Annuallycreate a SharePoint Designer Workflow that is set to Pause until XX days/hours based on the Due Date column with a Condition that is based on the Report TypeFor example:

      IF Report Type = Weekly Pause for XX Hours Before Due Date

      I would not try to create a Calculated Column to calculate the Due Date for these reports, I would use a Workflow to Pause.

      Then create a SharePoint Column choice that

      > Date: Sat, 6 Jul 2013 17:07:33 +0000 > To: rettlinger@live.com >

  6. Melo July 6, 2013 at 1:48 PM #

    thanks much for the help i really appreciate you taking the time to help with this. but i have a feeling doing it like that would not sent recurrent reminder. it would only send the reminder once. and there are some reports that are monthly and weekly and quarterly. i fully like the idea of a due date column, report type column, (reminder column)…i will still need a formula in the remider column to calculate when to send the reminder…if reporttype=weekly, starttime -7 (and so on)…and there will be more than one if statement in there, ijust wnat to know how to put that statement together. please

    • rebeccaettlinger July 6, 2013 at 2:51 PM #

      No problem, I think I must not fully understand your requirements. I had assumed you would be adding an item or a document to a list or library, so the reminder would be set for an email notification in the Workflow on creation. Is the report going to be uploaded and then a reminder needs to be sent? What kind of event will happen in the list to trigger the event? I’m not sure a complicated IF formula will work in a Calculated Column – but you could try something simple, like: =Due Date – 7 days then let the workflow use the Report Type such as Weekly, Monthly… as a Condition for sending the email before it looks as the Due Date Sorry I couldn’t help…if you want to expand a bit on how you are using this List/Library, I am sure there is a solution. I.E. – is a user uploading a document/report to a library? Or are you trying to create a list with these columns so you can then run these rules based on the report type?

      > Date: Sat, 6 Jul 2013 17:48:50 +0000 > To: rettlinger@live.com >

  7. Melo July 7, 2013 at 11:56 PM #

    Thank you very very much for your help your ideas helped me with this project. basically i will add all my reports in the calendar. and i want sharepoint to send the reminder everytime the report is due based on the reminder i set.

    • SOLAIRE July 10, 2013 at 4:56 AM #

      Hello Melo, did your solution works? i want send e-mails before plannified tasks but i don’t know how to do.

  8. Stephen July 18, 2013 at 11:00 AM #

    Thank you this formula helped me tremendously. Thanks again. I will now favorite this blog and point other colleagues here for help, if needed.

  9. Rubes August 22, 2013 at 11:22 AM #

    Hi! This post is really helpful as I’m trying to do the exact same thing with SharePoint. I just have question, in the first Condition on Step 5 “Condition: If value (Workflow Context: Association Start on Item Creation) equals value (Yes)” how do I set the value to “Yes?
    Also, when you list the second condition, I’m assuming that’s Step 2 in the workflow? Or is it an “Else If Conditional Branch”?

    Thanks so much for the help!!

    • rebeccaettlinger August 23, 2013 at 9:36 AM #

      Hi – I’m glad the post is helpful. So, if you select the Condition “If any value equals value”, then you change the first value data source to Workflow Context in the drop down field, and then change the Field From source to Association: Item Start on Creation – the second value in the condition will populate a Yes/No drop down to select from….

      I will update the post to show a screen shot.

      Let me know if you have any additional questions.

      • Rubes August 23, 2013 at 10:42 AM #

        Thanks for the quick reply! So I’m working with SP 2007 and it seems that some of the workflow lookup options are different than what you have. I was able to create a workflow using the following logic, do you think this would work?:
        Step 1
        Condition: If [ListName:Created] is less than [ListName: ReminderDate]
        Action: Pause until [ListName:ReminderDate]

        Step 2
        Condition: If [ListName:ReminderDate] is greater than or equal to [Today]
        Action: Email [ListName: AssignedPOC] then Log [reminder email sent] to the workflow history list.

        Thanks!

      • rebeccaettlinger August 26, 2013 at 1:38 PM #

        I would suggest creating a Calculated SharePoint column first using the formula I listed in the post, to determine the Reminder Date. Then just set the Action to Pause until [Reminder Date]. That way, you know the email is sent on the exact reminder date. Unless of course, the user is entering the Reminder Date, instead of it being a set number of days after the created date.

  10. PJ September 10, 2013 at 2:31 AM #

    Hello,
    Great article. I would like to know how to get the “insert column” (and add to formula) to show up on the formula form/page.
    When I go to the “change site column” page the “insert column” does not show up at all
    I am using SP 2010.
    Also the “formula” entry entry section shows up under the expandable section “column vailidation”
    -PJ

    • rebeccaettlinger September 10, 2013 at 12:30 PM #

      Hi PJ,

      First I want to make sure I am understanding your question. So let me ask – when you create a new Column are you selecting a Calculated Column as the type?

      • PJ September 17, 2013 at 12:40 AM #

        Yes Rebecca you are correct, I was using a Date/Time Column instead of a Calculated Column.
        -Thankyou

  11. Moses February 7, 2014 at 7:21 AM #

    Hi Rebecca can you give dummies step by step guide on how to add a workflow please?

    Basically i am trying to send an email notification to the author of a policy document 30 days before the policy expiry date.

    I already have these fields in the list as mandatory fields..
    “Expiry date” and “Author” (which is username from Active Directory)

    I will really appreciate if you can help me on this… with a step by step approach on what i do on Sharepoint Designer 2010 and how i active this work flow to work?

    I have looked at various website, but they go into more complex workflows.. i just want something simple as that explained in a very very basic way as i am very new to Sharepoint and Designer.

    Kindest Regards

    Moses

  12. Suzie February 7, 2014 at 11:48 AM #

    Hi,

    I have been asked to create a workflow that runs based on 2 sets of results within a list.

    1.If a user answers YES to any 4 out of 6 questions in the list, an email is sent to mailbox A
    2.If a user answers YES to 2 or less of the 6 questions, an email is sent to mailbox B

    The question columns are all Yes/No checkboxes. Is this possible and if so, how would I do this.

    Thanks,

    Suzie

  13. Andrea Vasquez May 15, 2014 at 12:48 PM #

    Can you answer SuZie’s question using a custom list?

  14. jamie June 17, 2014 at 8:11 AM #

    I cannot find “Today” as a selection for value, how do i find “Today” as an option?

    • rebeccaettlinger June 17, 2014 at 11:17 AM #

      Hi Jamie,

      I’m sorry, you should look for “Current Date”. So if you select the condition (in SP 2010 workflow) “If current item field equals value” then select “Reminder Date” for the “field” hyperlink and then click “value”
      and select the ellipses (…) symbol it will show you the date value dialogue box. Then select Current Date. However, you must have the Reminder Date set in the SP list as a Date & Time field with only “Date” type – in order to see this Date dialogue box.

      • Em January 5, 2015 at 3:13 PM #

        Is this working for the existing items in the library or just for the new ones???? Please advise
        I never worked with association workflow… great . will look into that more. I did my work flow after yours, have multiple notices and created calculated column for each. then I included them in the same workflow NOTIFICATIONS but with 4 of your workflows depending on the case.

      • rebeccaettlinger January 6, 2015 at 7:00 PM #

        Hi yes this does work on all of the documents in the library if you have an automatic workflow which says that it’s going to be associated on a change. However you have to be careful with this type of association because when anything changes the workflow will run there are ways around that if you need help let me know your specific requirements and I’m happy to guide you.

      • muni March 11, 2015 at 3:27 AM #

        Hi,

        I have a requirement similar to your post. A reminder mail has to be sent to users on friday 11am to send the weekly status report. On saturday 10 am another email has to be sent to another set of users to check the weekly status report in the site. Please help me.

        Thanks in advance.

      • rebeccaettlinger June 29, 2015 at 12:01 PM #

        Hi Muni,

        For this case, I would recommend using a SP Calendar (list type) instead of a Custom List. In the Calender, set a recurring reminder on Fridays and Saturdays. Create a workflow that is only triggered when the Title Field of the reoccurring date/time = Today in the workflow. So for the Friday reminder, when you build the email workflow, you will select the Title field from the Calender (ie: Friday Reminder) and then add the set of email addresses it should go to. Then repeat and build a workflow for the Sat. reminder. If you are still in need of help, please let me know which version of SP you are using and I will try to create step-by-step.

  15. Billy Cheung June 26, 2015 at 3:16 PM #

    This is a great blog! I followed your steps as outlined, but when I ran the workflow I get a Coercion Failed: Unable to transform the input lookup data into the requested type. What does that mean and how do I fix it?

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: