How’s the weather? Using a public API with PowerApps (part 2)

This entry is part 3 of 3 in the series OpenAPI
Send to Kindle

Introduction

Hi again

This is the second half to a post that will use the OpenWeatherMap API in PowerApps. The business scenario is around performing inspections. In the first post I gave the example of a park ranger or plant operator, both conducting inspections where weather conditions can impact the level of danger or the result of the inspection. In such a scenario it makes sense to capture weather conditions when a photo is taken.

PowerApps has the ability to capture location information such as latitude and longitude, and public weather API’s generally allow you to get weather conditions for a given location. So the purpose of these posts is to show you how you can not only capture this data in PowerApps, but then send it to SharePoint in the form of metadata via Flow.

In Part 1, we got the painful stuff out of the way – that is, getting PowerApps to talk to the OpenWeather web service via a custom connector. Hopefully if you got through that part, you now have a much better understanding of the purpose of the OpenAPI specification and can see how it could be used to get PowerApps to consume many other web services. Now we are going to actually build an app that takes photos and captures weather data.

App prerequisites…

Now to get through this post, we are going to do this is to leverage a proof of concept app I built in a separate post. This app was also an inspection scenario, allowing a user to take a bunch of photos, which were then sent to SharePoint via Flow, with correctly named files. If you have not read that post, I suggest you do so now, because I am assuming you have that particular app set up and ready to go.

Go on… its cool, I will wait for you… Smile

Seriously now, don’t come back until you can do what I show below. On the left is PowerApps, taking a couple of photos, and on the right is the photos now in a SharePoint document library.

image  image

Now if you have performed the tasks in the aforementioned article, not only do you have a PowerApp that can take photos, you’ll have a connection to Flow and ready to go (yeah the pun was intended).

First up, lets recap two key parts of the original app.

1. Photo and file name…

When the camera was clicked, a photo was taken and a file name was generated for it. The code is below:

Collect(PictureList,{

        Photo: Camera1.Photo,

        ID: Concatenate(AuditNumber.Text,”-“,Text(Today(),”[$-en-US]dd:mm:yy”),”-“,Text(CountRows(PictureList)+1),”.jpg”)

} )

This code created an in-memory table (a collection named PictiureList) that, when a few photos are taken, looks like this:

image

2. Saving to Flow

The other part of the original app was saving the contents of the above collection to Flow. The Submit button has the following code…

