Flow Tip: Pick “Value” for Choice Field

They say that it’s the little thing in life that makes a big difference. In that spirit, I’m looking to share this little thing I picked up today in Microsoft Flow and SharePoint. It’s trivial and if you already know this, then great! If not, then even better because now you know and don’t have to lose time like I did, over such a little thing =)

The scenario is building a Flow that interacts with a Choice Field in SP

SharePoint choice field

When you build the Flow, you’ll see 2 listings to the same Choice field. If you want to reference the user’s selection on the Choice Field, you have to select the one with the suffix of “Value”.

Select choice field with Value in Flow

Otherwise, the Flow will not reference the value/selection of the Choice field.

And that’s the end of post =) Because I was working on a slightly more complex Flow, I lost more time in troubleshooting this small issue than I care to admit to. In leaving this small breadcrumb, hope the tip saves the trouble from the next person! Enjoy your 4th of July!


Maps in PowerApps

This post outlines how to render address information on a map using PowerApps.  We will utilize SharePoint Online as the data source. However, keep in mind that you could opt for other (growing number of) sources, e.g.: Salesforce, Dynamics CRM Online, etc.

Quick Disclaimer –  At the time of writing this post, PowerApps is in Public Preview, so PowerApps’ features and accuracy of this post are subject to change.

Prep Work:

  1. Find a REST service to render your map. For this post, we’ll leverage Bing Maps:
    • Documentation and Requirements:
    • There are tons of options you could explore with Bings. To keep it simple and use plain address fields, let’s use this service:
    • Confirm your REST endpoint by going to the URL using a web browser
  2. Get the data source ready
    • Since SharePoint Online serves the underlying data for this example, I have created a plain-vanilla-custom-list, then added out-of-box address-related site columns.
    • Record the internal field names for all of your Address Fields

Steps in PowerApps Studio

  1. Open PowerApps Studio (currently available only for Windows 10 & 8.1)
  2. Create a New app and connect to your data source
  3. If you have connected to the data source correctly, some of your content should surface immediately
  4. Click on DetailScreen1 on left navigation pane, to display a single record view
  5. Click “Add a custom card” in the form design
  6. Select the Insert Tab then click on “Image”
  7. At this point, you should see the landscape icon in your Form.  If so, we will now construct our Bing Map onto this image.
  8. Go to the function (fx) text box to replace “SampleImage” with the REST Endpoint URL combined with the SharePoint internal field names, using the PowerApps Concatenate function.
  9. If you have done it correctly, the map should render almost instantly in Design mode.  A few additional notes:
    • Since this REST method separates the address with space, we are constructing a ” ” in our formula
    • PowerApps auto-populates field names in the function text box so you could easily confirm if you have the correct SharePoint internal field names

And there you have it! Hopefully straightforward.  Please leave comments with your thoughts and feedback.

Very much looking forward to the exciting things coming out of the labs of the MS Product Team and the magic our community creates out of this very promising product.

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:


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


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:


Using our example, we would post this in SPD:


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)

Back to Basics – Facilitation 101

After facilitating nearly 30 requirement gathering sessions in the past weeks, I had a chance to take a step back for some self-reflections. For starters, these sessions can be darn exhausting! And after numerous interactions with the many different personalities, I found myself needing to exercise recurring tactics, which were rather fundamental and, by no mean, earth shattering. Yet, it was refreshing to have been reminded of how important these things could be during any requirements gathering interview. I hope to keep them in mind and continue to hone my skills based on these basic principles:

Practice Empathy
Getting people to talk about what they do or want to do, could be challenging and uncomfortable. Sure, there are ways to make the process smoother. But ultimately, doing self or need-assessments and stepping someone else through that – is a hard ask. Throw technology in the mix, and you have what could be a very daunting experience for any information worker.

That being said, be sure to level with them. Hear their comments, concerns, complaints. Even in the most adversarial of interactions, I often experienced that listening to people and just simply nodding could be the best remedy to defuse the situations.

Come Prepared
These participants are setting aside their normal 9-to-5 to talk with you. Without doing anything (good or bad), you are already a distraction to their day. Knowing that, come prepared and be ready! Never discount any prep work, like arriving ahead of the meeting time to setup. Boot up your devices, hook up that projector, straighten out the room. Should your meeting be virtual, sign in to the meeting and start sharing your screen in advance.

You need to prepare your mindset as a facilitator. You want to make a good impression and let your participants know that you are present and you are ready from the moment they enter that room.

Different Strokes for Different Folks
One of my favorite mantras to live by in the professional setting is to “Over-prepare but be flexible.” As mentioned, preparation is key to a successful session. That being said, however, a common mistake is to put a meeting plan on a pedestal. Of course you want to meet the meeting objectives. But remember that you are there to facilitate, not dominate.

Some people are creative, others are analytical. Some groups are visual learners, others are auditory. What works for one person or one group could be a flop for another. You won’t hit a homerun every time, but you should do your best to adjust your approach, if you have to, so you don’t strike out.

For instance, if your open-ended-questions were getting nowhere, then try 20 close-ended-questions. If “Tell me about your morning” were simply getting an “I’m fine” response, then don’t stop there. Ask if they ran into any traffic in the morning, what their favorite morning coffee is, the kind of pets they had to kiss before leaving for work, the names of their kids they had to drop off on the way to the office.

Be flexible to change your method to cater to your audience and the room. Don’t fall in love with your agenda!

2 Heads are Better Than 1
If your project could afford the time for a peer review, always take advantage of it. I realize that in this competitive market with fast-tracked project schedules, that’s often a *big* if. Whether you are a more junior member or not, having a colleague or a mentor review your requirements really goes a long way for the project. Whether it’s to bounce ideas with or to let him/her poke holes on your theories, a peer review provides a different perspective, which keeps you more honest in your progress and approach.

Through my current project alone, the peer review sessions have helped me to be better prepared as a facilitator, allowed me to think more creatively about potential solutions, and has boosted my confidence as a technologist, which would ultimately impact my overall professional development.

Dilbert - Requirements Gathering

I thought I would end the post with this Dilbert cartoon, which I found to be very fitting. I often say (half-jokingly) that if we were to remove people from the equation, then everything would be too easy. At the same time, you would also be removing all the fun.

Thanks for reading. Let me know what you think and please do share any personal tips that you have to keep your requirement sessions productive and engaging.

RIP bobbyspworld.com


In an effort to unify my social media presence, I have decided to change my blog to bobbyschang.com. You can also find me in Twitter, LinkedIn, Slideshare, about.me – all with the bobbyschang tagline. It’s a personal branding move that, if not for anything else, would be easier for me to share my contact info for networking.

This also means that I bid farewell to the bobbyspworld.com domain name. With industry trends leaning towards O365 and cloud technologies, dropping sp may also not be the worst of idea. Since bobbyspworld had been around for 4 years, I expected this change to impact SEO. I don’t write often so I, honestly, am not too concerned with site statistics or lower readership.

But when I found myself receiving personal inquiries (including text/SMS on my personal cell) about the expired domain, I was taken aback. Unlike blog hits status quo, this on the other hand, has me concerned. If there is a person or two who reference these posts on a semi-regular basis, then I should have done a better job providing a heads-up of the change.

If you have relied on this blog and found that your bookmark now leading to a dead link, I apologize. I hope this post is picked up by you and the search engines, noting that bobbyspworld is no more but all content is still accessible in bobbyschang.com

As always, thank you for reading. And here’s to better communication and more writing!

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:


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