Email me when list item = (modified date = 180 days old)

Question

So i have a content approval list built to organize permission requests with a workflow to email me when a new list item is created as well as another to inform the creator when that list item is approved. I also have retention set up to email the creator there list item has reached 6 months.

I would like to set up 1 more workflow to email an admin that this list item has surpassed 6 months. BUT IM LOST lol

I have SharePoint 2013 and utilize SharePoint 2010 WorkFlows. Any help would be greatly appreciated!

Answer 1

With just about any other type of Workflow engine, I would suggest running a scheduled workflow that runs once a day that uses a filtered REST call to find all items that are 6 months old. Unfortunately, one of the biggest things missing from 2010 workflows is the ability to query for multiple items.

What 2010 workflows does have is the ability to "Pause". If you are looking to be alerted 6 months from the date the Item is created, then you can add these steps to your existing On Created workflow, after it emails the notifications about creating, it can initiate the "Pause" action here. If you need to base it on some other Date in the metadata that may change, then you will need to create a new workflow triggered on item creation and update. It will probably only have two steps, one to Pause for 180 days, and then the next step would sent the email notification.

To make this send the mail notification when it has been more than 180 days since the Item was modified, set this workflow to be triggered on Item Created and Item Updated, then the next step After the Pause action should check if the current date is greater than (the Item's modified date + 180 days), and if so, send the email, otherwise nothing. You will end up with multiple instances of this workflow being in progress for each item, once for every time the item is updated, but when each instance "wakes up" 180 days later, it checks again to see if it is still 180 days old before sending the email.

Note that due to the limitations of 2010 workflows not being able to simply query and loop through items, if you have any PowerShell skills, you may be better off writing a scheduled PowerShell script than implementing this complicated workflow.

Answer 2

While the workflow option obviously allows you to avoid any custom code or server-side access, a PowerShell script could actually give you a simpler, arguably more elegant solution.

You could schedule this script to run nightly using the built-in Windows Task Scheduler on your SharePoint App Server. Be sure to change the web url and list name in the code below, as well as changing the $emailSubject and $emailBody to match what you want to send:

Add-PSSnapin Microsoft.SharePoint.Powershell -ea SilentlyContinue
$web = get-spweb "http://mywebapp"
$list = $web.lists["MyUserRequestsList"]
$query = New-Object Microsoft.SharePoint.SPQuery

$query.Query = '<Where><Leq><FieldRef Name="Modified" /><Value Type="DateTime"><Today OffsetDays="-180" /></Value></Leq></Where>'

$query.ViewAttributes = "Scope='Recursive'"
$query.RowLimit = 1000

do
{
    $listItems = $list.GetItems($query)
    $query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    foreach($item in $listItems)
    {
        $emailSubject = $item["Title"] + " has not been updated in 6 month!"
        $emailBody = "You have not changed " + $file.LeafName + " since " + $item["Modified"] + ".  Please go to " + ($web.url + "/" + $list.rootfolder.url + "/dispform.aspx?id=" + $item.id) + " to update Now!"
        [Microsoft.SharePoint.Utilities.SPUtility]::SendEmail($web ,0,0,"[email protected]",$emailSubject,$emailBody)

    }
}
while ($query.ListItemCollectionPosition -ne $null)