Category Archives: SharePoint 2013

Easing the Pain of Document Copy in SharePoint

I was recently faced with a common challenge of needing to ship a document from one document library to another.  While we all wait for the upcoming, super promising Move & Copy feature (announced during Future of SharePoint), I had to come up with another route that doesn’t involve instructing the end users to use Save As or download a local copy then re-upload to the other library.

For this use case, the request was simple.  Copy a document to a different library.  For that reason, I immediately thought of an old trusted friend, the Send To destination.  Send To does everything that the end users needed, except the function is tucked away in the ribbon, accompanied by a teeny 16×16 icon.

 

This is, after all, a carry-over function from SharePoint 2007, so its lack of prominence is understandable.  In the spirit of making things easier though, I had to do a bit more digging.  As it turns out, the Send To function references a copy.aspx system file, residing in the _layouts folder.  What’s beautiful about this is the URL path, where you could identify the document location and send it anywhere within the web application (and yes, it does go across site collection!)

The formula is as follow:

[YourSourceSite]/_layouts/copy.aspx?SourceUrl=[YourDocument]&FldUrl=[YourTargetLibrary]&Source=[YourSourceLibrary]

As an example, here’s Demo Document being copied from Contoso Site to Demo Site:

http://contoso.com/_layouts/copy.aspx?SourceUrl=http://contoso.com/Shared%20Documents/Demo-Document.docx&FldUrl=http://contoso.com/sites/demo/Shared%20Documents&Source=http://contoso.com/Shared%20Documents

Now, what we can do with this information is to create a Custom Action (otherwise known as Quick Step) in SharePoint Designer (SPD) in order to promote this to our document’s Properties menu/hover card.  Once you connect to your site in SPD and find the Document Library, click on “New” in the “Custom Actions” section.

Give the Custom Action/Quick Step a name then select “Navigate to URL” option.  Copy the URL that you have constructed from the exercise above, except replace The Source URL (i.e.: the link to your document) with {ItemURL}

So the new formula is as follow:

[YourSourceSite]/_layouts/copy.aspx?SourceUrl={ItemUrl}&FldUrl=[YourTargetLibrary]&Source=[YourSourceLibrary]

Using our example, we would post this in SPD:

http://contoso.com/_layouts/copy.aspx?SourceUrl={ItemUrl}&FldUrl=http://contoso.com/sites/demo/Shared%20Documents&Source=http://contoso.com/Shared%20Documents

Once you save your Custom Action, you should see it immediately in the Properties menu/hover card.

Click the Quick Step/Custom Action and it will be like using the Send To.

A few things to note:

  • This process will copy document across libraries in different sites and different site collections (which is pretty cool)
  • It will copy metadata values *but* all columns and/or Content Types in the 2 document libraries must be identical.  Otherwise, you may run the risk of metadata values being left behind.
  • It can only copy 1 document at a time
  • It cannot copy Folder … but who wants to use folder anyways? =)
  • In my brief testing with the SharePoint Modern Library (i.e.: New Experience mode currently in SharePoint Online First Release, at the time this post was written), the Send To function does NOT work. The Copy page appears but clicking OK doesn’t actually do anything.
    That being said, this post would be less relevant after the new Move & Copy feature has been released for SharePoint 2016 and SharePoint Online (which should include move/copy function to other document libraries and OneDrive For Business)

Clearing SharePoint Designer 2013 Cache with PowerShell

If you’ve worked long enough with SharePoint Designer (SPD) 2013, then you’re probably aware of the constant need to clear cache.

Here’s a couple symptoms that you may run into with SPD caching issues:

  1. Error message: “Cannot perform this operation. The file is no longer checked out or has been deleted.”
     
  2. Published Workflow with conditions and transitions stripped/removed
    (if you invested time & sweat, this could look very daunting!!)
     

Clearing cache in SharePoint Designer is no rocket science. But it’s also not Ctrl+F5 that some of us have grown to love. Essentially, you have to delete all files in 2 local folders:

  • %APPDATA%\Microsoft\Web Server Extensions\Cache
  • %USERPROFILE%\AppData\Local\Microsoft\WebsiteCache

Just like rebooting any machine, once you clear cache, all most of your SPD problems disappear.

Now, my short term memory can be rather lousy … so I always have to dig up my notes to get these folder locations. This is why I love this handy dandy PowerShell script who does the clearing for me. The script is courtesy of my brilliant colleague, Matthew Bramer.

Remove-Item "$($env:APPDATA)\Microsoft\Web Server Extensions\Cache\*" -recurse -force
Remove-Item "$($env:USERPROFILE)\AppData\Local\Microsoft\WebsiteCache\*" -recurse -force

Save the script as a .ps1 file for future reference. When you run into a caching issue, make sure you close SharePoint Designer first. Then simply right click the ps1 script file and run with PowerShell. After that, you’re golden.

Side note: this should also work for SPD 2010 Cache. Though I have yet to confirm it.

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:

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.