UpdateContext( { FinalData : Concat(PictureList, ID & “|” & Photo & “#”) } );
UploadPhotostoAuditLib.Run(FinalData)

The first line takes the PictureList collection above and munges it into a giant string called FinalData. Each row is delimited by a hash “#” and each column delimited by a pipe “|”. The second line then calls the Flow and passes this data to it.

Both of these functions are about to change…

Getting the weather…

The first task is to get the weather. In part 1 we already created the custom connector to the service. Now it is time to use it in our app by adding it as a data source. From the View menu, choose Data Sources. You should see your existing data source that connects to Flow.

image  image

Click Add data source and then New connection. If you got everything right in part 1, you should see a data source called OpenWeather. Click on it, and you will be asked to enter an API key. You should have this key from part 1 (and you should understand exactly why you were asked for it at this point), so go ahead, add it here and click the Create button. If all things to go plan, you will now see OpenWeather added as a data source.

image  image  image  image

Now we are connected to the API, let’s test it by modifying the code that takes a photo. Instead of just capturing the photo and generating a file name, let’s also grab the latitude, longitude from PowerApps, call the API and collect the current temperature.

First here is the code and then I will break it down…

image

UpdateContext( { Weather: OpenAPI.GetWeather(Location.Latitude,Location.Longitude,”metric”) } );
Collect(PictureList,
{

     Photo: Camera1.Photo,

     ID: Concatenate(AuditNumber.Text,”-“,Text(Today(),”[$-en-US]dd:mm:yy”),”-“,Text(CountRows(PictureList)+1),”.jpg”),

     Latitude:Location.Latitude,

     Longitude:Location.Longitude,

     Temp:Weather.main.temp } )

 

The first line is where the weather API is called: OpenAPI.GetWeather(Location.Latitude,Location.Longitude,”metric”) . The parameters Location.Latitude and Location.Longitude come straight from PowerApps. I want my temperature in Celsius so I pass in the string “metric” as the 3rd parameter.

My API call is then wrapped into an UpdateContext() function, which enables us to save the result of the API call into a variable I have called Weather.

Now if you test the app by taking photos, you will notice a couple of things. First up, under variables, you will now see Weather listed. Drill down into it and you will see that a complex data structure is returned by the API. In the example below I drilled down to Weather->Main to find a table that includes temperature.

image

image  image

The second line of code (actually I broke it across multiple lines for readability) is the Collect function which, as its title suggests, creates collections. A collection is essentially an in-memory data table and the first parameter of Collect() is simply the name of the collection. In our example it is called PictureList.  The second second parameter is a record to insert into the collection. A record is a comma delimited set of fields and values inside curly braces. eg: { Title: “Hi”, Description: “Greetings” }. In our example, we are building a table consisting of:

  • Photo
  • File name for Photo
  • Latitude
  • Longitude
  • Temperature

The last parameter is the most interesting, because we are getting the temperature from the Weather variable. As this variable is a complex data type, we have to be quite specific about the value we want. I.e. Weather.main.temp.

Here is what the PictureList collection looks like now. If you have understood the above code, you should be able to extend it to grab other interesting weather details like wind speed and direction.

image

Getting ready for Flow…

Okay, so now let’s look at the code behind the Submit button. The change made here is to now include the additional columns from PictureList into my variable called FinalData. If this it not clear then I suggest you read this post or even Mikael Svenson’s work where I got the idea…

image

UpdateContext( { FinalData : Concat(PictureList, ID & “|” & Photo & “|” & Latitude & “|” & Longitude & “|” & Temp & “#”) } );
UploadPhotosToAuditLib.Run(FinalData)

So in case it is not clear, the first line munges each row and column from PictureList into a giant string called FinalData. Each row is delimited by a hash “#” and each column delimited by a pipe “|”. The second line then calls the Flow and passes it FinalData.

At this point, save your changes to PowerApps and publish as you are done here. Let’s now make some changes to the SharePoint document library where the photos are currently being uploaded to. We will add columns for Temperature, Latitude and Longitude and I am going to assume you know enough of SharePoint to do this and will paste a screenshot of the end in mind…

image  image

Right! Now it is time to turn our attention to Flow. The intent here is to not only upload the photos to the document library, but update metadata with the location and temperature data. Sounds easy enough right? Well remember how I said that we got rid of most of the painful stuff in part 1?

I lied…

Going with the Flow…

Now with Flow, it is easy to die from screenshot hell, so I am going to use some brevity in this section. If you played along with my pre-requisite post, you already had a flow that more or less looks like this:

  1. A PowerApps Trigger
  2. A Compose action that splits the photo via hash: @split(triggerbody()[‘ProcessPhotos_Inputs’],”#”)
  3. An Apply to each statement with a condition inside @not(empty(item()))
  4. A Compose action that grabs the file name: @split(item(),’|’)[0]
  5. A Compose action that grabs the file contents and converts it to binary: @dataUriToBinary(@split(item(),’|’)[1])
  6. A SharePoint Create File action that uploads the file to a document library

The image below illustrates the basic layout.

image

Our task is to now modify this workflow to:

  1. Handle the additional data sent from PowerApps (temperature, latitude and longitude)
  2. Update SharePoint metadata columns on the uploaded photos with this new data.

As I write these lines, Flow has very poor support for doing this. It has been acknowledged on UserVoice and I know the team are working on improvements. So the method I am going to use here is essentially a hack and I actually feel a bit dirty even suggesting it. But I do so for a couple of reasons. Firstly, it helps you understand some of the deeper capabilities of Flow and secondly, I hope this real-world scenario is reviewed by the Flow team to help them understand the implications of their design decisions and priorities.

So what is the issue? Basically the flow actions in SharePoint have some severe limitations, namely:

  • The Create File action provides no way to update library metadata when uploading a file
  • The Create Item action provides access to metadata but only works on lists
  • The Update Item action works on document libraries, but requires the item ID of the document to do so. Since Create File does not provide it, we have no reference to the newly created file
  • The Get Items function allows you to search a list/library for content, but cannot match on File Name (actually it can! I have documented a much better method here!)

So my temporary “clever” method is to:

  1. Use Create File action to upload a file
  2. Use the Get Items action to bring me back the metadata for the most recently created file in the library
  3. Grab the ID from step 2
  4. Use the Update Item action to set the metadata on the recently uploaded image.

Ugh! This method is crude and I fear what happens if a lot of flows or file activity was happening in this library and I really hope that soon the next section is soon redundant…

Okay so let’s get started. First up let’s make use of some new Flow functionality and use Scopes to make this easier. Expand the condition block and find the Compose action that extracts the file name. If you dutifully followed my pre-req article it will be called “Get File Name”. Above this, add a Scope and rename it to “Get File and Metadata”. Drag the “Get File Name” and “Get File Body” actions to it as shown below.

image  image  image

Now let’s sort out the location and temperature data. Under “Get File Body”, add a new Compose action and rename it to “Get Latitude”. In the compose function add the following:

Under “Get Latitude”, add a new Compose action and rename it to “Get Longitude”. In the compose function add the following:

Under “Get Longitude”, add a new Compose action and rename it to “Get Temperature”. In the compose function add the following:

  • @split(item(),’|’)[4]

This will result in the following:

image  image

Now click on the Get File and Metadata scope and collapse it to hide the detail of metadata extraction (now you know what a scope is for Smile)

image

So now we have our metadata, we need to apply it to SharePoint. Under the “Create File” action, add a new scope and call it “Get Item ID”. This scope is where the crazy starts…

Inside the scope, add a SharePoint – Get Items action. Enter the URL of your site collection and in the name (not URL) of your document library. In the Order By field, type in Created desc and set the Maximum Get Count to 1. Basically this action is calling a SharePoint list web service and “Created desc” says “order the results by Created Date in descending order (newest first).

Actually what you do is set Filter Query to FileLeaf eq ‘[FileName]’ as described in this later post!

Now note the plural in the action: “Get Items”. That means that by design, it assumes more than 1 result will be returned. The implication is that the data will comes back as an array. in JSON land this looks like the following:

[ { “Name”: “Value” }, { “Name”: “Value2” }, { “Name”: “Value3” } ]

and so on…

Also note that there is no option in this action to choose which fields we want to bring back, so this action will bring back a big, ugly JSON array back from SharePoint containing lots of information.

Both of these caveats mean we now have to do some data manipulation. For a start, we have to get rid of the array as many Flow actions cannot handle them as data input. Also, we are only interested in the item ID for the newly uploaded photo. All of the other stuff is just noise. So we will add 3 more flow actions that:

  1. clear out all data apart from the ID
  2. turn it from an array back to a regular JSON element
  3. extract the ID from the JSON.

For step 1, under the “Get items” action just added, add a new Data Operations – Select action. We are going to use this to select just the ID field and delete the rest. In the From textbox, choose the Value variable returned by the Get Items action. In the Map field, enter a key called “ID” and set the value to be the ID variable from the “Get Items” action.

image

For step 2, under the “Select” action, add a Data Operations – Join action. This action allows you to munge an array into a string using a delimiter – much like what we did in PowerApps to send data to Flow. Set the From text box to be the output of the Select action. The “Join with” delimiter can actually be anything, as the array will always have 1 item. Why? In the Get Items action above, we set the Maximum Get Count to 1. We will always get back a single item array.

image

The net effect of this step will be the removal of the array. I.e., from:

[ { “ID”: 48 } ]

to

{ “ID”:48 }

For step 3, under the “Join” action, add a Data Operations – Parse JSON action. This action will process the JSON file and each element found will be available to subsequent actions. The easiest way to understand this action is to just do it and see the effect. First, set the Content textbox to the output from the Join action.

image

Now we need to tell this action which elements that we are interested in. We already know that we only have 1 variable called ID because of the Select action we set up earlier that has stripped everything else out. So to tell this action we are expecting an ID, click the “use sample payload…” link and paste some sample JSON in our expected format…

{

    “ID”:48

}

If all goes to plan, a Schema has been generated based on that sample data that will now allow us to grab the actual ID value.

image  image

Okay, so we are done with the Get Item ID scope, so collapse it as follows…

image

Finally, under the “Get Item ID” scope, add a SharePoint – Update Item action. Add the URL of your site collection and then specify the document library where the photos were uploaded to. If this has worked, the additional metadata columns should now be visible as shown in the red box below. Now set the specific item we want to update by setting the ID parameter to the ID variable from the Parse JSON step.

image

Now assign the metadata to the library columns. Set Latitude to the output variable from the Get Latitude step, Longitude to the output variable from the Get Longitude step and Temperature to the output variable from the Get Temperature step as shown below.

image

Now save your flow and cross all fingers and toes…

Testing and conclusion!

Return to PowerApps (in the browser or on your mobile device – not the PowerApps studio app). Enter an audit number and take some photos… Wohoo! Here they are in the library along with metadata. Looks like I need to put on a jacket when I step outside Smile

image   image

So taking a step back, we have managed to achieve quite a lot here.

  1. We have wired up a public web service to PowerApps
  2. We have used PowerApps built-in location data to load weather data each time a photo has been taken
  3. We have used Flow to push this data into SharePoint and included the location and weather data as parameters.

Now on reflection there are a couple of massive issues that really prevent this from living up to its Citizen Developer potential.

  1. I had to use a 3rd party service to generate my OpenAPI file and it was not 100%. Why can’t Microsoft provide this service?
  2. Flow’s poor support for common SharePoint scenarios meant I had to use (non) clever workarounds to achieve my objectives.

Both of these issue were resolvable, which is a good thing, but I think the solutions take this out of the realm of most citizen developers. I had to learn too much JSON, too much Swagger/OpenAPI and delve deep into Flow.

In saying all that, I think Flow is the most immature piece of the puzzle at this stage. The lack of decent SharePoint support is definitely one where if I were a program manager, I would have pushed up the release schedule to address. It currently feels like the developer of the SharePoint actions has never used SharePoint on a day to day basis, and dutifully implemented the web services without lived experience of the typical usage scenarios.

For other citizen developers, I’d certainly be interested in how you went with your own version and variations of this example, so please let me know how you go.

And for Microsoft PowerApps/Flow product team members who (I hope) are reading this, I think you are building something great here. I hope this material is useful to you as well.

 

Thanks for reading

 

Paul Culmsee

www.hereticsguidebooks.com

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

A Filename Generation Example for PowerApps with Flow

This entry is part 1 of 3 in the series OpenAPI
Send to Kindle

Hiya

A client recently asked to make a PowerApps proof of concept audit app for safety inspections. The gist was that a user would enter an audit number into the App, take a bunch of photos and make some notes. The photos needed to be named using the convention: <Audit Number>-<Date>-<Sequence Number>. Eg

  • 114A3-13:04:17-3.jpg is Audit number 114A3, photo taken on 13th of April and it was the 3rd photo taken.
  • 114A6-14:04:17-7.jpg is Audit number 114A3, photo taken on 14th of April and it was the 7th photo taken.

As I type these lines, it is still not possible to save pictures directly into SharePoint from PowerApps, so we are going to build on the method that Mikael Svenson documented. But first let’s look at this file name requirement above. While it seems straightforward enough, if you are new to PowerApps this might take a while to figure out. So let’s quickly build a proof of concept app and show how this all can be achieved.

In this post I will build the app and then we will use Microsoft Flow to post the images to a SharePoint document library. I’ll spend a bit of time on the flow side of things because there are a few quirks to be aware of.

First up create a blank app and then add a text box, camera, gallery and two buttons to the form… Rename the text box control to “Auditnumber” and arrange them similar to what I have done below…

image

First up, let’s disable the submit button until an audit number is entered… Without an audit number we cannot generate a filename. To do this, set the Disabled property of the button labelled “Submit” to AuditNumber.Text=””. This means that while the audit number text box is blank, this formula will return “true” and disable the button.

image

image  image

Now let’s set things up so that when a photo is taken, we save the photo into a Collection. A collection is essentially an in-memory data table and I find that once you get your head around collections, then it opens up various approaches to creating effective apps.

On the camera control (assuming it is named Camera1), set the OnSelect property to “Collect(PictureList,{ Photo: Camera1.Photo, ID: CountRows(PictureList)+1 } )

image

Now if you are a non developer, you might think that is a bit ugly, so let’s break it down.

  • The collect function creates/populates a collection – and the first parameter is simply the name of the collection. You could call this anything you like, but I chose to call mine PictureList.
  • The second parameter is a record to insert into the collection. This consists of a comma delimited set of fields and values inside curly braces. eg: { Photo: Camera1.Photo, ID: CountRows(PictureList)+1 }

Now that whole curly brace thing is a bit ugly so let’s break it down further. First up here is an image straight from Microsoft’s own documentation. You can see a record is a row of fields – pretty standard stuff if you are familiar with databases or SharePoint list items.

In PowerApps formulas, a record can be specified using curly braces. So { Album: “Thriller”, Price: 7.99 } is a record with 2 fields, Album and Price.

Now take a look at the record I used: { Photo: Camera1.Photo, ID: CountRows(PictureList)+1 } . This shows two fields, Photo and ID. The values for the Photo field is Camera1.Photo, which holds the latest photo taken by the camera. The ID field is a unique identifier for the photo. I generated this by calling the CountRows function, passing it the PhotoList collection and then adding 1 to the result.

So here’s what happens when this app starts:

  • The PhotoList collection is empty
  • A user clicks the camera control. A photo is taken and stored in the Camera1.Photo property.
  • I then count the number of records in the PhotoList collection. As it is currently empty, it returns 0. We add 1 to it
  • The photo and an ID value of 1 is added to the PhotoList collection
  • A user clicks on the camera control again. A photo is taken and stored in the Camera1.Photo property.
  • I then count the number of records in the PhotoList collection. As it is currently has 1 record from steps 1-4, it returns 1. We add 1 to it
  • The photo and an ID value of 2 is added to the PhotoList collection

… and so on. So let’s test this now…

Press play to test your app and take a couple of photos. It may not look like anything is happening, but don’t worry. Just exit and go to the File Menu and choose “Collections”. If everything has gone to plan, you will now see a collection called PictureList with the Photo and ID columns. Yay!

image

So next let’s bind this collection to the Gallery so we can see photos taken. This part is easy. On the Gallery you added to the page, set the Items property to PictureList (or whatever you named your collection). Depending on the type of gallery you added, you should see data from the PictureList collection. In my case, I changed the gallery layout to “Image and Title”. The ID field was bound to the title field and now you can see each photo and its corresponding ID.

image  image  image

Now let’s provide the ability to clear the photo gallery. On the button labelled “Clear”, set the OnSelect property to “Clear(PictureList)”. The clear command does exactly what it suggests: clears out all items from a collection. Now that we have the gallery bound, try it out. You can take photos and then clear them to your hearts content.

Now the point of this exercise is to generate a nice filename. One way is by modifying the record definition we were using: eg

From this:

  • Collect(PictureList,{ Photo: Camera1.Photo, ID: CountRows(PictureList)+1 })

To this crazy-ass looking formula:

  • Collect(PictureList,{ Photo: Camera1.Photo, ID: Concatenate(AuditNumber.Text,”-“,Text(Today(),”[$-en-US]dd:mm:yy”),”-“,Text(CountRows(PictureList)+1),”.jpg”) } )

To explain it, first check out the result in the gallery. Note my unique identifier has been replaced with the file-name I needed to generate.

image

So this formula basically uses the Concatenate function to build the filename in the format we need. Concatenate takes a list of strings as parameters and munges them together. In this case, it takes:

  • The audit number from the textbox – Auditnumber.Text
  • A hyphen – “-“
  • Todays date in dd:mm:yy format and converts it to text – Text(Today(),”[$-en-US]dd:mm:yy”))
  • Another hyphen – “-“
  • The row count for PictureList collection, adds 1 to it and converts the result to text – Text(CountRows(PictureList)+1)
  • The file type – “.jpg”

The net result is our unique filename for each photo.

Now we have one final step. We are going to send the entire collection of photos and their respective file names to flow to put into a SharePoint Library. The method we will use will take the PictureList collection, and save it as one giant string. We will send the string to Flow, and have Flow then pull out each photo/filename pair. Mikael describes this in detail in his post, so I will just show the code here, which we will add to the onSelect property of the submit button.

UpdateContext( { FinalData: Concat(PictureList, ID & “|” & Photo & “#”) } )

image

So what is this formula doing? Well working from inside to out, the first thing is the use of the Concat function. This function does something very useful. It works across all the records of a PowerApps collection and returns a single string. So Concat(PictureList, ID & “|” & Photo & “#”) takes the file name we generated (ID), joins it to a pipe symbol “|” and joins that to the photo, and then adding a hash “#”. The UpdateContext function enables us to save the result of the Concat into a variable called FinalData.

If we test the app by taking some photos and clicking the submit button, we can see what the FinalData variable looks like by the Variable menu as shown below. Our table is now one giant text string that looks like:

“filename 1 | encoded image data 1 # filename 2 | encoded image data 2 # filename x | encoded image data x “

image

Now a quick caveat, avoid using PowerApps desktop client to do this. You should use PowerApps web creator due to an image encoding bug.

Anyway, let’s now move to Flow to finish this off. To do this, click on the Submit button on your app and choose Flows from the Action menu and on the resulting side panel choose to Create a new flow.

image  image

A new browser tab will open and sign you into Microsoft flow, and be nice enough to create a new flow that is designed to be triggered from PowerApps. Rename the flow to something more meaningful like “Upload Photos to Audit Lib” and then click the New Step button, and add a new Action. In the search bar, type in “Data Operations” and in the list of actions, choose the “Compose” action…

image  image

Okay so at this point I should a) explain what the hell we are going to do and b) credit Mikael Svenson for first coming up with this method. In relation to the first point, PowerApps is going to send flow a giant string of photos and filenames (remember the FinalData variable – that’s what flow will receive). If you recall with the FinalData, each photo/filename pair is delimited by a hash “#” and the file name is delimited by a pipe “|”. So we need to take what PowerApps sends, and turn it back into rows. Then for each row, we grab the file name, and the file content and upload it to our friendly neighbourhood SharePoint library.

Sounds easy right?

Our first step is to use the compose action we just added to split the data from PowerApps back into photos. This particular workflow action does something really neat. It executes workflow definition language formulas. What are these? Well workflow definition language is actually a part of another Microsoft product called Azure Logic Apps. Flow leverages Azure Logic Apps, which means this language is available to us. Sounds complex? Well yeah, it does at first but when you think about it this is not new. For example, MS Teams, Groups and Planner use SharePoint behind the scenes.

Anyway, the point is that there are several workflow definition language functions we can use, namely:

  • Split() –  Takes a string, and splits it up into smaller strings based on a delimiter
  • Item() – This is used to to return an element of an array. Eg if we use the split command above, item() will refer to each smaller string
  • dataUriToBinary() – This takes an image encoded and turns it back into binary form.

Okay enough talk! Let’s fill in this compose action. First up (and this is important), rename the action to something more meaningful, such as “ProcessPhotos”. After renaming, click on the text box for the Compose action and a side panel will open, showing a PowerApps label and a box matching the PowerApps logo called Body. Click the Body button and the compose textbox should have a value called ProcessPhotos_Inputs as shown in the 3 images below…

image

image

image

So what have we just done? Essentially we told the Compose method to ask PowerApps to supply data into a variable called ProcessPhotos_Inputs. In fact, let’s test this before going any further by saving the flow.

Switch back to PowerApps, click the Submit button and select the onSelect method we used earlier. You should still see the function where we created the FinalData variable. Copy the existing function to the clipboard, as you’re about to lose everything you typed in. Now click the Flow once and it should say that it’s adding to PowerApps.

At this point, the function you so painstakingly put together has been replaced by a reference to the Flow. Delete what’s been added and paste the original function back. Add a semicolon to the end of the function (which tells PowerShell that another command is coming) and then press SHIFT+ENTER to insert a newline. Now type in the name of your Flow and it should be found via auto-complete. Click the matching flow with .Run on the end. Add a left bracket to the end of the flow and it will ask for an input parameter. If you have done it right, that parameter will be called ProcessPhotos_Inputs. Note that it matches the parameter from above image. The parameter we are passing is the FinalData variable we constructed earlier.

image   image

image

Okay so basically we have wired up PowerApps to flow and confirmed that Flow is asking PowerApps for the correct parameter. So let’s now get back to Flow and finish the first action. If you closed the Flow tab in your browser, you can start a new flow editing session from within PowerApps. Just click the ellipsis next to your flow and click Edit.

image

Right! So after that interlude, lets use the first function of interest – split(). In the text box for your compose function, modify it to look like the string below. Be sure to put the whole thing in quotes because Flow is going to try and be smart and in doing so, make things really counter intuitive and hard to use. Don’t be surprised if your ProcessPhotos_Inputs box disappears. Just add it back in again via the “dynamic content” button.

image

In fact, save and close the flow at this point and then edit it again. You will now see what the real function looks like. Note how the ProcessPhotos_Input has magically changed to {@triggerbody()[‘ProcessPhotos_Inputs’]}.

image

Unfortunately this sort of magic will not actually work… there are a few too many curly braces and excessive use of “@” symbols. So replace the function with the following (include quotes):

  • “@split(triggerBody()[‘ProcessPhotos_Inputs’], ‘#’)”

Like I said… flow is trying to be smart, only its not Smile. If you have done things right the action looks like the screen below. Double check this because if you do not have the quotes right, it will get messy. In fact if you save the flow and re-open it the quotes will disappear. This is actually a good sign…

image

After saving the flow, closing an re-opening… look Ma, no quotes!

image

Now for the record, according to the documentation,  the triggerbody() function is a reference function you use to “reference outputs from other actions in the logic app or values passed in when the logic app was created. For example, you can reference the data from one step to use it in another”. This makes sense – as ProcessPhotos_Inputs is being passed from the PowerApps trigger step to this compose function we are building.

In any event, let’s add the next workflow step. The output of the ProcessPhotos step should be an array of photos, so now we need to process each photo and upload it to SharePoint. To do this, click on “New step”, and from the more button, choose “Add an apply to each” action. In the “Select an output from previous steps” action, click “Add dynamic content” and choose the output from the ProcessPhotos step as shown in the sequence of images below.

image   image

image

Next click “Add a condition” and choose the option “Edit in advanced mode”. Replace any existing function with :

  • @not(empty(item()))

image

image

The item() function is specifically designed for a “repeating action” such as our “apply to each” event. It returns the item that is in the array for this iteration of the action. We are then using the empty() function to check if this item has any data in it and then via the not() function so we can only take action of the item has data. In case you are wondering why I need to test this, the data that comes from PowerApps has an extra blank row and this effectively filters it out.

The resulting screen should look like this:

image

At this point we should have a file name and file body pair, delimited by a pipe symbol. So let’s add an action to get the file name first. On the “If Yes” condition, click “Add an action” and choose another Compose Action. Rename the action to “Get File Name” and enter the function:

  • “@split(item(),’|’)[0]”

image  image

The square brackets are designed to return a value from an array with a specific index. Since our split() function will return an array of two values, [0] tells Flow to grab the first value.

Now let’s add an action to get the file body. Click “Add an action” and choose another Compose Action. Rename the action to “Get File Body” and enter the function:

  • “@dataUriToBinary(split(item(),’|’)[1])”

image

Looking at the above function, the split() side of things should be easy to understand. We are now grabbing the second item in the array. Since that item is the image in an encoded format, we are then passing it to the dataUriToBinary() to turn it back into an image again. Neat eh?

Now that we have our file name and file body, let’s add the final action. This time, we will choose the “Create File” action from the SharePoint connector. Rename the action to “Upload to Library” and enter the URL of the destination SharePoint site. If you have permission to that site, you can then specify which library to upload the photo to by browsing the Folder Path.

image  image

Now comes the final steps. For the File Name, click the “Add dynamic content” button and add the Output from the Get File Name step (now you know why we renamed it earlier). Do the same for the File Content textbox by choosing the output from the Get File Body step.

image

Right! We are ready to test. Sorry about screenshot hell in the Flow section, but I am writing this on the assumption you are new to it, so I hope the extra detail helped. To test, use PowerApps on your phone or via the web browser, as the PowerApps desktop tool has a bug at the time of writing that will prevent the photos being sent to Flow in the right format.

Now it is time to test.

So check your document library and see if the photos are there! In the example below I took a photo of my two books for blatant advertising purposes Smile . The 3rd image is the document library showing the submitted photos!

image   image

image

Finally, here are some tips for troubleshooting along the way…

First up if you are having trouble with your flows, one lame but effective way to debug is to add a “Send an email” action from the Ofifce365 Outlook Connector to your flow. This is particularly handy for encoded images as they can be large and often in the flow management tools, you will see a “value too big to display” error. This method is crude, and there are probably better approaches, but it is effective. In the image below you can see how I have added to action below my ProcessPhotos action prior to splitting it into an array.

image   image

Another thing that has happened to me (especially when I rename flow actions) is a disconnect between the trigger connection between Flow and PowerApps. Consider this error…

image  image

If your flow fails and you see an error like

“Unable to process template language expressions in action [one of your actions] inputs at line ‘1’ and column ‘1603’: ‘The template language expression ‘json(decodeBase64(triggerOutputs().headers[‘X-MS-APIM-Tokens’]))[‘$connections’][‘shared_office365’][‘connectionId’]’ cannot be evaluated because property ‘shared_office365′ doesn’t exist, available properties are ”. Please see https://aka.ms/logicexpressions for usage details.’

.. the connection between PowerApps and Flow has gotten screwed up.

To resolve this, disconnect your flow from PowerApps and reconnect it. Your flow will be shown as a data source which can easily be removed as shown below.

image

Once removed I suggest you go back to your submit button and copy your function to the clipboard. This is because adding the flow back will wipe the contents of the button. With your button selected, click the Flows button from the Action menu and then click on the Flow to re-associate it to PowerApps. The third image shows the onSelect property being cleared and replaced with the flow instantiation. This is where you can re-paste your original code (4th image)…

image

image

image

image

Finally, another issue you may come across is where no pictures come through, and instead you are left with an oddly named file, which (if you look closely) looks like one of your compose actions in Flow. You can see an example of this in the image below.

The root cause of this is similar to what I described earlier in the article where I reminded you to put all of your compose actions in quotes. I don’t have a definitive explanation for why this makes a difference and to be honest, I don’t care. The fix is easy: just make sure all of your compose actions are within quotes, and you should be cooking with gas.

Phew! Another supposedly quick blog post that got big because I decided to explain things for the newbie. Hopefully this was of use to you and I look forward to your feedback.

Paul Culmsee

www.hereticsguidebooks.com

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

A Clever-workaround for Saving Photos to SharePoint from PowerApps

Send to Kindle

At the time of writing, a common request for PowerApps is to be able to able to upload photos to SharePoint. It makes perfect sense, especially now that its really easy to make a PowerApp that is bound to a SharePoint list. Sadly, although Microsoft have long acknowledged the need in the PowerUsers forum, a solution has not been forthcoming.

I have looked at the various workarounds, such as using the OneDrive connector or a custom web API, but these for me were fiddly. Thanks to ideas from John Liu, I’ve come up with a method that is more flexible and less fiddly to implement, provided you are okay with a bit of PowerShell, and (hopefully) with PnP PowerShell. One advantage to the method is that it handles an entire gallery of photos in a single transaction, rather than just a single photo at a time.

Now in the old days I would have meticulously planned out a multi-part series of posts related to a topic like this, because I have to pull together quite a lot of conceptual threads into a single solution. But since the pace of change in the world of Office365 is so rapid, my solution may be out of date by the time I publish it. So instead I offer a single summary post of my solution and leave the rest to you to figure out.  Sorry followers, its just too hard to do epic multi-part articles these days – times have changed.

What you need

  1. An understanding of JSON and basic idea of web services
  2. An azure subscription
  3. Access to Azure functions
  4. The PnP Powershell cmdlets
  5. A Swagger file (don’t worry if this makes no sense now)
  6. To be signed up to PowerApps

 How we are going to solve this…

In a nutshell, we will create an Azure function, using PowerShell to receive photos from PowerApps and uploads them to a SharePoint library. Here is my conceptual diagram that I spent hours and hours drawing…

Snapshot

To do this we will need to do a few things.

  1. Customise PowerApps to store photo data in the way we need
  2. Create and configure our Azure function
  3. Write and test the PowerShell code to upload to SharePoint
  4. Create a Swagger file so that PowerApps can talk to our Azure function
  5. Create a custom PowerApps connection/datasource use the Azure function
  6. Test successfully and bask in the glory of your awesomeness

Step 1: Customise PowerApps to store photo data in the way we need

Let’s set up a basic proof of concept PowerApp. We will add a camera control to take photos, a picture gallery to view the photos and a button to submit the photos to SharePoint. I’ll use the PowerApps desktop client rather than the web page for this task and create a blank app using the Phone Layout.

image

From the Insert menu, add a Camera control from the Media dropdown to add it to the screen. Leave it up near the top…

image

From the Insert Menu, add a Gallery control. For my demo I will use the vertical gallery. Move it down below the camera control so it looks like the second image below.

image  image

From the Insert Menu, add two buttons below the gallery. Set the text property on one to “Submit” and the other to “Clear”. I realise the resulting layout will not win any design awards but just go with it. Use the picture below to guide you.

image    image

Now let’s wire up some magic. Firstly, we will set it up so clicking on the camera control will take a photo, and save it to PowerApps storage. To do this we will use the Collect function. Assuming your Camera control is called “Camera1”, select it , and set the OnSelect property to:

Collect(PictureList,Camera1.Photo)

image

Now when a photo is taken, it is added to an in-memory PowerApps data-source called PictureList. To see this in action, preview the PowerApp and click the camera control a couple of times. Exit the preview and choose “Collections” from the left hand menu. You will now see the PictureList collection with the photos you just took, stored in a field called Url. The reason it is called URL and not “Photo” will become clear later).

image

Now let’s wire up the clear button to clear out this collection. Choose the button labelled “Clear” and set its OnSelect property to:

Clear(PictureList)

image

If you preview the app and click this button, you will see that the collection is now empty of pictures.

The next step is to wire up the Gallery to the PictureList collection so that you can see the photos being taken. To do this, select the gallery control and set the Items property to PictureList as shown below. Preview this and you should be able to take a set of photos, see them added to the gallery and be able to clear the gallery via the button.

image

image

Now we get to a task that will not necessarily make sense until later. We need to massage the PictureList collection to get it into the right format to send to SharePoint. For example, each photo needs a filename, and in a real-world scenario, we would likely further customise the gallery to capture additional information about each photo. For this post I will not do this, but I want to show you how you can manipulate data structures in PowerApps. To do this, we are going to now wire up some logic to the “Submit” button. First I will give you the code before I explain it.

Clear(SubmitData);
ForAll(PictureList,Collect(SubmitData, { filename: "a file.jpg", filebody: Url }))

image

In PowerApps, it is common to add multiple statements to controls, separated by a semicolon. Thus, the first line above initialises a new Collection I have called “SubmitData”. If this collection already had data in it, the Clear function will wipe it out. The second line uses two functions, ForAll and the previously introduced Collect. ForAll([collection],[formula]) will iterate through [collection] and perform tasks specified in [formula]. In our case we are adding records to the SubmitData collection. Each record consists of two fields and is in JSON format – hence the curly braces. The first field is called filename and the second is called filebody. In my example the filename is a fixed string, but filebody grabs the Url field from the current item in PictureList.

To see the effect, run the app, click submit and then re-examine the collections. Now you will see two collections listed – the original one that captures the photos from the camera (PictureList), and the one called SubmitData that now has a field for filename and a field called filebody with the photo. I realise that setting a static filename called “a file.jpg” is not particularly useful to anybody, and I will address this a little later, the point is we now have the data in the format we need.

image

By the way, behind the scenes, PowerApps stores the photo in the Data URI scheme. This is essentially a Base64 encoded version of the image with a descriptor at the start that is included in HTML. When you think about it, this makes sense in some situations because it reduces the number of HTTP round trips between browser and server. For example here is a small image encoded and embedded direct in HTML using the technique.

<img src="data:image/png;base64,iVBORw0KGgoAAA
ANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4
//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU
5ErkJggg==" alt="Red dot" />

The implication of this format is when PowerApps talks to our Azure function, it will send this sort of JSON…

[ {  "filename": "boo.jpg",
"filebody": "data:image/jpeg;base64,/9j/4AAQSkZJR [ snip heaps of Base64 ] T//Z" },
{  "filename": "boo3.jpg",
"filebody": "data:image/jpeg;base64,/9j/TwBAQMEBA [ snip heaps of Base64 ] m22I" } ]

In the next section we will set up an Azure Function and write the code to handle the above format, so save the app in its current state and give it a nice name. We are done with PowerApps for now…

2. Create and Configure Azure Function

Next we are going to create an Azure function. This is the bit that is likely new knowledge for many readers. You can read all about them on their Azure page, but my quick explanation is that they allow you to take a script or small piece of code and turn it into a fully fledged webservice. As you will soon see, this is very useful indeed (as well as very cost effective).

Now like many IT Pros, I am a PowerShell hacker and I have been using the PowerShell PnP libraries for all sorts of administrative purposes for quite some time. In fact if you are administering an Office365 tenant and you are not using PnP, then I can honestly say you are missing out on some amazing time-saving toolsvand you owe it to yourself to skill-up in this area. Of course, I realise that many readers will not be familiar with PowerShell, let alone PnP, and I expect some readers have not done much coding at all. Luckily the code we are going to use is just a few lines and I think I can sufficiently explain it.

But we are getting ahead of ourselves, let’s create the Azure function and then revisit PowerShell. Assuming you have an Azure subscription, visit functions.azure.com and log in. If not, sign up for the free account and then create a function app to host the function. I called mine MyFunctionsDemo but yours will have to be something different. This will take minute or two to complete and you will be redirected to the Azure functions portal.

image   image

Once the web application to host your functions is created, Click the + next to the Functions button to create a new function. PowerShell is still in preview, so you have to click the option to create a custom function. On the next screen, in the Language dropdown, choose PowerShell.

image  image

Our function is going to be triggered from PowerApps when a user clicks the submit button. PowerApps will make a HTTP request so this is a HttpTrigger scenario. Click the HttpTrigger-PowerShell template, give it a name (I called mine PhotoSendSP) and click the Create button. If all goes to plan you will be presented with a screen with some basic PowerShell code… essentially a “Hello World” web service.

image   image

Let’s test this newly minted Azure function before we customise it. If you look to the right of the screen above, you will see a “Test” vertical label. Click it and you will be presented with a screen that allows you to craft some data to send to your shiny new function. You can see that the test is going to be a HTTP POST by default. As you can see below, there is a basic JSON entry with a single name/value pair “name”: “Azure”. Change the Azure string to something else and then click the Run button. The result will be displayed below the JSON as shown below.

image   image

Now let’s take a quick look at the PowerShell code provided to you by default. Only lines 2, 3 and 11 matter for our purposes. What lines 2 and 3 show is that all of the details that are posted to this webservice are stored in a variable called $req. Line 2 converts this to JSON format and stores that in a variable called $requestbody. Line 3 then asks $requestbody for the value of “name”, which is you look in the screenshots above are what you set in the test. Line 11 then outputs this line to a variable called $res, which is the response back to the caller of this webservice. In this case you can see it returns “Hello “ with $name appended to it.

image
Now that we have seen the PowerShell code, let’s now update it with code to receive data from PowerApps and send it to SharePoint.

3. Write and test the PowerShell code that uploads to SharePoint

If you recall with PowerApps, the data we are sending to SharePoint is one or more photos. The data will look like this…

[ {  "filename": "boo.jpg",
"filebody": "data:image/jpeg;base64,/9j/4AAQSkZJR [ snip heaps of Base64 ] T//Z" },
{  "filename": "boo3.jpg",
"filebody": "data:image/jpeg;base64,/9j/TwBDAQMEBAUEBQ  m22I" } ]

In addition, for the purposes of keeping things simple, I am going to hard code various things like the document library to save the files to and not worry about exception handling. Below is my sample code with annotations at the end…

1.  Import-Module "D:\home\site\wwwroot\modules\SharePointPnPPowerShellOnline\2.15.1705.0\SharePointPnPPowerShellOnline.psd1" -Global
2.  $requestBody = Get-Content $req -Raw | ConvertFrom-Json
3.  $username = "paul@tenant.onmicrosoft.com"
4.  $password = $env:PW;
5.  $siteUrl = "https://tenant.sharepoint.com"
6.  $secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
7.  $creds = New-Object System.Management.Automation.PSCredential ($username, $secpasswd)
8.  Connect-PnPOnline -url $siteUrl -Credentials $creds
9.  $ctx = get-pnpcontext
10. $doclib = $ctx.Web.Lists.GetByTitle("Documents")
11. ForEach ($item in $requestbody)
12. {
13.    $filename = $item.filename
14.    $rawfiledata = $item.filebody
15.    $rawfiledata = $rawfiledata -replace 'data:image/jpeg;base64,', ''
16.    $bytes = [System.Convert]::FromBase64String($rawfiledata)
17.    # uses comma notation related to .net reflection http://piers7.blogspot.com.au/2010/03/3-powershell-array-gotchas.html
18.    $memoryStream = New-Object System.IO.MemoryStream (,$bytes)
19.    $FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
20.    $FileCreationInfo.Overwrite = $true
21.    $FileCreationInfo.ContentStream = $memoryStream
22.    $FileCreationInfo.URL = $filename
23.    $Upload = $doclib.RootFolder.Files.Add($FileCreationInfo)
24.    $ctx.Load($Upload)
25.    $ctx.ExecuteQuery()
26. }

 

  • Line 1 loads the PnP PowerShell module. Without this, commands like Connect-PnPOnline and Get-PnPContext will not work. I’ll show how this is done after explaining the rest of the code.
  • Lines 3-7 are all about connecting to my SharePoint online tenant. Line 4 contains a variable called $env:PW. The idea here is to avoid passwords being stored in the code in clear text. The password is instead is pulled from an environment variable that I will show later.
  • Lines 7-9 connect to a site collection and then connect to the default document library within it.
  • Line 10 looks at the data sent from PowerApps and loops around to process for each image/filename pair.
  • Lines 13-18 grabs the file name and file data. It converts the file data into a memorystream, which is a way to represent a file in memory.
  • Line 19-25 then uploads the in-memory image to the document library in SharePoint, based on the filename provided. (Note: any PnP gurus wondering why I did not use Add-PnPFile, it was because this cmdlet did not properly handle the memorystream and the images were not proper binary and always broken.)

So now that we have seen the code, lets sort out some final configuration to make this all work. A lot of the next section I learnt from John Liu and watching the excellent Office Patterns and Practices Special Interest Group webinar he recently did with my all-time SharePoint hero, Vesa Juvonen.

Installing PnP PowerShell Components

First up, none of this will work without the PnP PowerShell module deployed to the Azure Function App. The easiest way to do this is to install the PnP PowerShell cmdlets locally and then copy the entire installation up to the Azure function environment. John Liu explains this in the aforementioned webinar but in summary, the easiest way to do this is to use the Kudu tool that comes bolted onto Azure functions. You can find it by clicking the Azure function name (“MyFunctionDemo” in my case) and choosing the “Platform Features” menu. From here you will find Kudu hiding under the Development Tools section. When the Kudu tab loads, click the Debug console menu and create a CMD or PowerShell console (it doesn’t matter which)

image  image  image

We are going to use this console to copy up the PnP PowerShell components. You can ignore the debug console and focus on the top half of the screen. This is showing you the top level folder structure for the Azure function application. Click on site and then wwwroot folders. This is the folder where all of your functions are stored (you will see a folder matching the name of the module we made in step 2). What we will do is install the PnP modules at this level, so it can be used for other functions that you are sure to develop Smile.

image  image

So click the + icon to create a folder here and call it Modules. From here, drag and drop the PnP PowerShell install location to this folder. In my case C:\Program Files\WindowsPowerShell\Modules\SharePointPnPPowerShellOnline\2.15.1705.0. I copied the SharePointPnPPowerShellOnline\2.15.1705.0 folder and all of its content here as I want to be able to maintain multiple versions of PnP as I develop functions over time.

image  image

Now that you have done this, the first line of the PowerShell script will make sense. Make sure you update the version number in the Import-Mobile command to the version of PnP you uploaded.

Import-Module "D:\home\site\wwwroot\modules\SharePointPnPPowerShellOnline\2.15.1705.0\SharePointPnPPowerShellOnline.psd1"

Handling passwords

The next thing we have to do is address the issue of passwords. This is where the $env:PW comes in on line 4 of my code. You see, when you set up Azure functions application, you can create your own settings that can drive the behaviour of your functions. In this case, we have made an environment variable called PW which we will store the password to access this site collection. This hides clear text passwords from code, but unfortunately it is a security by obscurity scenario, since anyone with access to the Azure function can review the environment variable and retrieve it. If I get time, I will revisit this via App Only Authentication and see how I go. I suspect though that this problem will get “properly” solved when Azure functions support using the Azure Key Vault.

In any event, you will find this under the Applications Settings link in the Platform Features tab. Scroll down until you find the “App Settings” section and add your password in as shown in the second image below.

image  image

Testing it out…

Right! At this point, we have all the plumbing done. Let’s test to see how it goes. First we need to create a JSON file in the required format that I explained earlier (the array of filename and filebody pairs). I crafted these by hand in notepad as they are pretty simple. To remind you the format was:

[ {  "filename": "boo.jpg",
"filebody": "data:image/jpeg;base64,/9j/4AAQSkZJR [ snip heaps of Base64 ] T//Z" },
{  "filename": "boo3.jpg",
"filebody": "data:image/jpeg;base64,/9j/TwBDAQMEB [ snip heaps of Base64 ] m22I" } ]

To generate the filebody elements, I used the covers of my two books (they are awesome – buy them!) and called them HG2BP and HG2M respectively. To create the base 64 encoded images in the Data URI scheme, I went to https://www.base64-image.de and generated the encoded versions. If you are lazy and want to use a pre-prepared file, just download the one I used for testing.

h2bph2m image

To test it, all we need to do is click the right hand Test link and paste the JSON into it. Click the Run button and hope for the best! As you can see in my example below, the web service returned a 200 status which means hunky dory, and the logs showed the script executing successfully.

image

Checking my document library and they are there… wohoo!!

image

So basically we have a lot of the bits in place. We have proven that our Azure function can take a JSON file with encoded images, process that file and then save it to a SharePoint document library. You might be thinking that all we need to do now is to wire up PowerApps to this function? Yeah, so did I too, but little did I realise the pain I was about to endure…

4. Create a Swagger file so that PowerApps can talk to our Azure function

Now we come to the most painful part of this whole saga. We need to describe our Azure function using a standard called Swagger (or OpenAPI). This provides important metadata so that PowerApps can make it easy for users to consume. This will make sense soon enough, but first we have to create it, which is a royal pain in the ass. I found the online documentation for swagger to be lacking and it took me a while to understand enough of the format to get it working.

So first up to make things simpler, let’s reduce some of the complexity. Our Azure function is a simple HTTP post. We have not defined any other type of requests, so lets make this formal as it will generate a much less ugly Swagger definition. Expand your function and choose the “Integrate” option. On the next screen, under Triggers, you will find a drop down with a label “Allowed HTTP methods”. Change the default value to “Selected methods” and then untick all HTTP methods except for POST. Click Save.

image  image  image

Now click back to your function app, and choose “API Definition” from the top menu. This will take you to the screen where you create/define your Swagger file.

image

On the initial screen, set your API definition source to function if asked, and you should see a screen that looks somewhat like this…

image

Click the Generate API definition template button as suggested by the comment in the code box in the middle. This will generate a swagger file and on the right side of the screen, the file has will be used to generate a summary of your API. You can see the Url of your azure app, some information about an API key (which we will deal with later) and below that, the PhotoSendSP function exposed as a webservice (/api/PhotoSendSP).

image

Now at this point you are probably thinking “okay so its unfamiliar, but this is pretty easy”, and you would be right. Where things got nightmarish for me was working out how to understand and customise the swagger file as the template is currently incomplete. All it has done is defined our function (note the paths section in the above screenshot  – can you see all those empty square brackets? that’s what you need to now fill in).

For the sake of brevity, I am not going to describe the ins and outs of this format (and I don’t fully know it yet anyway!). What I can tell you is that getting this right is a painful and time-consuming combination of trial and error, reading the swagger spec and testing in PowerApps. Let’s hope my hints here save you some frustration.

The first step is we need to create a definition for the format of data that our Azure function accepts as input. If you look closely above, you will see a section called definitions. Paste the following into the section so it looks like the screenshot below. Note: If you see any symbol apart from a benign warning message next to the “Photos:” line, then you do not have it right!.

definitions:
   Photos:
      type: object
      required:
         - filename
         - filebody
      properties:
         filename:
            type: string
            example: image.jpg
         filebody:
            type: string

image

So what we have defined here is an object called Photos which consists of two required properties, filename and filebody. Both are assumed to be string format, and filename also has an example to illustrate what is expected. Depending on how this swagger file is consumed by another application that supports swagger, one can imagine that example showing up on online help or intellisense when our function is being called.

Now lets make use of this definition.  Paste this into the parameters and description sections. Note the “schema:” section. Here we have told the swagger file that our function is expecting an array of objects based on the Photos definition that we created earlier.

parameters:
  - name: photocollection
    in: body
    description: “The encoded files”
    required: true
    schema:
       type: array
       items:
          $ref: '#/definitions/Photos'
description: "A collection of photos and filenames"

image

Finally, let’s finish off by defining that our Azure function consumes and produces its data in JSON format. Although our sample code is not producing anything back to PowerApps, you can imagine situations where we might do something like send back a JSON array of all of the SharePoint URL’s of each photo.

produces:
- application/json
consumes:
- application/json

image

Okay, so we are all set. Now to be clear, there is a lot more to Swagger, especially if you wanted to call our function from flow, but for now this is enough. Click the Save button, and then click the button “Export to PowerApps + Flow”. You will be presented with a new panel that explains the process we are about to do. Feel free to read it, but the key step is to download the swagger file we just created.

image

Okay, so if you have made it this far, you have a swagger JSON file and you are in the home stretch. Let’s now head back to PowerApps!

5. Create a custom PowerApps connection/datasource to use the Azure function

Back in PowerApps, we need to make a new custom connection to our Azure function. Click the Connections menu item and you will be redirected to web,powerapps.com. Click “Manage Custom Connections” and then click “Create a custom connector”. This will take you to a wizard.

image   image

image

The first step is to upload the swagger file you created in step 4 and before you do anything else, rename your connector to something short and sweet, as this will make it easier when displayed in the PowerApps list of connections.

image

Scroll to the bottom the of page and click the “Continue” button. Now you are presented with some security options about your connector. For context, the default settings for the PowerShell azure function template we chose in step 2 was to use an API key, so you can leave all of the defaults here, although I like to add the more meaningful “API Key” (this will make sense soon). Click Continue

image

PowerApps has now processed the swagger file and found the PhotoSendSP POST action we defined. It has also pulled some of the data from the swagger file to prepopulate some fields. Note this screen has some UI problems – you need to hover your cursor over the forms in the middle of the screen to see the scrollbar, so there is more to edit than what you initially see…

image

For now, do not enter anything into the summary field and scroll down to look at some of the other settings. The Visibility setting does not really matter for PowerApps, but remember that other online services can call our function. This visibility stuff relates more to Microsoft Flow, so you can ignore it for now. Scroll further down and you will see how our swagger schema has been processed by PowerApps. You can explore this but I suggest leaving it well alone. Below I have used all my MSPaint skills to make a montage to show how this relates to your Swagger file…

image

Finally, click Create Connector to wire it up. If all has gone to plan, you will see something resembling the following in the list of custom connectors in PowerApps. If you get this far, congratulations! You are almost there!

image

6. Test successfully and bask in the glory of your awesomeness

Now that you have a custom connection, let’s try it out. Open your PowerApp that you created in step 1. From the Content menu, click Data sources, click Add Datasource and then click New Connection. Scroll through the list of connections until you find the one you created in step 5. Click on it and you will be prompted for an API key (now you see why I added that friendly label during step 5).

image  image  image

Where to find this key? Well, it turns out that it was automagically generated for you when you first created your function! Go back to Azure functions portal and find your function. From the function sub menus, click Manage and you will be presented with a “Functions Keys” section with a default key listed. Copy this to the clipboard, and paste it back into the PowerApps API Key text box and click the Create button. Your datasource that connects to your Azure function is now configured in PowerApps!! (yay!).

image

image  image

Now way back in step 1 (gosh it seems like such a long time ago), we created a button labelled Submit, with the following formula.

Clear(SubmitData);
ForAll(PictureList,Collect(SubmitData, { filename: "a file.jpg", filebody: Url }))

The problem we are going to have is that all files submitted to the webservice will be called “a file.jpg” as I hardcoded the filename parameter for simplicity. Now if I fully developed this app, I would add a textbox to the gallery so that each photo has to be named prior to being able to submit to SharePoint. I am not going to do that here as this post is already too long, so instead I will use a trick I saw here to create a random two letter filename. I know it is not truly unique, but for our demo will suffice.

Here is the formula in all its ugliness.

Concatenate(Text( Now(), DateTimeFormat.LongDate ),Mid("0123456789ABCDEFGHIJKLMNOPQRTSTIUVWXYZ", 1 + RoundDown(Rand() * 36, 0), 1),Mid("0123456789ABCDEFGHIJKLMNOPQRTSTIUVWXYZ", 1 + RoundDown(Rand() * 36, 0), 1),".jpg"), filebody: Url } ) )

Yeah I know… let me break it down for you…

  • Text( Now(), DateTimeFormat.LongDate ) produces a string containing todays date
  • Mid(“0123456789ABCDEFGHIJKLMNOPQRTSTIUVWXYZ”, 1 + RoundDown(Rand() * 36, 0), 1) – selects a random letter/number from the string
  • Concatenate takes the above date, random letters and adds “.jpg” to it.

PowerApps does allow for line breaks in code, so it looks less ugly…

image

Let’s quickly test this before the final step of sending it off to SharePoint. Preview the app, take some photos and then examine the collections. As you can see, the SubmitData collection now has unique filenames assigned (By the way, yes I took these in a car and no, I was not driving at the time! 🙂

image

We are now ready for the final step. We need to call the Azure function! Smile

Go back to your submit button and add the following code to the end of the existing code, taking into account the name you gave to your connection/datasource. You should find that PowerApps uses intellisense to help you add the line because it has a lot of metadata from the swagger file.

'myfunctionsdemo.azurewebsites.net'.apiPhotoSendSPpost(SubmitData)

image

Important! Before we go any further, I strongly suggest you use the PowerApps web based authoring environment and not the desktop application. I have seen a problem where the desktop application does not encode images using the Data URI format, whereas the web based tool (and PowerApps clients on android and IOS) work fine.

So log into PowerApps on the web, open your app, preview it and cross your fingers! (If you want to be clever, go back to your function in Azure and keep and eye on the logs Smile

image

As the above screen shows, things are looking good. Let’s check the SharePoint document library that the script uploads to… YEAH BABY!! We have our photos!!!

image

Conclusion

Now you finally get to bask in your awesomeness. If you survived all this plumbing and have gotten this working then congratulations! you are well on your way to becoming a PowerApps, PowerShell, PnP (and flow) guru. This means you can now enhance your apps in all sorts of ways. For the non-developers who got this far, you can truly call yourself a citizen developer and design all sorts of innovative solutions via these techniques.

Even though a lot of this stuff is fiddly (especially that god-awful swagger crap), once you have gone through it a couple of times, and understand the intent of the components we have used, this is actually quite an easy solution to put together. I also found the Azure function side of things in particular, really easy to debug and see what was going on.

In terms of where we could take this, there are several avenues that I can immediately think of, but the possibilities are endless.

  • We could update the PowerShell code so it takes the destination document library as a parameter. We would need to update a new Swagger definition and then update our datasource in PowerApps, but that is not too hard a task once you have the basics working.
  • Using the same method, we could design a much more sophisticated form and capture lots of useful metadata with the pictures, and get them into SharePoint as metadata on the picture.
  • We could add in a lot more error handling into the script and return much better detail to PowerApps, such as detailed failure information.
  • Similarly, we could return detailed information to PowerApps to make the app richer. For example, we could generate unique filenames in our PowerShell function rather than PowerApps and return those names (and the URL to the image) in the reply to the HTTP Post, which would enable PowerApps to display images inline.
  • We could also take advantage of recent PowerApps enhancements and use local caching. I.e, when an internet connection is available, call the API, but if not, save to local storage and call the API once a connection is available.
  • We could not only upload images, but update lists and any other combination of SharePoint functions supported by PnP.

I hope that this post has helped you to better understand how these components hang together and I look forward to your feedback and how you have adopted/adapted and enhanced the ideas presented here.

 

Thanks for reading

 

Paul Culmsee

www.hereticsguidebooks.com

 

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

Tips for using SPD Workflows to talk to 3rd party web services

Send to Kindle

Hi all

One workflow action that anyone getting into SharePoint Designer 2013 workflow development needs to know is the Call HTTP Web Service action. This action opens up many possibilities for workflow developers, and in some ways, turns them into an option to be taken seriously for certain types of development in SharePoint – particularly in Office 365 where your customisations options are more restrictive.

Not so long ago, I wrote a large series of posts on the topic of calling web services within workflows and was able to get around some issues encountered when utilising Managed Metadata columns. Fabian WIlliams and Andrew Connell have also done some excellent work in this area too. More recently I have turned my attention to using 3rd party cloud services with SharePoint to create hybrid scenarios. After all, there are tons of fit-for-purpose solutions for various problems in cloud land and many have an API that supports REST/JSON. As a result, they are accessible to our workflows which makes for some cool possibilities.

But if you try this yourself, you find out fairly quickly that there are three universal realities you have to deal with:

  1. Debugging SPD workflows that call web services is a total bitch
  2. SPD Workflows are very fussy when parsing the data returned by web services
  3. Web services themselves can be very fickle

In this brief post, I thought I might expand on each of these problem areas with some pointers and examples of how we got around them. While they may not be applicable or usable for you, they might give you some ideas in your own development and troubleshooting efforts.

Debugging SharePoint Designer Workflows…

The first issue that you are likely to encounter is a by product of how SharePoint 2013 workflows work. To explain, here is my all-time most dodgy conceptual diagram that is kind of wrong, but has just enough “rightness” not to get me in too much trouble with hardcore SharePoint nerds.

Snapshot

In this scenario, the SharePoint deployment consists of a web front end server and a middle tier server running Workflow Manager. Each time a workflow step runs, it is executed on the workflow manager server, not on the SharePoint server, and most certainly not on the users browser. The implication of this is that if you wish to get a debug trace of the webservice call made by the workflow manager server, you need to do it on the workflow manager server, which necessitates access to it.

Now typically this is not going to happen in production and it is sure as hell never going to happen on Office365, so you have to do this in a development environment. There are two approaches I have used to trace HTTP conversations to and from workflow manager: The first is to use a network sniffer tool like Wireshark and the second is to use a HTTP level trace tool like Fiddler. The Wireshark approach is oft overlooked by SharePoint peeps, but that’s likely because most developers tend not to operate at the TCPIP layer. It’s main pro is it is fairly easy to set up and capture a trace, but its major disadvantage is that if the remote webservice uses HTTPS, then the packet data will be encrypted at the point where the trace is operating. This rules it out when talking to most 3rd party API’s out in cloud land.

Therefore the preferred method is to use install Fiddler onto the workflow manager server, and configure it to trace HTTP calls from workflow manager. This method is more reliable and easier to work with, but is relatively tricky to set it all up. Luckily for all of us, Andrew Connell wrote comprehensive and clear instructions for using this approach.

SPD Workflows are very fussy when parsing the data returned by web services

If you have never seen the joys of JSON data, it looks like this…

{“d”:{“results”:[{“__metadata”:{“id”:”71deada5-6100-48a5-b2e3-42b97b9052a2″,”uri”:”http://megacorp/iso9001/_api/Web/Lists(guid’a64bb9ec-8b00-407c-a7d9-7e8e6ef3e647′)/Items(1)”,”etag”:”\”6\””,”type”:”SP.Data.DocumentsItem”},”FirstUniqueAncestorSecurableObject”:{“__deferred”:{“uri”:”http://megacorp/iso9001/_api/Web/Lists(guid’a64bb9ec-8b00-407c-a7d9-7e8e6ef3e647′)/Items(1)/FirstUniqueAncestorSecurableObject”}},”RoleAssignments”

Hard to read? yeah totally, but it was never meant to be human readable anyway. The point here that when calling a HTTP Web service, SharePoint Designer will parse JSON data like the example above into a dictionary variable for you to then work with. In part 7 of my big workflow series I demonstrated how you can parse the data returned to make decisions in your workflow.

But as Fabian has noted, some web services return additional data other than the JSON itself. For example, sometimes an API will return JSON in a JavaScript variable like so:

var Variable = {“d”:{“results”:[{“__metadata”:{“id”:”71deada5-6100-48a5-b2e3-42b97b9052a2″,”uri”:”http://megacorp/iso9001/_api/Web/Lists(guid’a64bb9ec-8b00-407c-a7d9-7e8e6ef3e647′)/Items(1)”,”etag”:”\”6\””,”type”:”SP.Data.DocumentsItem”},”FirstUniqueAncestorSecurableObject”:{“__deferred”:{“uri”:”http://megacorp/iso9001/_api/Web/Lists(guid’a64bb9ec-8b00-407c-a7d9-7e8e6ef3e647′)/Items(1)/FirstUniqueAncestorSecurableObject”}},”RoleAssignments”

The problem here is that the parser code used by the Call HTTP Web Service action does not handle this well at all. Fabian had this to say in his research

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.

Microsoft documented an approach to getting around this when demonstrating pulling data from ebay which brings in XML format instead of JSON. They used a transformer web service provided by firstamong.com and then passed the URL of the ebay web service as a parameter to the transformer web service (ie http://www.firstamong.com/json/index.php?q=http://deals.ebay.com/feeds/xml. A similar thing could be done for getting cleaned JSON.

So what to do if you have a web-service that includes data you are not interested in? Before you swear too much or use Microsoft’s approach, check with the web service provider to see if they offer a way to return “raw” JSON data back to you. I have found with a little digging, some cloud providers can do this. Usually it is a variation of the URL you call or something you add to the HTTP request header. In short, do whatever you can to get back a simple JSON array and nothing else if you want it easily parsed by SharePoint.

Now speaking of request headers…

Web Services can be fickle…

It is very common to get a HTTP 500 internal server error when calling 3rd party web services. One common reason for this is that SPD workflows add lots of stuff to the HTTP request when calling a web service because it assumes it is going to be SharePoint and some authorisation needs to take place. But 3rd party webservices are likely not to care, as they tend to use API keys. In fact, sometimes it can cause you problems if the remote webserver is not expecting this stuff in the header.

Here is an example of a problem that my colleague Hendry Khouw had. After successfully crafting a REST request to a 3rd party service on his workstation, he tried to call the same web service using the Call HTTP Web Service workflow action. But when the webservice was called from the workflow, the HTTP responsecode was 500 (internal server error). Using Andrew Connell’s method of fiddler tracing explained earlier, he captured the request that was returning a HTTP 500 error.

image

image

Hendry then pasted the web service URL directly into Fiddler and captured the following trace that returned a successful HTTP 200 response and the JSON data expected.

image

By comparing the request header between the failed and successful request, it becomes clear that Workflow Manager sends oAuth data, as well as various other things in the header that were not sent when the URL was manually called. Through a little trial and error using Fiddler’s composer function, Hendry isolated the problem down to one particular oAuth header variable, Authorization: Bearer. By using Fiddler composer and removing the Authorization variable (or setting it as a blank value), the request was successful as can be shown on the second and third images below:

image  image

image

Now that we have worked out the offensive HTTP header variable that was causing the remote end to spit the dummy, we can craft a custom request header in the workflow to prevent the default value of Bearer being set.

Step 1. Build a dictionary to be included into the RequestHeaders for the web service call. Note the blank value.

image

Step 2. Set the RequestHeaders to use the dictionary we’ve just created.

image

image

Hendry then republished his workflow, the request was successful and he was able to parse the results into a dictionary variable.

Hope this helps others…

Paul Culmsee and Hendry Khouw

www.sevensigma.com.au

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

Trials or tribulation? Inside SharePoint 2013 workflows–conclusion and reflections

This entry is part 13 of 13 in the series Workflow
Send to Kindle

Hi all

In case you have not been paying attention, I’ve churned out a large series of posts – twelve in all – on the topic of SharePoint Designer 2013 workflows. The premise of the series was to answer a couple of questions:

1.  Is there enough workflow functionality in SharePoint 2013 to avoid having to jump straight to 3rd party tools?

2. Is there enough workflow functionality to enable and empower citizen developers to create lightweight solutions to solve organisational problems?

To answer these questions, I took a relatively simple real world scenario to illustrate what the journey looks like. Well – sort of simple in the sense that I deliberately chose a scenario that involved managed metadata. Because of this seemingly innocuous information architecture decision, we encountered SharePoint default settings that break stuff, crazy error messages that make no sense, learnt all about REST/oData, JSON, a dash of CAML and mastered the Fiddler tool to make sense of it all. We learnt a few SharePoint (and non SharePoint) web services, played with new features like dictionaries, loops and stages. Hopefully, if you have stuck with me as we progressed through this series, you have a much better understanding of the power and potential peril of this technology.

So where does that leave us with our questions?

In terms of the question of whether this edition enables you to avoid 3rd party tools – I think the answer is an absolute yes for SharePoint Foundation and a qualified yes for everything else. On the plus side, the new architecture certainly addresses some of the previous scalability issues and the ability to call web services and parse the data returned, opens up all sorts of really interesting possibilities. If “no custom development” solutions are your mantra (which is really “no managed code” usually) , then you have at your disposal a powerful development tool. Don’t forget that I have shown you a glimpse of what can be done. Very clever people like Fabian WIlliams have taken it much further than me, such as creating new SharePoint lists, creating no code timer jobs and creating your own declarative workflows – probably the most interesting feature of all.

In a nutshell, with this version, many things that were only possible in Visual Studio now become very doable using SharePoint Designer – especially important for Office365 scenarios.

So then, why a qualified yes as opposed to an enthusiastic yes?

Because it is still all so… how do I put this…  so #$%#ing fiddly!

Fiddly is just a euphemism for complexity, and in SharePoint it manifests in the minefield of caveats and “watch out for…” type of advice that SharePoint consultants often have to give. It has afflicted SharePoint since the very beginning and Microsoft are seemingly powerless to address it while they address issues of complexity by making things more complex. As an example: Here is my initial workflow action to assign the process owner a task from part 2. One single, simple action that looks up the process owner based on the organisation column.

image_thumb43  image

Now the above solution never worked of course because managed metadata columns are not supported in the list item filtering capability of SPD workflows. Yes, we were able to work around the issue successfully without sacrificing our information architecture, but take a look below at the price we paid in terms of complexity to achieve it. From one action to dozens. Whilst I prefer this in a workflow rather than in Visual studio and compiled to a WSP file, it required a working knowledge of JSON, REST/oData, CAML and debugging HTTP traffic via Fiddler. Not exactly the tools of your average information worker or citizen developer.

image_thumb10  image_thumb18    image_thumb22

image_thumb25  image_thumb27  image_thumb14

A lot of code above to assign a task to someone eh?

Another consideration on the 3rd party vs. out of the box discussion is of course all of the features that the 3rd party workflow tools have. The most obvious example is a decent forms solution. Whilst InfoPath still is around, the fact that Microsoft did precisely nothing with it in SharePoint 2013 and removed support for its use in SharePoint 2013 workflows suggests that they won’t have a change of heart anytime soon.

In fact, my prediction is that Microsoft are working on their own forms based solution and will be seriously bolstering workflow capability in SharePoint vNext. They will create many additional declarative workflow actions, and probably model a hybrid forms solution that works in a similar way to the way Nintex live forms does. Why I do I think this? It’s just a hunch, based on the observation that a lot of the plumbing to do this is there in SharePoint 2013/Workflow Manager and also that there is a serious gap in the forms story in SharePoint 2013. How else will they be able to tell a good multi-device story going forward?

But perhaps the ultimate lead indicator to the suitability of this new functionality to citizen developers is to gauge feedback from citizen developers who took the time to understand the twelve articles I wrote. In fact, if you are truly evil IT manager, concerned with the risk of information worker committing SharePoint atrocities, then get your potential citizen developers to read this series of articles as a way to set expectations and test their mettle. If they get through them, give them the benefit of the doubt and let them at it!

So all you citizen developers, do you feel inspired that we were able to get around the issues, or feel somewhat shell shocked at all of the conceptual baggage, caveats and workarounds? If you are in the latter camp, then maybe serious SharePoint 2013 workflow development is not for you, but then again, if you are not blessed with a large budget to invest in 3rd party tools, you want to get SharePoint onto your CV, all the while, helping organisations escape those annoying project managers and elitist developers, at least you now know what you need to learn!

On a more serious note, if you are on a SharePoint governance, strategy or steering team (which almost by definition means you are only reading this conclusion and not the twelve articles that preceded it), then you should consider how you define value when looking at the ROI of 3rd party verses going out of the box for workflow. For me, if part of your intention or strategy is to build a deeper knowledge and capacity of SharePoint in your information workers and citizen developers, then I would look closely at out of the box because it does force people to better understand how SharePoint works more broadly. But (and its a big but), remember that the 3rd party tools are more mature offerings. While they may mitigate the need for workflow authors to learn SharePoint’s deeper plumbing, they nevertheless produce workflows that are much simpler and more understandable than what I produced using out of the box approaches. Therefore from a resource based view (ie take the least amount of time to develop and publish workflows), one would lean toward the third party tools.

I hope you enjoyed the series and thanks so much for reading

Paul Culmsee

HGBP_Cover-236x300.jpg

www.hereticsguidebooks.com

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

Trials or tribulation? Inside SharePoint 2013 workflows–Part 12

This entry is part 12 of 13 in the series Workflow
Send to Kindle

Hi all, and welcome to part 12 of my articles about SharePoint 2013 Workflows and whether they are ready for prime time. Along the way we have learnt all about CAML, REST, JSON, calling web services, Fiddler, Dictionary objects and a heap of scenarios that can derail aspiring workflow developers. All this just to assign a task to a user!

Anyways, since it has been such a long journey, I felt it worthwhile to remind you of the goal here. We have a fictitious company called Megacorp trying to develop a solution to controlled documents management. The site structure is as follows:

image

The business process we have been working through looks like this:

Snapshot_thumb3

The big issue that has caused me to have to write 12 articles all boils down to the information architecture decision to use a managed metadata column to store the Organisation hierarchy.

Right now, we are in the middle of implementing an approach of calling a web service to perform step 3 in the above diagram. In part 9 and part 10 of this series, I explained the theory of embedding a CAML query into a REST query and in part 11, we built out most of the workflow. Currently the workflow has 4 stages and we have completed the first three of them.

  • 1) Get the organisation name of the current item
  • 2) Obtain an X-RequestDigest via a web service call
  • 3) Constructed the URL to search the Process Owner list and called the web service

The next stage will parse the results of the web service call to get the AssignedToID and then call another web service to get the actual userid of the user. Then we can finally have what we need to assign an approval task. So let’s get into it…

Obtaining the UserID

In the previous post, I showed how we constructed a URL similar to this one:

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>Megacorp%20Burgers</Value></Eq></Where></Query></View>”}

This URL uses the CAML in REST method of querying the Process Owners list and returns any items where Organisation equals “Megacorp Burgers”. The JSON data returned shows the AssignedToID entry with a value of 8. Via the work we did in the last post. we already have this data available to us in a dictionary variable called ProcessOwnerJSON.

The rightmost JSON output below illustrates taking that AssignedToID value and calling another web service to return the username , i.e : http://megacorp/iso9001/_api/Web/GetUserById(8).

image   image_thumb52

Confused at this point? Then I suggest you go back and re-read parts 8 and 10 in particular for a recap.

So our immediate task is to extract the AssignedToId from the dictionary variable called ProcessOwnerJSON. Now that you are a JSON guru, you should be able to figure out that the query will be d/results(0)/AssignedToId.

Step 1:

Add a Get an Item from a Dictionary action as the first action in the Obtain Userid workflow stage. Click the item by name or path hyperlink and click the ellipses to bring up the string builder screen. Type in d/results(0)/AssignedToId.

image

Step 2:

Click on the dictionary hyperlink and choose the ProcessOwnerJSON variable from the list.

Step 3:

Click the item hyperlink and use the AssignedToID variable

image

That is basically it for now with this workflow stage as the rest of it remains unchanged from when we constructed it in part 8. At this point, the Obtain Userid stage should look like this:

image

If you look closely, you can see that it calls the GetUserById method and the JSON response is added to the dictionary variable called UserDetail. Then if the HTTP response code is OK (code 200), it will pull out the LoginName from the UserDetail variable and log it to the workflow history before assigning a task.

Phew! Are we there yet? Let’s see if it all works!

Testing the workflow

So now that we have the essential bits of the workflow done, let’s run a test. This time I will use one of the documents owned by Megacorp Iron Man Suits – the Jarvis backup and recovery procedure. The process owner for Megacorp Iron Man suits is Chris Tomich (Chris reviewed this series and insisted he be in charge of Iron Man suits!).

image  image

If we run the workflow against the Jarvis backup and recovery procedure, we should expect a task to be created and assigned to Chris Tomich. Looking at the workflow information below, it worked! HOLY CRAP IT WORKED!!!

image

So finally, after eleven and a half posts, we have a working workflow! We have gotten around the issues of using managed metadata columns to filter lists, and we have learnt a heck of a lot about REST/oData, JSON, CAML and various other stuff along the way. So having climbed this managed metadata induced mountain, is there anything left to talk about?

Of course there is! But let’s summarise the workflow in text format rather than death by screenshot

Stage: Get Organisation Name
   Find | in the Current Item: Organisation_0 (Output to Variable:Index)
   then Copy Variable:Index characters from start of Current Item: Organisation_0 (Output to Variable: Organisation)
   then Replace " " with "%20" in Variable: Organisation (Output to Variable: Organisation)
   then Log Variable: Organisation to the workflow history list
   If Variable: Organisation is not empty
      Go to Get X-RequestDigest
   else
      Go to End of Workflow

Stage: Get-X-RequestDigest
   Build {...} Dictionary (Output to Variable: RequestHeader)
   then Call [%Workflow Context: Current Site URL%]_api/contextinfo HTTP Web Service with request
       (ResponseContent to Variable: ContextInfo
        |ResponseHeaders to responseheaders
        |ResponseStatusCode to Variable:ResponseCode )
   If Variable: responseCode equals OK
      Get d/GetContextWebInformation/FormDigestValue from Variable: ContextInfo (Output to Variable: X-RequestDigest )
   If Variable: X-RequestDigest is empty
      Go to End of Workflow
   else
      Go to Prepare and execute process owners web service call

Stage: Prepare and execute process owners web service call
   Build {...} Dictionary (Output to Variable: RequestHeader)
   then Set Variable:URLStart to _api/web/Lists/GetByTitle('Process%20Owners')/GetItems(query=@v1)?@v1={"ViewXml":"<View><Query><ViewFields><FieldRef%20Name='Organisation'/><FieldRef%20Name='AssignedTo'/></ViewFields><Where><Eq><FieldRef%20Name='Organisation'/><Value%20Type='TaxonomyFieldType'>
   then Set Variable:URLEnd to </Value></Eq></Where></Query></View>"}
   then Call [%Workflow Context: Current Site URL%][Variable: URLStart][Variable: Organisation][Variable: URLEnd] HTTP Web Service with request
      (ResponseContent to Variable: ProcessOwnerJSON
       |ResponseHeaders to responseheaders
       |ResponseStatusCode to Variable:ResponseCode )
   then Log Variable: responseCode to the workflow history list
   If Variable: responseCode equals OK
      Go to Obtain Userid
   else
      Go to End of Workflow

Stage: Obtain Userid
   Get d/results(0)/AssignedToId from Variable: ProcessOwnerJSON (Output to Variable: AssignedToID)
   then Call [%Workflow Context: Current Site URL%]_api/Web/GetUserByID([Variable: AssignedToID]) HTTP Web Service with request
      (ResponseContent to Variable: userDetail 
       |ResponseHeaders to responseheaders
       |ResponseStatusCode to Variable:ResponseCode )
   If Variable: responseCode equals OK
      Get d/LoginName from Variable: UserDetail (Output to Variable: AssignedToName)
      then Log The User to assign a task to is [%Variable: AssignedToName]
      then assign a task to Variable: AssignedToName (Task outcome to Variable:Outcome | Task ID to Variable: TaskID )
   Go to End of Workflow

Tidying up…

Just because we have our workflow working, does not mean it is optimally set up. In the above workflow, there are a whole heap of areas where I have not done any error checking. Additionally, the logging I have done is poor and not overly helpful for someone to troubleshoot later. So I will finish this post by making the workflow a bit more robust. I will not go through this step by step – instead I will paste the screenshots and summarise what I have done. Feel free to use these ideas and add your own good practices in the comments…

First up, I added a new stage at the start of the workflow for anything relation to initialisation activities. Right now, all it does is check out the current item (recall in part 3 we covered issues related to check in/out), and then set a Boolean workflow variable called EndWorkflow to No. You will see how I use this soon enough. I also added a new stage at the end of the workflow to tidy things up. I called it Clean up Workflow and it’s only operation is to check the current item back in.

image   image

In the Get Organisation Name stage, I changed it so that any error condition logs to the history list, and then set the EndWorkflow variable to Yes. Then in the Transition to stage section, I use the EndWorkflow variable to decide whether to move to the next stage or end the workflow by calling the Clean up workflow stage that I created earlier. My logic here is that there can be any number of error conditions that we might check for, and its easier to use a single variable to signify when to abort the workflow.

image

In the Get X-RequestDigest stage, I have added additional error checking. I check that the HTTP response code from the contextinfo web service call is indeed 200 (OK), and then if it is, I also check that we successfully extracted the X-RequestDigest from the response. Once again I use the EndWorkflow variable to flag which stage to move to in the transition section.

image

In the Prepare and execute process owners web service call stage, I also added more error checking – specifically with the AssignedToID variable. This variable is an integer and its default value is set to zero (0). If the value is still 0, it means that there was no process owner entry for the Organisation specified. If this happens, we need to handle for this…

image

Finally, we come to the Obtain Userid stage. Here we are checking both the HTTP code from the GetUserInfo web service call, as well as the userID that comes back via the AssignedToName variable. We assign the task to the user and then set the workflow status to “Completed workflow”. (Remember that we checked out the current item in the Workflow Initialisation stage, so we can now update the workflow status without all that check out crap that we hit in part 3).

image

Conclusion…

So there we have it. Twelve posts in and we have met the requirements for Megacorp. While there is still a heap of work to do in terms of customising the behaviour of the task itself, I am going to leave that to you!

Additionally, there are a lot of additional things we can do to make these workflows much more robust and easier to manage. To that end, I strongly urge you to check out Fabian Williams blog and his brilliant set of articles on this topic that take it much (much) further than I do here. He has written a ton of stuff and it was his work in particular inspired me to write this series. He also provided me with counsel and feedback on this series and I can’t thank him enough.

Now that we have gotten to where I wanted to, I’ll write one more article to conclude the series – reflecting on what we have covered, and its implications for organisations wanting to leverage out of the box SharePoint workflow, as well as implications for all of you citizen developers out there.

Until then, thanks for reading…

Paul Culmsee

HGBP_Cover-236x300.jpg

www.hereticsguidebooks.com

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

Trials or tribulation? Inside SharePoint 2013 workflows–Part 11

This entry is part 11 of 13 in the series Workflow
Send to Kindle

Hi all, and welcome to the penultimate article in what has tuned into a fairly epic series about SharePoint 2013 Workflows. From part 6 to part 8 of this series, we implemented a workflow that made use of the web service calls as well as the new looping capabilities of SharePoint Designer 2013. We used the web service call to get all of the items in the Process Owners list, and then looped through them to find the process owner we needed based on organisation. While that method worked, the concern was that it was potentially inefficient because if there was a large list of process owners, it might consume excessive resources. This is why I referred to the approach in part 6 as the “easy but flawed” way.

Now we are going to use the “better but harder way”. To that end, the part 9 and part 10 have set the scene for this one, where we are going to implement pretty much all of the theory we covered in them. Now I will not rehash any of the theory of the journey we took to get here, but I cannot stress enough that you really should have read them before going through this article.

With that said, we are going make a bunch of changes to the current workflow by doing the following:

  • 1) Change the existing workflow to grab the Organisation name as opposed to the GUID
  • 2) Create a new workflow stage that gets us the X-RequestHeader (explained in part 9).
  • 3) Build the URL that we will use to implement the “CAML in REST” approach (explained in part 9 and part 10)
  • 4) Call the aforementioned webservice
  • 5) Extract the AssignedToId of the process owner for a given organisation
  • 6) Call the GetUserByID webservice to grab the actual userID of the process owner and assign them an approval task

In this post, we will cover the first four of the above steps…

Get the Name not the GUID…

Here is the first stage of the workflow as it is now, assuming you followed parts 6 to 8.

image

First let’s make a few changes so that we get the Name of the Organisation stored with the current item, rather than the GUID as we are doing now. If you recall from part 4, the column Organisation_0 is a hidden column that got created because Organisation is a managed metadata column. This column stores the names and Id’s of managed metadata term(s) that have been assigned in the format of <term name>|<term GUID>. For example “Metacorp+Burgers|e2f8e2e0-9521-4c7c-95a2-f195ccad160f”.

To get the GUID, we grabbed everything to the right of the pipe symbol (“|”). Now to get the name, we need everything to the left of it.

Step 1:

Rename the stage from “Obtain Term GUID” to “Get Organisation Name” (I trust that by part 11 a screenshot is not required for this)

Step 2:

Delete the second workflow action called Calculate Variable: index plus 1 (Output to Variable:calc) as we don’t need the variable calc anymore. In addition, delete the workflow action “Copy from Current Item: Organisation_0”. You should be left with two actions and the transition to stage logic as shown below.

image

Step 3:

Add an Extract Substring from Start of String workflow action in between the two remaining actions. Click the “0” hyperlink and click the fx button. In the Lookup for Integer dialog, set it to the existing variable Index. Click on the “string” hyperlink and set it to the Organisation_0 column from the Current Item. Finally, click the (Output to…) hyperlink and create a new string variable called Organisation.

image

Now, at this point we need to pause and think about what we are doing. If you recall part 10, I had trouble getting the format right for the URL that uses CAML inside REST web service call. The culprit was that I had to encode any occurrence of a space in the URL with the HTML encoded space (a %20). Take a look at the URL that  was tested in Fiddler below to see this in action…

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>Megacorp%20Burgers</Value></Eq></Where></Query></View>”}

Look toward the end of the URL where the organisation is specified (marked in bold). What do you notice?

Yep – the space between Megacorp and Burgers is also encoded. But this causes a problem since the current value of the Organisation variable contains the space. So let’s deal with this now by encoding spaces.

Step 4:

Add a Replace Substring in String workflow action. Click the first string hyperlink and type in a single space. In the second string hyperlink, type in %20. In the third string hyperlink, click the fx button and add the Organisation variable. In the final hyperlink (Output to Variable:Output), choose the variable Organisation.

image

After all this manipulation of the Organisation variable, it is probably worthwhile logging it to the workflow history list so we can see if the above steps work as expected.

Step 5:

Click the Log Variable:TermGUID to the workflow history list action and change the variable from TermGUID to Organisation. The action will now be called Log Variable:Organisation to the workflow history list

image

Step 6:

In the Transition to stage section, find the “If Variable: TermGUID is not empty” condition and change the variable from TermGUID to Organisation

image

Step 7:

Create a new workflow stage and call it “Get X-RequestDigest”. Then in the Transition to stage section of the Get Organisation Name stage, find the “Go to Get Process Owners” and change the stage from Get Process Owners to Get X-RequestDigest.

The adjusted workflow should now look like the image below…

image

Getting the X-RequestDigest…

If you recall in part 9, we need to call the contextinfo web service so we can extract the FormDigestValue to use in our CAML embedded web service call to the Process Owners list. If that statement makes no sense then go back and read part 9, otherwise, you should already know what to do!.. Bring on the dictionary variables and the Call to HTTP Web service action!

Step 1:

Go to the Get Process Owners stage further down and find the very first action – a Build Dictionary action that creates a variable called RequestHeader. Right click on it and choose Move Action Up. This will move the action into the Get X-RequestDigest stage as shown below.

image  image

What are we doing here? This action was the one we created in part 9 that asks SharePoint to bring back data in JSON format. We first learnt all about this in part 4 when I explained JSON and part 5 when I explained how dictionary variables work.

Step 2:

Add a Call HTTP Web Service action after the build dictionary action. For the URL, use the string builder and add a lookup to the Current Site URL (found in Workflow Context in the data source dropdown). Then add the string “_api/contextinfo” to it to complete the URL of the web service. Also, make sure the method chosen is a HTTP POST and not a GET.

image  image

image

This will construct the URL based on which SharePoint site the workflow is run from (eg http://megacorp/iso9001/_api/contextinfo. ) but without hard-coding the URL.

Step 3:

Make sure the workflow action from step 2 is selected and in the ribbon, choose the Advanced Properties icon. In the Call HTTP Web Service Parameters dialog, click the RequestHeaders dropdown and choose the RequestHeader variable and click OK. (Now you know why we moved the build dictionary action in step 1)

image

Step 4:

Click the response hyperlink in the Call HTTP Web Service action and choose to create a new variable. Call it ContextInfo. Also check the name of the variable for the response code and make sure it is set to the responseCode and not something like responseCode2.

image  image

Step 5:

Add an If any value equals value condition below the web service call. For the first value hyperlink, choose the variable responseCode as per step 4. Click the second value hyperlink, type in “OK” as shown below:

image

This action ensures that the response to the web service call was valued (OK is the same as a HTTP 200 code). If we get anything other than an OK, there is no point continuing with the workflow.

Step 6:

Inside the condition we created in step 5, add a Get an Item from a Dictionary action. Then do the following:

  • In the item by name or path hyperlink, type in exactly “d/GetContextWebInformation/FormDigestValue” without the quotes.
  • In the dictionary hyperlink, choose the variable ContextInfo that was specified in step 4.
  • In the item hyperlink in the “Output To” section, create a new string variable called X-RequestDigest.

All this should result in the action below.

image

Now let’s take a quick pause to understand what we did in this step. You should recognise the d/GetContextWebInformation/FormDigestValue as parsing the JSON output. We get the value of FormDigestValue and assign it to the variable X-RequestDigest. As a reminder, here is the JSON output from calling the contextinfo web service using Fiddler. Note the path from d –> GetContextWebInformation –> FormDigestValue.

image_thumb17

Step 7:

In the transition to stage section, add an If any value equals value condition. For the first value hyperlink, choose the variable X-RequestDigest that we created in step 6. Click the equals hyperlink and change it to is empty.

image

Step 8:

Under the newly created If Variable: X-Request is empty condition, add a Go to a stage action and set it to End of Workflow. In the Else section of the condition, add a Go to a stage action and set it to the Get Process Owners stage.

image

Cool! We have our X-Request Digest stage all done. Here is what it looks like…

image

This has all been very easy so far hasn’t it! A big difference to some of the previous posts. But now its time to wire up the CAML inside REST web service call, and SharePoint is about to throw us another curveball…

Get the Process Owner…

Our next step is to rip the guts out of the existing stage to get the process owner. Unlike our first solution, we no longer need to loop through the process owners list which means the entire Find Matching Process Owner stage is no longer needed. So before we add new actions, lets do some tidying up.

Step 1:

Delete the entire stage called “Find Matching Process Owner”. Do this by clicking the stage to select all actions within it, and then choose delete from the SharePoint Designer ribbon. SPD will warn you that this will delete all actions. Go ahead and click OK.

image

Our next step is to attempt to make the CAML inside REST web service call. To remind you of what the URL will look like, here is the one we successfully tested in part 10. Ugly isn’t it. Now you know why developers are an odd bunch – they deal with this stuff all day!

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>Megacorp%20Burgers</Value></Eq></Where></Query></View>”}

Let’s take our time here, because as you can see the URL we have to craft is complex. First up, we need to use a Build a Dictionary action to create the HTTP headers we need (including the X-RequestDigest). Recall in part 9, that we also need to set Content-length to 0 and Accept to application/json;odata=verbose.

Step 2:

Add a Build dictionary action as the first action in the Get Process Owners section. Click the this hyperlink and the add button in the Build a Dictionary dialog. Add the following dictionary items:

  • Add a string called Accept and a value of: application/json;odata=verbose

image

  • Add a string called Content-length and a value of 0

image

  • Add a string called X-RequestDigest. In the value textbox, click the fx button and choose the workflow variable called X-RequestDigest.

image  image  image

Your dictionary should look like this:

image

Click ok and set the dictionary variable name to be the existing variable called RequestHeader. The completed action should look like the image below:

image

Now let’s turn our attention to creating the web service URL we need.

Step 3:

Find the existing Call HTTP Web Service action in the Get Process Owner stage. Click the URL hyperlink and click the ellipses to bring up the string builder dialog. Delete the existing URL so we can start over. Add the following entries back (carefully!)

  • 1) A lookup to the Site URL from the Workflow Context
  • 2) The string “_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>”
  • 3) A lookup to the Organisation workflow variable
  • 4) The string “</Value></Eq></Where></Query></View>”}”

This should look like the image below:

image

A snag…

Click OK and see what happens. Uh-oh. We are informed that “Using the special characters ‘[%%]’ or [%xxx%]’ in any string, or using the special character ‘{‘ in a string that also contains a workflow lookup may corrupt the string and cause an unexpected error when the workflow runs” – Ouch!

image

How do we get out of this issue?

Well, we are using two workflow lookups in the string – the first being the site URL at the start and the second being the Organisation variable embedded in the CAML bit of the URL. Since it is complaining of using certain special characters in combination with workflow lookups, let’s break up the URL into pieces by creating a couple of string variables. At the start of step 3 above, we listed 4 elements that make up the URL. Let’s use that as a basis to do this…

Step 4:

Add a Set Workflow Variable action below the build dictionary action in the Get Process Owner stage. Call the variable URLStart and set its value to: _api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>

image   image

Step 5:

Add another Set Workflow Variable action in the Get Process Owner stage. Call the variable URLEnd and set its value to: “</Value></Eq></Where></Query></View>”}”

image

Step 6:

Edit the existing Call HTTP Web Service action in the Get Process Owner stage. Click the URL hyperlink and add the following entries back (carefully!)

  • 1) A lookup to the Site URL from the Workflow Context
  • 2) A lookup to the URLStart workflow variable
  • 3) A lookup to the Organisation workflow variable
  • 4) A lookup to the URLEnd workflow variable

