Calculate Fiscal Year Using Workflow

This one might be simple but is often requested for. Most organizations track their numbers in an operating or fiscal year which typically spans two calendar years. For example, if bookkeeping were to start every October, then Fiscal Year 2014 would mean an operating period of Oct 2013 to Sept 2014.

This post demonstrates how to auto calculate fiscal year based on month and year selection using SharePoint Designer workflow.

To better illustrate each step, here’s a screenshot of the completed workflow:

  1. Prepare your fields
    At the minimum, you’ll need Month, Calendar Year, and Fiscal Year fields. Recommended format:

    • Month & Calendar Year = Choice fields
    • Fiscal Year = Single line of text
  2. Hide Fiscal Year field
    Because the value will be auto generated by the workflow, you won’t need this field to appear on the form (Don’t know how to hide? Read this post)
  3. Set initial Fiscal Year value
    1. Create a Variable
      • Click on “Local Variables” in SPD (SharePoint Designer) ribbon
      • Add an “Integer” variable named “CalculatedYear”

    2. Set value of CalculatedYear variable to Calendar Year
      • Select “Set Workflow Variable” action
      • Select “CalculatedYear” workflow variable
      • Click on “value” then click on
      • Select “Calendar Year” field. Then click OK

    3. Set initial Fiscal Year value
      • Select “Set Field in Current Item” action
      • Select “Fiscal Year” field
      • Click on “value” then click on
      • Type “FY” then click on “Add or Change Lookup” button
      • Select Data source of “Workflow Variables and Parameters”
      • Select “Variable: CalculatedYear” field then click OK

      • At this point, you should see this. Click OK.

  4. Create exceptions for first Calendar Year
    1. Create workflow conditions
      • Click in between the 2 workflow actions then add an “If any value equals value” condition
      • Add all the months for the first Calendar Year as workflow conditions, e.g.: if fiscal year starts in July then create different conditions from July to December
      • Change the conditions from “and” to “or” operation

    2. Add 1 to Calendar Year
      • Add “Do Calculation” action
      • For 1st value, select “Variable: Calculated Year”

      • For 2nd value, type “1”
      • Leave “Variable: calc” as is
    3. Assign new value to variable
      • Add “Set workflow variable” action
      • Select “Variable: CalculatedYear” workflow variable
      • Click on “value” then click on
      • Select Data source of “Workflow Variables and Parameters”
      • Select “Variable: calc”

  5. Create “End of Workflow” at the bottom of your workflow design. Optional step: Log workflow activities
    As a general good practice, you should create log history along the way. In this example, I created a log to differentiate between first or second Calendar Year in any given Fiscal Year.
  6. Start workflow automatically when item is created or changed
    Select these settings in SPD so your users won’t have to manually kick off the workflow.


Here’s the end product:


SharePoint Online 2013 Upgrade As Easy As 1-2-3

Very pleased to have received my official upgrade to Wave 15 (aka 2013) for my SharePoint Online environment. Microsoft made the process very seamless. Granted I’m not running much customizations, e.g.: sandbox solutions, custom branding, etc. Nonetheless, after receiving the MSFT notification email, it took 3 clicks to complete. I wanted to share with those still awaiting their upgrades.

Here’s the sequence:

Click 1 – Start Now
Bright pink banner across the top that even the three blind mice could see

Click 2 – Upgrade

Click 3 – I’m ready
You can still hold off in case you changed your mind

Now you just sit and wait
My upgrade took all of 70 seconds. But while you’re at it, here’s a second chance to back out of the upgrade. I didn’t give it a try but it’s nice to have yet another opportunity to change your mind.

Then voila, au revoir 2010.

Looking forward to diving in. Hope your upgrade goes/went as smooth as mine did.

Why I Always Opt Out from SharePoint Welcome Emails

In SharePoint, you can send email notifications when granting a user with access to SharePoint content. I have never been a big fan of this “Welcome Email.” The email is wordy, but often doesn’t provide enough context to users. Instead, I usually draft a personal email and notify the users separately.

Unfortunately, the “Welcome Email” option is selected by default. Easy to miss. So I have always encouraged users to be mindful and deselect this check box.

Recently, I had a front row seat of yet another great reason why we should all stay away from SharePoint welcome emails. A Site Manager had added a Domain Group to SharePoint but neglected to turn off welcome emails. Normally it wouldn’t be such a big deal, but in this case, the Domain Group added was comprised of over 10,000 users. What ensued is undoubtedly a memorable lessons learned – here’s a recap of how the day unfolded.

