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
- First I created a Custom List in SP2010
- In the Custom List I created a Calculated Column.
- 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
- 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
6. Then I checked the workflow for errors, saved it and published it
My SharePoint Chronicles 
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.
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.
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
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 >
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.
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 >
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)
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 >
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
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 >
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.
Hello Melo, did your solution works? i want send e-mails before plannified tasks but i don’t know how to do.
i just started working on Sharepoint designer but here is a good link….the formula whatever you use you have to close all the parentheses at the end….
Thank you this formula helped me tremendously. Thanks again. I will now favorite this blog and point other colleagues here for help, if needed.
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!!
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.
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!
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.
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
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?
Yes Rebecca you are correct, I was using a Date/Time Column instead of a Calculated Column.
-Thankyou
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
Hi Moses, check out my post here https://mysharepointchronicles.wordpress.com/2014/02/07/step-by-step-workflow-to-notify-document-author-of-document-expiration-date/. Let me know if you need any further explanation.
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
Hi Suzie, this is possible. Tell me what version of SP and SharePoint Designer you are using? I will create a post on how to do this…
Hi – I am using SP2010 and a custom list. Thanks…
Suzie, are you using a SharePoint Survey list. Or a Custom list?
Hi Suzie,
Here is the post. https://mysharepointchronicles.wordpress.com/2014/05/15/create-a-sharepoint-designer-workflow-based-on-two-sets-of-calculated-results/
Can you answer SuZie’s question using a custom list?
Hi – please see my post on this request here https://mysharepointchronicles.wordpress.com/2014/05/15/create-a-sharepoint-designer-workflow-based-on-two-sets-of-calculated-results/
There may be an easier way to create the calculations using a SharePoint calculated column, instead of the variables in the SPD workflow, but I wasn’t able to find a formula that worked. This solution works, although it may be a bit longer to create.
I cannot find “Today” as a selection for value, how do i find “Today” as an option?
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.
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.
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.
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.
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.
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?
Hi Billy,
Could you first confirm that you have the same exact fields as the outcome for your workflow, as shown in my screen shots above? Second, please confirm the SP version you are using and the SPD version?
Also, can you confirm that you created a Calculated Column field and not a Look-up field by accident? This type of error is associated with SP Look-up columns, not Calculated Columns.
Also here is a blog regarding this type of error. http://www.sharepointed.com/2012/06/01/coercion-failed-unable-to-transform-the-input-lookup-data-into-the-requested-type/
Hi Rebecca,
Thanks for this great post. I too am getting the coercion error and I don’t have any lookup columns in my library or workflow. Is the pause until reminder date action considered a lookup? Here’s some info about my library and workflow:
My First Reminder Date is a calculated column with the formula=[ End of Term] -2 where End of Term is a date and time field, formatted as date.
For this particular document the end of term was set to 7/12/15 so the first reminder date was calculated correctly to 7/10/15, yet here are the cancelled workflow details:
7/10/2015 4:07 PM Comment Pausing until 12/28/1899 12:00:00 AM <workflow action: Pause until Current Item:First Reminder Date >
7/10/2015 4:10 PM Comment Pausing complete
7/10/2015 4:10 PM Comment workflow paused until first reminder date
7/10/2015 4:10 PM Error Coercion Failed: Unable to transform the input lookup data into the requested type.
Have you run into this before?
Hi Monica,
I found this statement “Coercion Failed: Unable to transform the input lookup data into the requested type
The operation failed to cast values between incompatible data types (for example, converting a random string to a Date/Time value). You should check the Return Field As settings in your lookup to ensure that it is a valid data type for the expected data.” from this site https://msdn.microsoft.com/en-us/library/office/dn449112.aspx#bkmk_error11.
Check the Return Field AS setting in your workflow.
Hi Rebecca,
I have a problem sending email from list.
I have followed exactly the same conditions and actions as you posted in my workflow. I have transferred my Risk details list to SharePoint from Access database which has a review date.
The requirement is to send email when the Risk Review date is due.
I have created a column named reminder date as ( Review Date -1) and write the conditions as
If current Item: Reminder Date equals Today
Email Current Item: Created By;
I think it works fine, but it I am not receiving email even though the workflow status shows it is completed.
How to fix this. Please advise.
Thanks
Raji
Hi Raji,
Thanks for visiting my blog. Can you tell me a couple of things? 1) What version SP and SharePoint Designer are you using? 2) What is the Initiation setting on the Workflow? (Step #4) Can you validate you have it set to run when the item is created? If you have, did you create the List with existing data in it? Meaning the data was created before the workflow was created? If so we need another set of logic to achieve. Can you test by adding a new item to the list?
Thanks for your quick response Rebecca.
I have created one more column with email address and changed the workflow as follows.
Pause until Current Item: Risk Review Date
then email Current Item: Risk Owner Name
It is working fine now.
Great! Glad it’s working.
Hi Rebecca,
Please help – this workflow will not work if I change the start date, because pausing is working only for initial start date, any changes do not restart ‘pausing’ action. Would You have any solution for that?
Thank You
Hi lukasz – I don’t understand what you mean by the “start” date. Do you mean the Created Date or the Reminder Date?
i have two questions:
1. i dont create field [reminder email sent], is it a problem? so i only stop at “Action: Email (To: User who created item)”.
2. at what time the email will sent? cos i already follow ur step (without log reminder email sent). i suppose to receive the email today but it doesnt appear.
FYI i use SP 2010 and SPD 2010
If you aren’t using a Reminder Date (which would be in the future) – and just want to send an email to the user who created the item – you don’t need to have the Pause in the workflow.
If you want it to send an email once to the user when the item is created, just use the Action: Email (To: User who created item).
This will only send once if you select send on item creation.
Following your instructions, I get a “Failed to Start” message in the List’s workflow column and when I click the text, is shows two items under Workflow History in the Event Type and Description columns: “Workflow Cancelled”, “Workflow was cancelled by System Account” and “Error”, “Workflow failed to start”
What version SP and SPD are you on? Could you send a screen shot of the Workflow History showing these errors?
SP 2010 (farm) service pack 2, SPD 2010 … don’t see a way to attach a screenshot (or site is intranet only)
Check out this article for your errors.
http://blogs.technet.com/b/sharepointdevelopersupport/archive/2015/03/11/failed-on-start-in-spd-workflow.aspx
Ok… .I am not able to see the individual files in the workflow as detailed in section 1 of the link you provided (I can only right click the workflow name as see “Workflow Setting”, “Edit Workflow” and “Delete”.. Section 2 does not apply as this is a 2010 site.
First – are any of the file checked out in the library?
I’m not able to see individual (i.e.: .xoml files) as seen in the Microsoft site’s screenshot). If I click the name of the workflow in the left pane, I don’t see them and if I right click I get the menu mentioned previously.
HOld one second Rebecca….. I’m getting my farm administrator to look at this as now I do see two files checked out but am getting a ‘Cannot perform this operation” message when I try to check in the.xoml and .xsn files.
OK. I have resolved the checked out files issue. To clarify my specific workflow, I am following your instructions except that I want to send an email to the person who creates the list item 14 days before the value in the Start Date field in the list so the calculated column formula is =[StartDate]-14.
Now, when I create a new list item, I get an email immediately, rather than 14 days prior to the Start Date.
Are you still using the “Pause” in the SPD workflow?
It’s working now. THank you!
If we start a workflow on item creation so it would make lots of instances. As an example in my scenario I have 4 approval levels and on each approval level if a manager haven’t have approved the item then after a week the email needed to be triggered. So, in this particular case the workflow instances would be created unnecessarily.
I’m using s pause and a condition to keep it from having an infinite loop.
Pause is very heave logic, any other approach?
I need to set up the workflow to run every Friday at 0900 hours. How would I go about doing this?
Hi Michaele,
The first thing I would try is to create a new Calculated Column (call it Day of Week) in your SP list using this formula below to try to extract the day of the week from the date. Use Created Date below where it says [Column 1].
=TEXT(WEEKDAY([Column1]), “dddd”)
Once you have successfully captured the day of the week, make your SPD workflow logic do the following:
Pause until Day of Week = Friday and Date and Time equals 9:00 AM Today.
Then have the workflow run on those conditions listed above.
Let me know if it works out. You may also have to create a Variable to make this work….
I work for an organization that does not have SharePoint Developer. Is it possible to create a workflow like this without Developer??
If you have O365 SharePoint you might have access to Flow. It’s a new light weight workflow designer from Microsoft. The functionality is more limited but it might work.
We aren’t using O365 on our desktop computers but the company will allow us access to the Sharepoint list from our laptops. Once I created the email reminders with Flow and published it, would it then operate according to the flow even though we wouldn’t have access to O365.
You’d have to first make sure you have access to Flow. If you can create this workflow it should function on either type of computer. As long as the SP list is on O365.
Ok. I will give it a try. Thanks!!!
Thank you for the post it is helpfull.
If there is 3 items with same due date , can we get all the three items details in one single email.?
Hello! Thank you for your post, its help me a lot to develop a workflow, but for some reason, It doesn’t email the review date log to my email, I just recreate your workflow but maybe I miss a step, can you help me on this matter? thank you
Hi Lina,
I’m not sure I am understanding the problem. This workflow is to send an email notification reminder 30 days from the date (created date) a document was uploaded to the Document Library. What do you mean by the “review date log”?
My mistake sorry for that! Thank you for your reply
Hello! I have a question… In the first If you select this conditions: 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, I’m using SP2013 and in my workflow between the data source and the association I have to select begins, equal or greater than or less than, many other options, how can I skip this selection from my workflow or which one do I have to use? example (If Workflow Context:Association Name begins with Item Start on Creation = yes)
hi, i have sharepoint 2010. can i use your solution from my example. i have date field instead created date and i need reminder also for 30 days in my list?
Yes as long as the date field is filled out with a date and not left blank.