This should look like the image below:

image

Click OK and in the Call HTTP Web Service dialog, make sure the HTTP method is set to HTTP POST. Click OK

image  image

Step 7:

Select the Call HTTP Web Service action and click the Advanced Properties icon in the ribbon. In the Call HTTP Web Service Properties dialog box, click the RequestHeaders parameter and in the drop down list to the right of it, choose the RequestHeader variable created in step 3. Click OK.

image_thumb97    image_thumb103

 

Step 8:

Select the Call HTTP Web Service action and click the variable next to the ResponseContent to section. Create a variable called ProcessOwnerJSON. This variable will store the JSON returned from the web service call.

image    image

Step 9:

In the Transition to stage section of the Get Process Owners stage, look for the If responseCode equals OK condition. Set the stage to Obtain Userid as shown below:

image

Step 10:

To make the workflow better labelled, rename the existing Get Process Owners stage to Prepare and execute Process Owner web service call. This workflow stage is going to end when it has attempted the call and we will create a new stage to extract the process owner and create the approval task. At this point the workflow stage should look like the image below:

image

Conclusion

We will end the post at this point as it is already very long. In the next post, we will make a couple of tweaks to the Obtain Userid workflow stage and test the workflow out. For your reference, here is the complete workflow as it stands…

image

image

