1

Let me specify from the outset, this applies to SharePoint 2013 Foundation (thus, 2010 workflows). Additionally, i'd like to thank in advance anyone that takes the time to help me.

The Project:

I've been tasked with setting up a document library for our Vendor's quotes and a workflow that will send out emails based on when those quotes were uploaded to the library. To be specific, each vendor's - of which there are 27 - quotes expire at different times. For example, vendor A's quotes expire in 30 days, while vendor B's quotes may expire in a year. My goal is for the email to pause for 20 days in the case of vendor A, and 355 days in the case of vendor B (leaving our purchasing team 10 days of leeway to act before the quote expires). The email also should link to the quote in question, and be unique to the vendor, i.e. vendor A will get an email about a vendor A quote expiring, and link to the [%Current Item:URL Path%], while vendor B, C, etc. will have the same thing.

Unfortunately, my attempts haven't worked yet, but I'll go over what I have tried for the workflow:

I created a column in the document library so each vendor is specified when a document is uploaded, and a folder for each vendor in the document library into which the users put their quotes.

The workflow was originally all in one step, but I've since separated it into individual steps for each vendor, but the same issue happens so it doesn't seem to matter.

The workflow itself is as follows:

Step 1 If Current Item:Vendor equal Vendor A Pause for 20 days then email relevant parties (with a message that a vendor A quote is near expiration and a link to the item)

Step 2 If Current Item:Vendor equal Vendor B Pause for 355 days then email relevant parties (with a message that a vendor B quote is near expiration and a link to the item)

Etc. for the remaining 25 vendors.

The Issue

As is, the correct emails are being sent, and it seems to be linking the correct item but it's also sending too many emails. Specifically, while the email for Vendor B is eventually sent, I will ALWAYS get vendor A's email first (for all vendors/items uploaded to the document library despite them not being a vendor A item) but it still links to the correct item.

EDIT

I've discovered that perhaps having multiple items paused for days/weeks/months is probably not the best course of action “Pause until” in workflow 2010. Please advise if this would become an issue, too.

/EDIT

As is, I'm stuck folks. Any ideas on how I can achieve the desired outcome?

I've also included a picture of the workflow in case my description wasn't adequate.Vendor Quote Expiration workflow

0

I am unable to solve this by just using workflows. I do have a solution, however, that uses workflows and PowerShell. I am not sure if you have access to use PowerShell, but wanted to post this in case it helps you or someone else.

Basically, the SharePoint list will record the expiration dates for the different vendors. A PowerShell script will monitor the list & change an item's "SendReminder" column based on the expiration date. The item changed will start a SharePoint workflow that will send the email.

SharePoint List Settings

On the SharePoint list, keep the Vendor column that you have. Create a calculated column called "ExpirationDate". Use the following formula to determine the expiration date by vendor, based on created date.

=IF(
Vendor="Vendor A",Created+20
,IF(Vendor="Vendor B",Created+355)
)

Create a SharePoint choice column called "SendReminder" with two choices - "No" and "Yes". Set the default value to "No". The PowerShell script will use this column.

Create a SharePoint choice column called "ReminderSent" with two choices - "No" and "Yes". Set the default value to "No." The SharePoint workflow will use this column.

PowerShell Script

The following PowerShell script will change the "SendReminder" column when the ExpirationDate is today.

$snapin = Get-PSSnapin | Where-Object {$_.Name 
-eq 'Microsoft.SharePoint.Powershell'} 

if ($snapin -eq $null) 
{    
Write-Host "Loading SharePoint Powershell Snapin"    
Add-PSSnapin "Microsoft.SharePoint.Powershell"  -EA SilentlyContinue
}

$currentDateTime = Get-Date

#Supply the system name of the library, not the library's friendly name.
#The system name can be found in the Library Settings, in the "Web Address" Url.
    $libraryLocation = "VendorTest"

    $web = Get-SPWeb -Identity 'https://yoursharepointURL/'
    $library = $web.GetList($web.ServerRelativeUrl + $libraryLocation)

    foreach($libraryItem in $library.Items)
    {
    #The ExpirationDate column begins with "datetime;#", which needs to be removed.
    $itemExpirationDate = $libraryItem["ExpirationDate"].Substring(10)

    #Convert the ExpirationDate column into the proper datetime format.
    $itemExpirationDateFormatted = [datetime]$itemExpirationDate

    #If the expiration date is less than or equal to today's date
    if($itemExpirationDateFormatted -le $currentDateTime)
    {
        if($libraryItem["SendReminder"] -eq "No")
        {
            $libraryItem["SendReminder"] = "Yes"
            $libraryItem.Update()
        }
    }
}

$web.Dispose()

SharePoint Workflow

Remove the pauses in the workflow as those are no longer needed. Change the logic of the workflows to the following:

If CurrentItem: ReminderSent equals No
    If CurrentItem:Vendor equals Vendor A
        then Email

    If CurrentItem:Vendor equals Vendor B
        then Email  

You should be able to have one step with 27 if statements in it. Be sure to set the workflow to trigger when an item is changed.

Note: You can create a Scheduled Task on the SharePoint App server to run the PowerShell script as often as needed. This site has information for creating a PowerShell Scheduled Task.

https://community.spiceworks.com/how_to/17736-run-powershell-scripts-from-task-scheduler

I hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.