I struggled with the title for this blog; dare i say, after spending almost two days trying to make SharePoint Designer 2013 and Visual Studio 2012 Workflow return data from a few notable providers [YouTube, Twitter, iTunes] using the Dynamic Value / Dictionary Data Type in JSON format, and not being able to as documented, i was frustrated to say the least. I would have been Ok, if it wasnt for the fact that the same exact Logic/Process works for other providers such as [FreeWeatherOnline, etc] returning data in JSON format, the key difference being the structure of the JSON data being returned.
This is for a few SharePoint Community Events that I have been scheduled to present this material. So after beating my head against the wall, sending out S.O.S tweets, and pinging a few folks that are versed on the topic, I ended up doing my Demos with the REST API provider that returns the JSON data in the way that works OOB and documented in both SharePoint Designer 2013 (SPD2013) and Visual Studio 2012 (VS2012). Nevertheless, this was going to haunt me if i cant get it to work, so i went back at it and with the help of Bart @bart_tubalinal Tubalinal who is the self professed and "fabian validated" Pound for Pound Undisputed Developer in the World, we proved out that if you altered the return JSON data from the providers that didn’t work as expected, and then make a call from SPD2013 and VS2012 using the DynamicType / Dictionary variable, it worked as expected.
I will describe the process we used to determine this below…
Below, I will show you three data calls to REST API providers returning in JSON format, showing you both in the browser and in the JSON viewer, I call your attention especially to the JSON viewer and the hierarchy.
Screen clipping: Google YouTube API Above
Screen clipping: FreeWeatherOnline API Above
Screen clipping: Twitter API Above
Screen clipping: YouTube JSON Data Returned Above
Screen clipping: FreeWeatherOnline JSON Data Returned Above
Screen clipping: Twitter JSON Data Returned Above
What we found out is that if you have anything under the Root of the JSON node other than a JSON Array, for example as in the case of a few, the Version Number [returned as a JSON Object], although it works perfectly in a browser, or JSON Viewer, or Fiddler, it doesn’t make the right call when using SPD2013 or VS2012. If after modifying the data output and removing anything that is NOT a JSON Array from the Root of the Node, it should work as expected.
The first thing to prove it out is to have SPD2013 or VS2012 make a call to a URI that returns JSON, so that can easily be accomplished via Visual Studio Project to create a new Site, and then drop a file with the removing anything in the root that is not a JSON Array (Collection) and calling that Web Site from inside for example SPD2013 then it should return the requisite data than can be consumed by the Dictionary Object.
To begin, the first thing we need to do is remove the "appVersion: 2.1" JSON object out of the data returned, below is what is looks like without any adjustment
after you remove it, the raw file looks like below
just to verify, ill put it into a JSON Viewer to make sure it is what i want
then drop that raw file into a text document and place it at the root of the newly created Visual Studio Project Web Site
verify that you can get to it from a browser call
Now, below I will show you WITHOUT adjustment how the FreeWeatherService REST API works right off the bat, you will recall above that the only thing at the JSON root node is the JSON Array. So we put it into SharePoint Designer as below
Screen clipping: See the URI and Process Above
Screen clipping: Above you’ll notice the return data goes to the Dictionary DataType
So after you publish the Site Workflow and run it on the Site, you get what you expect to see below
Screen clipping: SharePoint Promoted Links List with JSON Data Returned
Showing the YouTube Working with the Mods to the Data Set and Without the Mods
So, below you will see how we make a call to the Virtual ASP Visual Studio Site [look at the URI call line you will see it set to localhost and port number]
Screen clipping: SPD2013 WF with the Modded JSON data call Above
After publishing the Site Workflow to the Promoted Links List we can see the output works as expected.
Screen clipping: Promoted Links List showing the Top 10 YouTube Videos displayed with Modded JSON data Call
Now Showing it NOT working as Expected
Below you will see the same code as in the one that worked, except the URI is set to the Google YouTube REST API and no Mods will be done to this data set.
Screen clipping: Above the same Logic/ Process except this call is made directly to the YouTube REST API
Now pay attention to the logging above, you will see that when the Workflows is published and deployed and ran, that the line for the call fails.
Screen clipping: Above is me executing the Site Workflow
Below you will see the Workflow status showing that the process halted
Screen clipping: The Workflow Status showing it didnt work
So, I originally called this a BUG, but for now it is just an observation. Ill be happy to know from our community folks, MVPs, MCMs, and other folks that come across this blog your thoughts. Sadly, I have turned off comments on my blog, but you can always shoot me a tweet at @fabianwilliams
Other Notables but I wont write it up, you can discern yourself
See me making it work in a Custom Action and also Not working using one REST API result that is in the format I said will work, and the other with it not.
Screen clipping taken: 12/31/2012 3:32 PM
Screen clipping taken: 12/31/2012 3:32 PM
Screen clipping taken: 12/31/2012 3:34 PM