image

Thanks for reading

Paul Culmsee

HGBP_Cover-236x300.jpg

www.hereticsguidebooks.com

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

Trials or tribulation? Inside SharePoint 2013 workflows–Part 10

This entry is part 10 of 13 in the series Workflow
Send to Kindle

Hi there and welcome back to my series of articles that puts a real-world viewpoint to SharePoint 2013 workflow capabilities. This series is pitched more to Business Analysts, SharePoint Hackers and generally anyone who might be termed a citizen developer. This series shows the highs and lows of out of the box SharePoint Designer workflows, and hopefully helps organisations make an informed decision around whether or not to use what SharePoint provides, or moving to the 3rd party landscape.

By now you should be well aware of some of the useful new workflow capabilities such as stages, looping, support for calling web services and parsing the data via dictionary objects. You also now understand the basics of REST/oData and CAML. At the end of the last post, we just learnt that it is possible to embed CAML queries into REST/oData, which gets around the issue of not being able to filter lists via Managed metadata columns. We proved this could be done, but we did not actually try it with the actual CAML query that can filter managed metadata columns. It is now time to rectify this.

Building CAML queries

Now if you are a SharePoint developer worth your salt, you already know CAML, because their are mountains of documentation on this topic on MSDN as well as various blogs. But a useful shortcut for all you non coders out there, is to make use of a free tool called CAMLDesigner 2013. This tool, although unstable at times, is really easy to use, and in this section I will show you how I used it to create the CAML XML we need to filter the Process Owners list via the organisation column.

