Category Archives: Workflow

Creating a Currency Converter in SharePoint 2013

In this post, I will demonstrate how to create a no-code currency conversion solution using SharePoint Designer 2013 workflow.

Find a currency exchange data source

First things first. We need to identify a data source that would present the currency exchange rates. For this post, I will be using the following Currency API.

For one, it’s free. And since this service provides the response in JSON format, it’s primed for SharePoint’s consumption. A couple of caveats to note – it only supports 17 types of currency and you can only make a max of 3,000 web service calls per month. Since this post serves as a proof of concept, I’m mostly concerned about it being operational (and of course, free). If you search on the Interweb, you will find A LOT of results on Exchange Rates API. For a production development, you should consider paying for a service to ensure a higher reliability.

If you’re following this post to a tee, you need to sign in to get your key with the Currency API. Once you get your key, make sure that you can access the REST endpoint and fill in all the URL parameters (i.e.: source, target, and key) outlined here.

E.g.:  http://currency-api.appspot.com/api/USD/EUR.json?key=IwouldPasteMyKeyRightHere

Hang on to the URL as you will need it later! You can access it via a web browser, but I recommend using a JSON Viewer so the data is more presentable. One that I use is JSONView – it’s a Chrome plug-in that’s quick to install so I can punch the URL directly into Chrome. This is what you would see with JSONView:

JSONView

Create the list

Create a custom SharePoint list with the following columns:

Since the workflow will do the conversion, hide the following fields (Don’t know how to hide? Read this post):

  • Title
  • Exchange Rate
  • Converted Amount

Create the workflow

  1. Create a list workflow
     
  2. Break the workflow to 2 stages to segment the process:
    1. Rename Stage 1 to “Get Exchange Rate” then set Transition to “Go to Stage 2”
    2. Rename Stage 2 to “Calculate Converted Amount” then set Transition to “Go to End of Workflow”
    3. Workflow should look like this, at this point:
      Two Stages
       
  3. Create the web service call
    1. Go to the first stage. Type “web service” then click enter to select “Call HTTP Web Service”
       
      Call HTTP Web Service
       
    2. Click on “this”
      1. Once you see a pop-up, click on ellipses (…) on the right to access the String Builder
      2. In String Builder pop-up, paste your web service URL here, e.g.: http://currency-api.appspot.com/api/USD/EUR.json?key=IwouldPasteMyKeyRightHere
      3. Using the “Add or Change Lookup” button:
        1. Replace “USD” with the “Convert From This Currency” field
        2. Replace “EUR” with the “Convert To This Currency” field
      4. You should see thisString Builder
      5. Click OK
    3. Click on “response”
      1. Click “Create a new variable”
      2. Type a name for the dictionary, e.g.: “dictionaryXchangeRate”. This dictionary contains the JSON response which you would need to reference later on.
         
  4. Optional: Create a Log for the web service call
    1. To make it easier to troubleshoot potential issue, try creating logs throughout workflow
    2. Type “log” then select “insert Log to History List”
    3. Click on “message” than click on ellipses to open String Builder
    4. Click on “Add or Changer Lookup” and select the “Workflow Variables and Parameters” of “responseCode”Log Response Code
    5. Note: when you’re running the workflow, you should expect to see “OK” in workflow history if this web service call was successful
       
  5. Obtain the Exchange Rate info from the dictionary
    1. First, go back to JSON Viewer and obtain the exact data path to the Exchange rate field. In Chrome JSONView, you need to hover over the display field name and copy the exact path displayed on the bottom of the browser
      Exact Path

      1. For this REST API, the exact path is the same so it’s a non-issue. However, in other APIs, the path could certainly be different than what’s displayed in the browser, so be sure to use the exact path.
      2. Side note: you may also need to reference the path differently in SharePoint Designer (SPD). For example if the exact path were “d.rate” then you would need to change it to “d/rate” in SPD.
    2. In SPD, type in “get” then click enter to select “Get an Item from a Dictionary”
    3. Click on “item by name or path”
      1. Type “rate” and click Enter
      2. Click “dictionary” and select “Variable: dictionaryXchangeRate”
      3. Click “item” then select “Create a new variable”
        1. Provide a variable name for the currency exchange, e.g.: “valueXchangeRate”
        2. Select the type of “Number”
          Variable XchangeRate
           
  6. Optional: Create a log for the exchange rate
    1. Type “log” then select “insert Log to History List”
    2. Click on “message” than on ellipses to open String Builder
    3. Click on “Add or Changer Lookup” and select the “Workflow Variables and Parameters” of “valueXchangeRate”
       
  7. Set Exchange Rate
    1. Type “set field” then select “Set Field in Current Item”
    2. Click “field” then select the “Exchange Rate” list field
    3. Click “value” then select the fx button
    4. Select “Workflow Variables and Parameters” data source and “Variable: valueXchangeRate” field source. Then click OK.
    5. Workflow should look like this, at this point:
      Workflow with Completed Stage 1
       
  8. Convert Currency
    1. Go to the “Calculate Converted Amount” Workflow Stage
    2. Perform calculations
      1. Type “calc” then select “Do Calculation”
      2. Click the 1st “value” then click fx button to select “Exchange Rate” field. Then click OK.
      3. Click “plus” then change to “multiply by”
      4. Click “value” then click fx button and select the “Amount” field. Then click OK.
    3. Set calculated field to list field
      1. Type “set field” then select “Set Field in Current Item”
      2. Click “field” to select “Converted Amount” field
      3. Click “value” then click fx button. Select the “Workflow Variables and Parameters” data source and “Variable: calc” field source
      4. Click OK
    4. Your second Workflow Stage should like this:Workflow with Completed Stage 2
       
  9. Start workflow automatically when item is created or changed
    Workflow Start Option

Take Workflow for a Test Drive

Access the SharePoint list and create a new item
 
Create New Item Form

See the workflow in action. Watch the rate and converted amount get auto-populated.
 
Workflow in action

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:

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]