Bobby's SharePoint World

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:

Create the list

Create a custom SharePoint list with the following columns:

• Amount (Number field)
• Convert From This Currency (Choice field – populate with these country codes)
• Convert To This Currency (Choice field – populate with these country codes)
• Exchange Rate (Number field)
• Converted Amount (Number field)

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:
      
       
3. Create the web service call
   1. Go to the first stage. Type “web service” then click enter to select “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 this
      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”
   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
      
      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”
            
             
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:
      
       
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:
       
9. Start workflow automatically when item is created or changed
   

Take Workflow for a Test Drive

Access the SharePoint list and create a new item
 

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