After you have downloaded CAMLDesigner and successfully gotten it installed, follow these steps to build your query.

Step 1:

Start CAMLDesigner 2013 and on the home screen, click the Connections menu.

image

Step 2:

In the connections screen that slides out from the right, enter http://megacorp/iso9001 into the top textbox, then click the SharePoint 2013 and Web Services buttons. Enter the credentials of a site administrator account and then click the Connect icon at the bottom. If you successfully connect to the site, CAMLDesigner will show the site in the left hand navigation.

image  image

Step 3:

Click the arrow to the left of the Megacorp site and find the Process Owners list. Click it, and all of the fields in the list will be displayed as blue boxes below the These are the fields of the list section.

image

Step 4:

Drag the Organisation column to the These are the selected fields section to the right. Then do the same for the Assigned To column. If you look closely at the second image, you will see that the CAML XML is already being built for you below.

image     image

Step 5:

Now click on the Where menu item above the columns. Drag the Organisation column across to the These are the selected fields section. As you can see in the second image below, once dragged across, a textbox appears, along with a blue button with an operator. Also take note of the CAML XML being build below. You can see that has added a <Where></Where> section.

image

image

image

Step 6:

In the Textbox in the Organisation column you just dragged, type in one of the Megacorp organisations. Eg: Megacorp Burgers. Note the XML changes…