It all started with that Welcome Email [Names and references have been altered]

The team went proactive and tried recalling the message

Of course, it didn’t help and instead, the message went *viral*. Here’s a small sample size of the email traffic fiasco.

The Unsubscribe Me Group

The Angry Birds

The Good Citizen (note: singular tense is not a mistype)

The Clueless Bunch

The “I Will Get You to Stop “Reply All” By Replying to All”

So just to recap: Turn Off the SharePoint Welcome Emails.

You’ll thank me and this Site Manager later.

5 Steps to Enhance SharePoint 2010 Approval Workflow

In my previous post, we discussed the shortcomings of SharePoint 2010 Approval workflow. Though empowering and convenient to use, the out-of-box workflow lacks a user-centered experience. To quickly recap, I highlighted five limitations in particular:

  • Inability to specify workflow condition
  • Potential governance concern
  • Vague email notifications
  • Due Date Duration not accounted for
  • Rejected document marked as “Completed”

Fortunately, through SharePoint Designer (SPD) 2010, you can extend the approval workflow process. This post outlines how to configure the approval workflow using the SPD workflow designer. Though it’s certainly not the only way to customize approval workflow, I find myself coming back to this similar framework when implementing approval processes.

Step 1 – Expose “Start Approval Process” action in workflow designer

  1. Open SPD 2010 and create a new List Workflow for your Document Library
  2. Click Action in the ribbon. Then select “Start Approval Process” under the “Task Actions” heading
    Start Approval Process Action

  3. Click on “these users” to set the approval designations
  4. The next screen you’ll see mimics the Approval Workflow design form in the UI. Fill in the appropriate approval details. (Note: You can add multiple approval stages by clicking this icon and selecting “Insert Assignment Stage”)
    these users

  5. After clicking Ok, you’ve completed the 1st step. Click on “Approval” to start configuring the details of the approval workflow.
    Update Approval Process

  6. Optional Step

    For every approval action you utilize in SPD, SharePoint creates a custom Approval Site Content Type. In other word, if you create 5 approval actions through SPD, you’ll end up with 5 different Approval Content Types (see screenshot below). So, it’s a good idea to rename the Approval Site Content Type and match your workflow name. Renaming to “Approval – Team A Proposal Draft,” for example, would provide better context and probably serve your well in the future.

Step 2 – Update email notification for the requester

  1. Click on “Change the behavior of the overall task process” in the Approval editor page
    Change the behavior of the overall task process

  2. Find the Step “When the Task Process Starts”
  3. Then click on the Action “then Email Workflow Context: Initiator”
    Email Workflow Initiator action

  4. Change the subject line to something more descriptive
    Otherwise SharePoint would send an email with a generic subject line, i.e.: “Approval started on.” To change the subject, click on icon and replace [%Task Process: Process Name%] with something easier for your team to relate to, i.e.: RFP Approval, Draft Report Approval, etc.
  5. Clean up the email body. Below is a screenshot example of my change:
      Workflow Initiator email design

    The following Data Sources and Fields were kept same:

    • [%Task Process:Item Title (Unencoded)%]
    • [%Task Process:Item Title%]
    • [%Task Process:Participant List%]
    • View the status of this workflow
      • URL is the same
      • Changed label to “Monitor the status of the approval here”

    The following Data Source and Field were added:

    • [%Workflow Context: Initiator%]
      • Data Source = Workflow Context
      • Field from source = Initiator
      • Return field as = Display Name

Email as end result of Step 2:

    Workflow Initiator actual email

