Sending Email alerts before expire date on document (using Sharepoint Designer)

Question

I need to trigger an email alert 2 weeks before the expire date on document (document library).

 

The document library has a List/Site Column for an Expiry date.

I need to clarify the following:

 

I assume it is possible to create a CALCULATED field (Alert Date) of DATE (assigned to List/Site Column) that would

have a formula of: Expiry date - 14 days. 

 

Is it possible to trigger a workflow (created in Sharepoint Designer) AFTER the Alert + Expiry dates have been set

and ONLY if the Alert Date  = Todays Date? 

 

Is it possible to add a variable in the workflow of type DATE that MAPPED to the current item 'UPLOADED' to the

Document Library and was calculated against the number of days to be subtracted?

 

If this was possible could it be bound to the PAUSE UNTIL DATE action?

 

The Workflow would only then be triggered (placed in dehydration) once the PAUSE UNTIL DATE is true?

 

 

 

Chelsea_Blue

Answer 1

Hi Chelsea,

 

If you are using MOSS 2007, you can set an "Information Management Policy" on the list/library and one of the options is to "Enable Expiration" (go to "Settings > List Settings > Information Management Policy settings" for your list and define a new "Policy" - turn on the Expiration at the bottom of the Policy settings page and some more options will appear). 

Once you do that, you can choose which column/date in your list to monitor (the one that you calcualte the expiration date - 14 days like in your example or you can use the built in one that looks at the date created + 1 year, etc.) and then select the workflow that you have built in SPD that sends the email (it should appear in the drop down box once you have published it to that list).  The Expiration "job" will run each night (ours is set for ~10pm) and look for any lists/libraries that have the Policy set.  If there are any items in the list that are going to "expire", it will trigger your custom workflow for that item (s) and send your email.

 

Good luck,

Scott

 

Answer 2

ScottL62 wrote:

Once you do that, you can choose which column/date in your list to monitor (the one that you calcualte the expiration date - 14 days like in your example or you can use the built in one that looks at the date created + 1 year, etc.)

 

I'm trying this out, but SharePoint doesn't show my calculated column as an option in the dropdown. It only shows fixed columns like Created, Modified and any other editable date column in the list.

 

Are calculated columns not supported as expiration date? Looks like my only option is to update the expiration date in a workflow each time the item is modified.

Answer 3

Mabster,
Did you solve this problem in the end? I'm looking to do the exact same thing but having the same problem; that SP won't let me add a calculated column to the information management policy settings page.

Andrew

Answer 4

If I am understanding your question correctly, here is the solution I used.  I used the created column to make a custom column called Expires On.

Create a custom column
In the formula box use: =Created+365
Name it Expires On
Use the date and time format
I chose date only

In all of my librarys I used a disposition approval workflow, named expiration policy
Add the workflow to the library in settings
Choose Disposition Approval Workflow
Name it Expiration Policy
Task list name: Expiration Policy Task List
History name: Expiration Policy History

In settings, under views, select the following columns:
Created (to show the date the item was created)
Expiration Policy (to show that the workflow has started when an item is created)
Expires on (to show your expiration date calculated off the created column)

Upload a document to test it.
Under View all content you will see the Expiration Policy Task list. This is where items go when they are created and when you click on it you will see the option to retain or delete. 

---

B. Celuck

Answer 5

Bace,

I understand your process, but when choosing the expires on field, moss will not show the calculated field, in your instance 'Expires on'

Hoqw did you manage to get the calculated field to appear?

Answer 6

I created a custom column at the top level of the site and used it throughout the sub-sites. It is named Expires On. It calculates the expiration date based on the 'Created' column.  Use this formula: =Created+365    Set it to show in the Date and Time format; date only.  When your in a library or list, click on the settings button > default view > check mark the Expires On column. This will make the column display in the list or library. 

Answer 7

MOSS does not show Calculated column in Expire on field. how did you manage to get it appeared ? I am really curious to know how did you manage to do so.

Answer 8

I have a related problem. In Enable Expiration, the option, Start this workflow (under When the itme expires) is not clickable. So, the only thing i can do is delete the task, which is not what I want. I want to send an email. Is there some reason this radio button is not clickable?

Answer 9

Microsoft really dropped the ball on this one (yet again).  I have spent two days trying to make a calcualted value show up in the retention time period drop down with no luck.  It will only take a Date and Time data type column...which CANNOT use a field to calcualte its value.  HOW COMPLETELY USELESS IS THAT?  I cannot calcualte a value based on another column in a Date Time data type....so why bother adding it as an option?  Microsft has added features without any docuemntation that leads us in circles.... 