image

Step 7:

Click the Execute button (the Play icon across the top). The CAML query will be run, and any matching data will be returned. In the example below, you can see that the user Teresa Culmsee is the process owner for Megacorp Burgers.

image

image

Step 8:

Copy the XML from the window to clipboard. We now have the XML we need to add to the REST web service call. Exit CAMLDesigner 2013.

image

Building the REST query…

Armed with your newly minted CAML XML as shown below, we need to return to fiddler and draft it into the final URL.

<ViewFields>
   <FieldRef Name='Organisation' />
   <FieldRef Name='AssignedTo' />
</ViewFields>
<Where>
   <Eq>
      <FieldRef Name='Organisation' />
      <Value Type='TaxonomyFieldType'>Megacorp Burgers</Value>
   </Eq>
</Where>

As a reminder, the XML that we had working in the past post looked like this:

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query></Query></View>”}

Let’s now munge them together by stripping the carriage returns from the XML and putting it between the <Query> and </Query> sections. This gives us the following large and scary looking URL.

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields> <FieldRef Name=’Organisation’ /> <FieldRef Name=’AssignedTo’ /> </ViewFields> <Where> <Eq> <FieldRef Name=’Organisation’ /> <Value Type=’TaxonomyFieldType’>Megacorp Burgers</Value> </Eq> </Where></Query></View>”}