Step 3 – Update email for approver(s)

  1. Click on “Change the behavior of a single task” in the Approval editor page
    Change the behavior of a single task

  2. Find the Step “When a Task is Pending”
  3. Then find the Condition “If Current Task: External Participant is empty,” and click on “Current Task: Assigned To”
    Email Assigned To

  4. Clean up the email body. Below is a screenshot example of my change:
      Approver email design

    The following Data Sources and Fields were kept same:

    • [%Current Task: Title%]
      • This value comes from the Title field in the Select Task Process Participants screen (see #4 in Step 1 above)
    • [%Workflow Context: Initiator%]
    • [%Task Process:Item Title%]

    The following Data Sources and Fields were added:

    • [%Current Task: Assigned To%]
      • Data Source = Current Task: Approval
      • Field from source = Assigned To
      • Return field as = Display Name
    • [%Current Task:Due Date%]
      • Data source = Current Task: Approval
      • Field from source = Due Date
      • Return field as = Short Date
    • Access approval form in SharePoint
      • To create hyperlink, highlight the text then click Globe Hyperlink Icon icon
      • Assign [%Current Task:Form_URN%] on the address field:
        • Data source = Current Task: Approval
        • Field from source = Form_URN
        • Return field as = As String
      • Click OK a couple of times and you should see the following below. Then click OK.
        Edit Hyperlink

Email as end result of Step 3:

    Workflow Assigned To actual email

Step 4 – Update rejection notice

  1. Click on “Change the behavior of the overall task process” in the Approval editor page
    Change the behavior of the overall task process

  2. Find the Step ““When the Task Process Completes”
  3. Find “Set workflow status to Rejected.” Then click below “Set workflow status to Rejected.”
  4. Update email subject line to denote rejection:
    • Add “Set Workflow Variable” from Action on the ribbon
      Workflow Variable
    • Click on “workflow variable” then select “Variable: CompletionMailTitle”
    • Click on “value” to assign a rejection tagline to be displayed in the email subject line
      • Access the String Builder by clicking this icon
      • Use the word “Rejected” combined with brief info about the rejected file
      • E.g.: “REJECTED – Draft Report: [%Task Process:Item Title%]”
        • Data source = Task Process: Approval
        • Field from source = Item Title
        • Return field as = As String

        String Builder Rejected Title

  5. Update email content to denote rejection:
    • Click under the action you just created to add another workflow action
    • Click “Set Workflow Variable” from Action on the ribbon
    • Click on “workflow variable” then select “Variable: CompletionMailReason”
    • Click on “value” to assign a rejection message to be displayed in the email body
      • Access the String Builder by clicking this icon
      • Include the word “Rejected” along with some context about the rejected file
        String Builder for Rejected Body
  6. At this point, your set of Rejected actions should look similar to the following
    Rejected workflow actions

  7. Scroll below and click on the last action – “Email Workflow Context: Initiator” under “Else”
    Workflow Context Initiator

  8. Clean up the email body
    • Remove the first line. Otherwise, the words “Approval” and “Completed” will be included on the rejected email
    • Make sure you keep both [%Variable: CompletionReason%] and [%TaskProcess:Consolidated Comments%] because they provide the rejected status and comments from the user(s) who rejected the file
    • Everything else is optional. Please feel free to redesign as needed.

Email as end result of Step 4:

    Rejected email

Step 5 – Update notification for approved files

  1. Similar to Step 4. Only this time, we’re configuring the email notification for an approved document
  2. Click on “Change the behavior of the overall task process” in the Approval editor page
  3. Find the Step ““When the Task Process Completes”
  4. Find “If Variable: IsItemApproved equals Yes” then click below “Set workflow status to Approved.”
  5. Update email subject line to denote Approval:
    • Add “Set Workflow Variable” from Action on the ribbon
    • Click on “workflow variable” then select “Variable: CompletionMailTitle”
    • Click on “value” to assign a rejection tagline to be displayed in the email subject line
      • Access the String Builder by clicking this icon
      • Use the word “Approved” combined with brief info about the approved file
      • E.g.: “APPROVED – Draft Report: [%Task Process:Item Title%]”
        • Data source = Task Process: Approval
        • Field from source = Item Title
        • Return field as = As String
  6. Update email content to denote Approval:
    • Click under the action you just created to add another workflow action
    • Click “Set Workflow Variable” from Action on the ribbon
    • Click on “workflow variable” then select “Variable: CompletionMailReason”
    • Click on “value” to assign a rejection message to be displayed in the email body
      • Access the String Builder by clicking this icon
      • Include the word “Approved” along with some context about the approved file
        String Builder Approved
  7. At this point, your set of Approved actions should look similar to the following
    Approved workflow actions

Email as end result of Step 5:

    Approved email

And there you have it! Five steps to get you started with enhancing the approval workflow. We focused mostly on emails, but there are still other system generated emails that you can configure. Furthermore, there are a slew of other functions that you can explore, e.g., Task Form Fields and Task Outcomes to extend the approval capability even further. I hope this post would get you started in the right direction.

5 Limitations of SharePoint Approval Workflow

The Approval workflow in SharePoint has been helping teams automate business processes since the days of MOSS 2007 and WSS3.0. It is the most commonly used workflow and since it’s pre-built, SharePoint has done the heavy lifting so your setup time is greatly reduced. However, the Approval workflow can be a bane to many. On one hand, you can be up and running with just a few clicks; on the other, the out-of-box user experience may not be very intuitive for the users.

There’s certainly more than one way to skin this cat, but I find myself repeating a similar series of steps to enhance the experience. I look to document these steps before Microsoft officially upgrades my SharePoint Online environment to version 2013. I hope that, in the process, this post could also be useful to other SharePointers.

Before diving into the proposed solution, let’s first breakdown some of the challenges. Here’s my personal Top 5 Limitations of the SharePoint Approval Workflow:

  1. Inability to specify workflow condition

    Applying workflow through the browser only provides you with the option to either start the workflow automatically or start it manually. You can’t conditionally design a parameter around how the workflow starts. For example, if you want the workflow to run only when a specific type of file is uploaded, you can’t do so through the browser.

  2. Potential governance concern

    Users, who can manually start the workflow, can also change the workflow’s rule and designation. When teeing up a document for an approval, SharePoint allows for ad-hoc update to the workflow logic. Almost every makeup to the workflow (approvers, deadline, etc.) is up for grabs to everyone who has access to run it manually. This open-ended structure leaves you at the mercy of your users, who can accidentally (or not) change the most fundamental elements of the approval, thus altering the orientation of your business processes.

  3. Vague email notifications

    The auto-generated emails from the Approval workflow often lack clarity. Upon receiving the email, users may not know how to move forward because the emails have duplicated info, ambiguous message, and most importantly, unclear instructions on how to approve the document. Users would be notified to “Perform specific activities required” without any other context.

    Outlook integration feature – which could open the approval form from the email and is actually very helpful – gets lost in the mix because the language in the email makes no reference to Outlook. Users are simply instructed to “Use the Open this task button” – a “button” that’s located at the top of the email and is very easy to miss, especially in the Outlook preview pane.

  4. Due Date Duration not accounted for

    There are 2 ways to set approval due dates – you can either assign an actual due date or specify a duration of the approval task. For the most part, the preference should be to set deadlines by duration so you don’t pin yourself down to one particular due date for all documents. Furthermore, should enough time passes by and your due date is in the past, SharePoint will display an error message to the user initiating the workflow.

    Due Date vs. Durations

    Unfortunately, if you were to assign due date by duration, the approval workflow would not account for it. The system generated email would simply assign your due date with “None.”

  5. Rejected document marked as “Completed”

    I saved the best for last, because this one trips me up the most. Receiving an email from the SharePoint library with a subject line of “Approval has completed” does NOT necessarily mean that your document has been approved. Your supervisor could very well have rejected your document and as long as all the required tasks are done, SharePoint deems the workflow as “completed.”

    The body of the email doesn’t help the cause either by repeating such words as “approval,” “successfully,” “completed” to further mislead the user. Only if you were to look carefully, would you see 1 mention of the rejection buried in the middle of the email content. A definite flaw from a user experience design perspective.

Despite these challenges, I still believe that there are a lot of business values provided by the SharePoint Approval workflow. Thankfully, there are a few simple ways to overcome these shortcomings and I look to document these steps on my upcoming blog post.

Do you have other challenges with the workflow that’s been plaguing you? If so, please feel free to share your experience. Hopefully we can all compare notes on the implementation work-around and be more productive at the other end.

[Update: My next post in this approval workflow series is now available. Read the 5 Steps to Enhance SharePoint 2010 Approval Workflow]

Maiden Speaking Opp as SharePoint Saturday Celebrates 5 Years

SharePoint Saturday celebrated its 5th year this past weekend at Virginia Beach. As the birthplace of SharePoint Saturday, VA Beach was a fitting place to open the 2013 year with this birthday bash. Five years since its inception, the SharePoint Saturday franchise now has Palestine, Perth, Vietnam, and Brisbane, to name a few, to call home. With so many events globally, all provided at no cost, it’s easy to take SharePoint Saturdays for granted.

If you take a step back and truly think of the magnitude — how amazingly awesome is this feat? That’s 5 years of bringing together SharePoint professionals, thought leaders, authors, and MVPs. 5 years of food, T-Shirt, raffle, and giveaways. 5 years of best practices, tips, demos, slidedecks, keynotes, and presentations. All provided FOR FREE.

If you have a curiosity or passion for SharePoint, all you have to do is sign up and show up. You’ll get a chance to learn a wicked thing or two, meet like-minded SharePoint folks, and get free stuff while you’re at it. And by free stuff, we’re not talking a 10% coupon to Applebee’s. They can range from SharePoint books to games, gift cards to electronics. This past weekend, SharePoint Saturday VA Beach even gave out the new Surface tablet.

I feel so blessed and fortunate to be working in a field that has so many selfless individuals. People who have donated countless hours to make such fantastic events like SharePoint Saturdays and SharePoint User Groups that happen on a regular basis. I’m hard pressed to find anything else out there that resembles what we have with the SharePoint community. As someone who has gained so much from attending these free events for the last couple of years, I decided to take a leap and pay it forward. I had the great pleasure of speaking at a SharePoint Saturday for the first time this weekend in VA Beach. My topic was avoiding the use of folders in SharePoint (see the slidedeck below). We had a full room and an interactive group. The session went almost the entire 75 minutes. It not only felt right to help out the SharePoint community, but I also had such a blast delivering my session.

One main takeaway for me from the entire experience is to be a little less apprehensive amongst the other SharePoint speakers. Many of the SharePoint speakers have a very strong SharePoint and social media persona that you can’t help but to be a bit intimidated. After all, you’ve read their books, followed their posts, heard them speak in conferences for years. When you finally see them in person, your respect and admiration can certainly get the best of you. But through this experience, I have found that these thought leaders are extremely approachable. They are very open to connect and are genuinely nice people. If I get another opportunity to be selected to speak in future SharePoint events, I look to draw up more courage to reach out and personally thank them for all their great work in the community.

So if you actually made it to the end of this post, this probably means 1 of 2 things – you’re either my wife (hi baby!) or you probably have a tie of some sort to the SharePoint technology. If you’re invested in a certain capacity with SharePoint and you have found yourself benefiting from the community – whether through a blog post, a SharePoint User Group session, or anything in between – then I encourage you to be involved. I would like to conclude by referring to a great post written by a community champion, Wendy Neal. Wendy listed different avenues to encourage everyone to be involved. After all, the community is about you and me! SharePoint Saturdays and User Groups can only sustain their success if we are all involved at some capacity. There are so many opportunities out there, please seize them! Help out and meet some amazing people along the way!

Pesky Long URLs

In an SP2010 client environment, I have recently been encountering a slew of Check-In and Check-Out issues across separate libraries and site collections. The common scenario is a user checking out a file, then after working on the document and in the process of checking it back in, he/she would receive a SharePoint error. Discarding check out and going through the process again will yield the same result.

In my experience, a Check-In and Check-Out issue is typically a user error. So I would go through the normal verification process, i.e.:

  • Does the user have the right permission?
  • Is the user trying to check out a document that’s already checked out to another user?
  • If the file’s checked out to a local SharePoint Draft folder, is the user trying to re-open the file on a different computer?
  • Does this file have a previously checked-in version?
  • Is it the same user login for both the computer and SharePoint?
  • Etc. etc.

However, the troubleshooting sessions came to no avail.

Luck would have it though. With some persistence poking and prodding, the perpetrator is Long URL.

TechNet has notified us of the SharePoint limitation with long URL. As the article mentioned, Office Client can only consume a link with a max of 259 total characters. If you’re lucky, you might see this DDE error.

    A DDE error has occurred, and a description of the error cannot be displayed because it is too long. If the filename or path is long, try renaming the file or copying it to a different folder.

Sadly, the errors that my users were seeing had zero indication of long URL. So if you’re experiencing a similar issue with document collaboration, try putting URL length on your test radar. If that’s indeed your problem, you’ll need to cut down on the URL for your documents. Here’s a few things to minimize or preferably not have altogether in your SharePoint instance:

  1. Deep folder structure
    In general, a good SharePoint implementation should have little to no folder on document libraries. If you must, try not going too deep. Each folders and subfolders are included in the filepath/URL, which gets you closer to the 259 max.
  2. Long Site or Document Library URL
    Leave the descriptive context for the name/title. You want to be very careful at the time of creation, especially with Document Libraries, because you cannot change the URL to a library once it’s created. When you first create a document library, you want a short and concise name with no spaces (i.e.: “TeamDocuments”). Once it’s created, you can access Library Setting > Title, and change it something a bit more descriptive.
  3. Long naming convention
    Much like site and library, filename also counts towards the URL total count. Beware of long winded filename.
  4. Managed Path
    If you’re not familiar with Managed Path, it’s typically the “/sites/” after your sitename (i.e.: If you have a long Managed Path for your site collection, try working with your SharePoint administrators to create an Alternate Access Mapping, so you can start off with a shorter URL.

Of course there will always be another reason why Check-In/Check-Out isn’t working accordingly in your environment. But I hope that this scenario can help somebody out there, because it certainly got me scratching my head for a bit. Good luck!