Answer 10

Sorry, I never checked back to see if there were repllies to this post. I'm not sure if I understand exactly where your having this issue, so let me know if I am not on the right track here.

You set up an experiation policy giving it a one year expiration date.  This is not where you select the custom column.

In the list or library, you will use the custom column to show the date that the document expires.

You make the cutomn column at the top level. You can then use it wherever you want.

The custom column uses the the formula.

As for myself, I am searching for a way to send an alert 7 days prior to that columns date; to let users know that they have a document about to expire. That is the reason I created a column. At least they know the date it will expire when they look at that column.

---

B. Celuck

Answer 11

Ok I have done this:
Use Pause Until Date then send this e-mail.

Use a calculated column to create the date of your e-mail to be sent.

[ExpireDate] - 2

Then the e-mail would send two days prior.

Answer 12

create a SPD workflow that starts upon item creation. build a variable of the type date, that stores the value of "(Expire date) minus 14 days" and name it, let's say, notification_date. then pause the workflow until the date is equal to notification_date. when the system date becomes equal to notification_date, the workflow can be set to send a custom e-mail message.

Answer 13

design4fun:

Please wait for someone else to propose your answers. That helps the moderators. Proposing your own answers, doesn't.

(Moderator)

---

FAQ sites: (SP 2010) http://wssv4faq.mindsharp.com; (v3) http://wssv3faq.mindsharp.com and (WSS 2.0) http://wssv2faq.mindsharp.com
Complete Book Lists (incl. foreign language) on each site.

Answer 14

Hi all,

I'm new to Sharepoint.  I've got a task list created would like to send an email to the task owner when it's overdue.

From the posts here it sounds like I need to use Sharepoint Designer to send an email when a task is overdue.

Not sure I understand why, since sharepoint sends an email when the task is assigned.

Anyway, how do I do this?

Each day a script must look at the date and if today's date is > due date then send email to task owner.

I have sharepoint designer, but I can find anything in the online help about setting triggers for emails and reading fields (like the task owner name).


thanks

Answer 15

Hi

I am experiencing thye same issue.  Did you get a response to this query?

Answer 16

Alexander, can you post a step by step instruction on how to do this? I am just learning designer and haven't created an alert yet. I am assuming this will be attached to the expiration policy task list. I would greatly appreciate it if you could explain how.

Thanks.

---

B. Celuck

Answer 17

Scott oh Scott,

 

I wish it were that easy. If so then we would know that the Sharepoint team at MS had really thought this through. I have tried for two days to figure work arounds (other than designing some dumb and completed set of 'wait until' work flows in SPD -- which I do not want to do) to get the calculated field into the Retention Policy drop down list. This seems to be because the type is listed and marked as type 'calculated field' and the retention policy is not smart enough to know that this calc field is a date so can/could be used. A real 'ball dropper' I would say. The other viable idea is to set a real date field as the policy monitor and subtract the 90 days from the date the poor user has entered there. But alas, the retention policy input does not support 'negative numbers' which is what is required because the interface only provides the option of '[monitored date] + [n] [Days,Months, or Years]. The Expiration and retention policy thing was such a promising OOTB idea but falls short in its versatility as far as I can see.

If anyone knows of updates to MOSS 2007 that have rectified this oversight please let me know.

 

Thanks

 

Bill

 

Answer 18

Thx Scott, it worked like a charm...

The workflow is the one that does the work... set up the workflow correctly with the necessary steps and the notifications will be sent accordingly.

Answer 19

Hi,

Alternatively you can use the "Add Time to Date" option in the workflow actions. Then choose "Pause Until Date" and "Send an Email". This is what I did:

Add -30 days to List:Expiry Date (Output to Variable:Date)
  then Pause until Variable:Date
  then Send email toOwner

With this, i'm able to receive alert 30 days before an item expires in the calendar i created that has "Expiry Date" field.

So for you, you just change the days to -14. :)

Using calculated field will not work directly in workflow. There is a delay in this and there is no direct way to work on this. If you insist, you could try the method in this site: http://www.documentmanagementworkflowinfo.com/sharepoint-video-tutorials/use-a-calculated-field-and-two-dependent-workflows-in-sharepoint-to-format-a-date.htm. This also works.

 

Answer 20

I've written up a quick and dirty solution. The solution notifies users 2 weeks before a document expires and sends them another notification on the date of expiry: http://bit.ly/t74kLu