Are we done? Unfortunately not. If you paste this into Fiddler composer, Fiddler will get really upset and display a red warning in the URL textbox…

image

If despite Fiddlers warning, you try and execute this request, you will get a curt response from SharePoint in the form of a HTTP/1.1 400 Bad Request response with the message HTTP Error 400. The request is badly formed.

The fact that Fiddler is complaining about this URL before it has  even been submitted to SharePoint allows us to work out the issue via trial and error. If you cut out a chunk of the URL, Fiddler is okay with it. For example: This trimmed URL is considered acceptable by Fiddler:

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query>

But adding this little bit makes it go red again.

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields> <FieldRef Name=’Organisation’ />

Any ideas what the issue could be? Well, it turns out that the use of spaces was the issue. I removed all the spaces from the URL above and where I could not, I encoded it in HTML. Thus the above URL turned into the URL below and Fiddler accepted it

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’ />

So returning to our original big URL, it now looks like this (and Fiddler is no longer showing me a red angry textbox):

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><ViewFields><FieldRef%20Name=’Organisation’/><FieldRef%20Name=’AssignedTo’/></ViewFields><Where><Eq><FieldRef%20Name=’Organisation’/><Value%20Type=’TaxonomyFieldType’>Megacorp%20Burgers</Value></Eq></Where></Query></View>”}

image

So let’s see what happens. We click the execute button. Wohoo! It works! Below you can see a single matching entry and it appears to be the entry from CAMLBuilder2013. We can’t tell for sure because the Assigned To column is returned as AssignedToID and we have to call another web service to return the actual username. We covered this issue and the web service to call extensively in part 8 but to quickly recap, we need to pass the value of AssignedToID to the http://megacorp/iso9001/_api/Web/GetUserById() web service. In this case, http://megacorp/iso9001/_api/Web/GetUserById(8) because the value of AssignedToId is 8.

The images below illustrate. The first one shows the Process Owner for Megacorp burgers. Note the value of AssignedToID is 8. The second image shows what happens when 8 is passed to the GetUserById web service call. Check Title and LoginName fields.

image image

Conclusion

Okay, so now we have our web service URL’s all sorted. In the next post we are going to modify the existing workflow. Right now it has four stages:

  • Stage 1: Obtain Term GUID (extracts the GUID of the Organisation column from the current workflow item in the Documents library and if successful, moves to stage 2)
  • Stage 2: Get Process Owners (makes a REST web service call to enumerate the Process Owners List and if successful, moves to stage 3)
  • Stage 3: Find Matching Process Owner (Loops through the process owners and finds the matching organisation from stage 1. For the match, grab the value of AssignedToID and if successful, move to stage 4)
  • Stage 4: Obtain UserID (Take the value of AssignedToID and make a REST web service call to return the windows logon name for the user specified by AssignedToID and assign a task to this user)

We will change it to the following  stages:

  • Stage 1: Obtain Term Name (extracts the name of the Organisation column from the current workflow item in the Documents library and if successful, moves to stage 2)
  • Stage 2: Get the X-RequestDigest (We will grab the request digest we need to do our HTTP POST to query the Process Owners list. If successful move to stage 3)
  • Stage 3: Get Process Owner (makes the REST web service call to grab the Process Owners for the organisation specified by the Term name from stage 1. Grab the value of AssignedToID and move to stage 4)
  • Stage 4: Obtain UserID (Take the value of AssignedToID and make a REST web service call to return the windows logon name for the user specified by AssignedToID and assign a task to this user)

One final note: After this epic journey we have taken, you might think that doing this in SharePoint Designer workflow should be a walk in the park. Unfortunately this is not quite the case and as you will see, there are a couple more hurdles to cross.

Until then, thanks for reading…

Paul Culmsee

HGBP_Cover-236x300.jpg

www.hereticsguidebooks.com

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

Trials or tribulation? Inside SharePoint 2013 workflows–Part 9

This entry is part 9 of 13 in the series Workflow
Send to Kindle

Hi all and welcome to my series that aims to illustrate the trials and tribulations of SharePoint 2013 workflow to those who consider themselves as citizen developers. In case you don’t want to go all the way back to part 1, a citizen developer is basically a user who creates new business applications themselves, without the use of developers (or IT in general). Since there is no Visual Studio in sight in this series, I think its safe to ay that SharePoint 2013 workflow has the potential to be a popular citizen developer tool, but it is important that people know what they are in for.

We start part 9 of this series having just finally assigned a task to a user in part 8. While this in itself is not particularly earth shattering, if you have followed this series to now, you will appreciate that we have had to navigate some serious potholes to get here, but along the way it is clear that there is some very powerful features available.

Currently the workflow as it stands consists of four stages.

  • Stage 1: Obtain Term GUID (extracts the GUID of the Organisation column from the current workflow item in the Documents library and if successful, moves to stage 2)
  • Stage 2: Get Process Owners (makes a REST web service call to enumerate the Process Owners List and if successful, moves to stage 3)
  • Stage 3: Find Matching Process Owner (Loops through the process owners and finds the matching organisation from stage 1. For the match, grab the value of AssignedToID and if successful, move to stage 4)
  • Stage 4: Obtain UserID (Take the value of AssignedToID and make a REST web service call to return the windows logon name for the user specified by AssignedToID and assign a task to this user)

As mentioned at the end of part 8, one flaw in this workflow is the issue that if the process owners list has a large number of entries, the workflow has to iterate each process owner to find the one with a matching organisation. This causes a bit of concern, because in general, iterating through SharePoint lists in this way is not overly great on performance. In fact SharePoint has an  unfortunate heritage of newbie developers causing all sorts of disk and memory performance issues because of code that iterates a list in a similar way.

So this post is going to explore how we can do better. How about change the workflow behaviour so that rather than grab the entire process owners list, we grab just the entry we need from the process owners list.

But wait – didn’t you say something about this not working?

Now if you have dutifully read this series in the way I intend you to do, you might recall the issue that cropped up in parts 4 and 5. I pointed out that since the Organisation column we are using is a managed metadata column, we cannot use it to filter a list using REST/oData. So while the first query below would happily filter a list by a title of “something”, the second one will result in a big fat error.

http://megacorp/iso9001/_vti_bin/client.svc/web/lists/getbyid(guid’0ffc4b79-1dd0-4c36-83bc-c31e88cb1c3a’)/Items?$filter=Title eq ‘something’ Smile

http://megacorp/iso9001/_vti_bin/client.svc/web/lists/getbyid(guid’0ffc4b79-1dd0-4c36-83bc-c31e88cb1c3a’)/Items?”filter=Organisation eq ‘something’ Sad smile

So this is a pickle isn’t it – how can we filter the process owners list by organisation when its not supported by REST/oData?

The thing about managed metadata columns…

Going back in time a bit, SharePoint 2010 was the first version with support for REST and in SharePoint 2013, REST support was extended significantly. As you now know, it seems the managed metadata people never got that memo because one of the older methods that can be used to query lists is called Collaborative Application Markup Language (CAML for short), and CAML does support filtering on managed metadata columns.

CAML, in case you are not aware of it, has been used for SharePoint since the very first version. It is based on a defined XML schema that can be used to query lists and libraries – much like a SQL query does on a database table. Being XML, it is more verbose than a SQL table and for me, harder to read. As an example, the SQL statement “SELECT * from TABLE WHERE field=VALUE” would look something like:

<Query><Where>< Eq><FieldRef Name=’field’ />< Value Type=’Text’>VALUE</Value> </Eq></Where></Query>.

Turning our attention back to the Organisation column that we are having trouble with, a CAML query to bring back all documents tagged as owned by “Megacorp Burgers” would look something like this…

<Where>
   <Eq>
      <FieldRef Name='Organisation' />
      <Value Type='TaxonomyFieldType'>Megacorp Burgers</Value>
   </Eq>
</Where>

Note: By the way, if you want to prove that this works, use CAML Designer 2013, to connect to a list, apply a filter and it will generate the CAML XML it used. I will cover this in the next post.

So here is where we are at. We can definitely can filter a list with a managed metadata column by using the CAML language. But we cannot filter a list using managed metadata via the REST/oData methods that I outlined in part 4. I wonder If there a way to embed a CAML query into a REST web service call?

Turns out there is… only problem is that there is some more conceptual baggage required to understand it properly, so have a strong coffee and lets go…

A journey of CAML in REST…

A while back I came across an MSDN forum thread where Christophe Humbert asked whether CAML queries could be done via the REST API. Erik C. Jordan provided this answer:

POST https//<site>/_api/web/Lists/GetByTitle(‘[list name]‘)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query>[other CAML query elements]</Query></View>”}

Take a close look at the above URL. We are still talking to SharePoint via REST and we are calling a method called GetItems. As part of the GetItems call, we see CAML XML inside some curly braces: ‘@v1={“ViewXml”:”<View><Query>[other CAML query elements]</Query></View>”}’.

Hmmm – this looks to have potential. If I can embed a valid CAML query that filters list items based on a managed metadata column, we can very likely have the workflow do that using the Call HTTP Web Service workflow action.

So let’s test this web service and see if we can get it to work. Let’s try enter the above URL on the MegaCorp process owners site.

http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query></Query></View>”}

Uh-oh, error 400. Dammit, what now?

image_thumb3_thumb_thumb

Turns out that we cannot use a browser to test this particular web service because it is requires a HTTP POST operation, but when we type a URL into a browser, we are performing a HTTP GET operation, hence the error 400.  If you really want to know the difference between a GET and POST in relation to HTTP go and visit this link. But for the purpose of this article, we need to find a way to compose HTTP POST web service calls and guess what – you already know exactly how to do it because we covered it in part 7 – the Fiddler composer function.

So start up Fiddler and let’s craft ourselves a call to this web service….

Step 1:

Start Fiddler and click the Composer Tab. Paste in the web service call we just tried – http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query></Query></View>”}. Make sure you change the request from a GET to a POST by clicking the dropdown to the left of the URL.

image

Step 2:

Type in the string “Accept: application/json;odata=verbose” into the Request headers textbox as shown below. If you recall the HTTP interlude from part 6, this tells SharePoint to bring back the data in JSON format, rather than XML

image

Step 3:

Click the execute button to execute the request and then click the Response Headers tab as shown below to see what happened. As you can see below, things did not go so well. We got a response code of HTTP/1.1 411 Length Required

image

Hmm – so what are we missing here? It turns out that some HTTP queries require the use of a ‘Content-Length‘ field in the HTTP header. The standard for the HTTP protocol states that: “Any Content-Length greater than or equal to zero is a valid value”, so let’s add a value of zero to the request header.

Step 4:

Click the composer tab again and add the string “Content-length: 0” to the request header textbox as shown below and click execute again:

image

Checking the response and it looks like we are still not quite there as we have another error code: HTTP/1.1 403 FORBIDDEN (The security validation for this page is invalid and might be corrupted. Please use your web browser’s Back button to try your operation again). *sigh* will this ever just work?

image

The reason for this error is a little more complex than the last one. It turns out that we are missing another required HTTP header in the POST request that we are crafting. This header has the cool sounding name of X-RequestDigest and it holds something called the form digest. What is the form digest? Here is what Nadeem Ishqair from Microsoft says:

The form digest is an object that is inserted into a page by SharePoint and is used to validate client requests. Validation of client requests is specific to a user, a site and time-frame. SharePoint relies on the form digest as a form of security validation that helps prevent replay attacks wherein users may be tricked into posting data to the server. As described on this page on MSDN, you can retrieve this value by making a POST request with an empty body to http://site/_api/contextinfo and extracting the value of the “d:FormDigestValue” node in the XML that the contextinfo endpoint returns.

So there you go – it is a security function that validates web service requests. So our workflow is going to have to make yet another web service call to handle this. We will make a POST request with an empty body to http://megacorp/iso9001/_api/contextinfo and then extract the value of the “d:FormDigestValue” node in the information returned.

This probably sounds as clear as mud, so let’s use Fiddler to do it so we know what we have to do in the workflow.

Step 5:

Start Fiddler and click the Composer Tab. Paste in the web service of http://megacorp/iso9001/_api/contextinfo. Make sure you change the request from a GET to a POST and add the string “Accept: application/json;odata=verbose” into the Request headers textbox as shown below

image

Step 6:

Click the execute button to execute the request and make sure the response headers tab is selected. Confirm that the response you get from the server is 200 OK.

image

Step 7:

Click the JSON button and look for an entry called FormDigestValue in the response.

image

Step 8:

Right click on the FormDigestValue entry and choose copy to get it into the clipboard.

image

Step 9:

Click on the composer tab again and paste the FormDigestValue into the Request Headers textbox as shown below. Replace the string “FormDigestValue =” with “X-RequestDigest: “ to make it the correct format needed as shown in the second image below.

image    image

Step 10:

Paste in the original request into the URL: http://megacorp/iso9001/_api/web/Lists/GetByTitle(‘Process%20Owners’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query></Query></View>”} and click Execute.

image

Check that the HTTP return code is 200 (OK) and then click the JSON tab to see what has come back. You should see a JSON result set that looks like the image below. If you examine the JSON data returned detail of this image, you will it is exactly the same JSON structure that was returned when we used fiddler in part 7.

image  image

Let’s pause here for a moment and reflect. If you have made it this far, you have pretty much nailed the hard bit. If you had an issue, fear not as there are a couple of common problems that are usually easy to rectify.

Firstly, if you receive an error HTTP/1.1 400 Bad Request with a message that looks something like “Invalid JSON. A colon character ‘:’ is expected after the property name ‘â’, but none was found.”, just double check the use of quotes (“”) in the URL. Sometimes when you paste strings from your browser or RSS reader, the quotes can get messed up because of autocorrect. Look closely at the URL below and note the quotes are angled:

{“ViewXml”:”<View><Query></Query></View>”}

To resolve this issue, simply replace the angled quotes with a regular boring old quote so they are not angled and the problem will go away. Compare the string below to the one above to see what I mean…

{“ViewXml”:”<View><Query></Query></View>”}

The second common problem is a HTTP/1.1 403 FORBIDDEN response with the message: “The security validation for this page is invalid. Click Back in your Web browser, refresh the page, and try your operation again”. If you see this error, your X-RequestDigest may have expired and you need to regenerate it via repeating steps 5 to 9 above. The other possibility is that you did not properly paste the FormDigest into the request header. Double check this as well.

Conclusion

Okay, so that was a rather large dollop of conceptual baggage I handed out in this post. You got introduced to the older method of querying SharePoint lists called CAML, and we have successfully been able to call a REST web service and pass in a CAML XML string and get back data. We learnt about the HTTP POST request and some of the additional HTTP headers that need to be sent, like the Content-length and the X-RequestDigest to make it all work. As an added bonus, we are all Fiddler composer gurus now.

However all we sent across was an empty CAML string. The string <View><Query></Query></View> pretty much says “give me everything and don’t filter”, which is not what we want. So in the next post, we will learn how to create a valid CAML string that filters by the Organisation column. Once we have successfully tested it, we will modify the workflow to use this method instead.

Until then, thanks for reading…

Paul Culmsee

HGBP_Cover-236x300.jpg

www.hereticsguidebooks.com

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle