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

Sack Justin Bieber with SPD2010 and Forms Services – Part 2

Send to Kindle

Hi

This is part 2 of a quick (but huge) post on my experiences working with SharePoint Designer 2010 workflows and Forms Services. In part 1, we used the scenario of an employee termination form, and sacked Justin Bieber. Now we want to ensure that the SharePoint user experience for sacking Justin Bieber is seamless and intuitive.

Truth be told, I have never actually heard a Justin Bieber song because we have not had a television in the house for over a year. Ignorance is bliss, but I have seen enough news reports that I still want to sack him!

In part 1, we examined the ability of SPD2010 to leverage InfoPath for tailoring forms used by workflows. We then covered creating a workflow utilising the Start Approval Process action, which enables us to do a couple of cool things without custom programming.

Now we are onto the next two steps.

Continue reading

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

No Tags

Send to Kindle

Sack Justin Bieber with SPD2010 and Forms Services – Part 1

Send to Kindle

Hi all

A very long time ago now, I had the ambition to write an end-to-end blog post series called “A Humble Tribute to the Leave Form”. The intent was to show InfoPath Forms Services 2007 in all its glory – from its initially seductive, demo friendly first impressions, through to all of the dodgy workarounds and .net code required to get it to adequately handle a relatively simple business process like employee leave applications.

As it happened, I got through seven and a half blog posts and never finished it as events kind of overtook me. Its a pity because I didn’t get to the nasty bits. But one of the side effects of getting as far as I did, was that I ended up getting a lot of work developing leave forms!

Thus, now that SharePoint 2010 is upon us, it was inevitable that I would eventually get called to develop a leave form for this new edition. I now have done so, and in the process learnt a couple of new things that I thought were blogworthy – especially around getting things to play nice in a sustainable manner.

I came to realise that anytime you write any content that involves InfoPath, you end up with a stupid number of screenshots, and you are perpetually torn on the amount of detail to cover. So this time, rather than do a twelve post monster, I’ll just do 2 posts (real programmers will still find this post waffly but hopefully normal humans won’t).

I am not going to do a total beginners course here, I will assume instead you have done some basic InfoPath and SharePoint Designer workflows previously, and now want to know some interesting ways to do a general approval type process using SharePoint 2010. My main focus here is to deal with handling browser based InfoPath forms with SharePoint Designer workflows.

Continue reading

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

No Tags

Send to Kindle

SPD workflows: “ERROR: request not found in the TrackedRequests”

Send to Kindle

I’ve written this post to document a dumbass thing that I did and the error that it caused. Hopefully it might help someone googling in desperation sometime…

The popularity of the “Tribute to the humble leave form” series over at SharePoint Magazine, has meant that we actually get a few gigs implementing – surprise, surprise – InfoPath leave forms with SharePoint Designer workflows! This was actually quite unexpected, as I wrote that series as a joke and for end-user training purposes.

Now, I fully realise that many people don’t like tools like SharePoint Designer in the hands of mere mortals, but I personally think it is a terrific means to encourage buy-in and user evangelism and I encourage its sustainable use. Although “real developers” may disagree with me here, SPD workflows enable quick results without the embarrassing aftermath of premature code compilation (which always leads to frustration, right girls? 😀 ).

Anyhow enough sexual innuendo – here is my error with my lessons learned

The symptoms

Recently, a happily running leave form workflow ground to a halt with an error as shown below. What the screenshot below shows is that the workflow died right after a “Pause for duration” workflow action. (Note the “pausing for 2 minutes” line, just before the actual workflow error).

image

Now, the interesting thing here is that the pause action never happened. The workflow bombed out straight away and there was no pause at all. Yet, the fact that the pause action logged to the history list meant that it attempted to do so. It seemed that the pause action ran for long enough to log that it was about to pause, but died when actually trying to.

The pause action occurred in step 2 of my workflow, but it is worthwhile showing you the first workflow step first.

Below is a screenshot of step 1 of the workflow. This first step is called “Modify Item Permissions” and we are using the codeplex custom workflow actions to modify permissions of the submitted leave form, ensuring that only the requestor of the leave (and their boss) can see or modify the form.

image

The step in the workflow, called “Manager Approval”, shows where we pause for 2 minutes. The reason we pause is to get around another workflow error that I will explain at the end of the post. After the pause action was completed and the workflow recommenced, it performed a “Collect data from user” task as shown below. This created a task for the requestors manager to approve the leave request.

image

We know that the “Collect data from user” action never ran, because the “Employee Leave Approval” task never actually got created in the task list. Thus, we know the error occurred at the pause action. Or, did it?

This error occurred consistently whether the workflow was manually started or auto-triggered. Checked all the usual suspects – timber jobs ok, permissions unchanged and working well, etc. Scanning the ULS logs at this time showed an interesting error with a much less interesting stack trace (that I have pasted at the end of the post for readability).

“Unexpected    ERROR: request not found in the TrackedRequests. We might be creating and closing webs on different threads”

Hmm, what is TrackedRequests and why is a request not found? Googling that error message wasn’t much help at all. Although it is quite common, nothing matched my particular context. But then I received a lucky break. When I cancelled the running workflow that was in this error state, I received a new error that made it quite easy to work out what was going on.

WinWF Internal Error, terminating workflow Id# 02334a17-3211-4d30-902f-bf34e20354c6    
Unexpected    System.Workflow.Runtime.Hosting.PersistenceException: Object reference not set to an instance of an object. —> System.NullReferenceException: Object reference not set to an instance of an object.     at DP.Sharepoint.Workflow.Common.RemoveListItemPermissionEntry(SPListItem item, String principalName, Boolean breakRoleInheritance)    

It was this error that gave it away. For a start, the method being called was DP.Sharepoint.Workflow.Common.RemoveListItemPermissionEntry and the exception was a null reference. RemoveListItemPermissionEntry sounded suspiciously like the permission based workflow actions of the “Modify Item Permissions” step as highlighted below.

image

But wait… the workflow never failed at this line at all. It actually failed at the first action of the next step (“Manager Approval”)  with the “request not found in the TrackedRequests” error. Interesting, eh?

Let’s put aside the issue of what step and what action the workflow failed on for a moment, and examine the second error message more closely. If you examine the “Modify Item Permissions” action highlighted above, can you think of any reason that I would get a null reference exception?

Ah! What happens if the previous action called “Set Variable”, set a blank or null value? I suspect that the “Grant permissions on an item” workflow action will attempt to change permissions of the item to a blank or null user. The result? That workflow action will die with a null reference exception – precisely what the error states.

When I checked the source of the variable “LineManagerAccount”, it was looking up a contact list for the manager of the requestor. Sure enough, it did turn out that that column was blank for some users. Thus, a simple exercise in input validation combined with a small correction to that list item and the problem was solved.

Further questions and further information

I could stop this post there I suppose and perhaps someone, sometime might find this post and think “Wohoo!”. But this problem raised a couple of issues, as well as made it clear to me that I had been stupid in how I designed this one. Let’s tackle them one by one.

1. When did it die again?

Clearly the error that caused all of this, occurred at the last action of the “Modify Item Permissions” step. We know this because the step before was where the lookup to the contact list happened, and this is where the null value came from that caused the exception. But then why did the workflow not stop at this point? Why did it continue onward and attempt to move to the “Manager Approval” steps and die on the “Pause for duration” action?

Whether this is default behaviour of SPD workflows or a logic fault in the exception handling code in the codeplex-based “Grant Permission on an Item” action, this makes life hard to troubleshoot because the error message never made sense. I only found out the true root cause when I terminated the workflow manually and got lucky because the offending exception suddenly appeared.

So, the conclusion to draw? If your SPD workflow dies with “ERROR: request not found in the TrackedRequests” in the logs, it may be that the real cause of the problem is a previous workflow step and not the step where the workflow actually stopped.

2. Paul is a dumbass

Okay, so let’s talk about lessons learnt. Some people reading this may wonder why I never used Active Directory or the user profile store to store manager details for a user and to do the lookup from there. The answer is that this contact list approach simply made sense for this client and this is actually not the dumb thing that I did. The dumb thing that I did was to forget that all of the necessary business logic and data validation steps could have been performed in the InfoPath form itself.

Remember that InfoPath forms can have data connections to all sorts of sources such as SharePoint lists, web services and relational databases. (I am not going into detail on how to do that here because I have already covered it in quite a lot in part 5 of the “Leave Form Tribute” series). We can have a published InfoPath form connect to any SharePoint list or library across the entire farm to look up business logic details, such as an approver. This is something that SharePoint Designer workflows cannot do out of the box – it can only lookup from the site where the workflow has been created.

But, more important than that, we can easily use InfoPath rules and data validation functionality to ensure that the values are not null before submitting.

What this all means is that your SPD workflows end up being simpler and easier to maintain because InfoPath is providing all of the data to the workflow as well as the validation of the data. Therefore, the workflow now doesn’t have to do the lookup work and have unnecessary steps and actions.

For these reasons I think that, if you can, grab all of the data you need for a business process using the InfoPath form using hidden fields. Then publish the form and ensure these hidden fields are published as site columns.

For this particular purpose, I think that InfoPath is a lesser evil than SharePoint Designer workflows.

3. What are the pause actions there for anyway?

Finally, I promised I’d explain why I had a pause action in the workflow. This is to get around a race condition with workflows that produces an intermittent error message:

“This task is currently locked by a running workflow and cannot be edited”

I’ve seen this occur in several situations and it pretty much boils down to some workflow actions being synchronous and subsequent actions starting before the previous action properly completed. Consider this example.

One workflow creates or updates an item in a list, say a task list. But the task list also has a workflow attached to it. This means that the first workflow creates the task item and then moves onto the next step. Meanwhile, a new workflow has been triggered for that new task list item. This secondary workflow refers to information stored in the main item which causes the race condition.  If the information in the main item is not committed before this workflow attempts to use it, you’ll get null values when the main item is a new item.  When working with SPD workflows, these null values will show up as ????. 

To resolve the race condition, I had to ensure that the main item was committed before the secondary workflow was started by pausing the workflow.  Why does pausing the workflow cause the changes to be committed?  According to the MSDN article “How Windows SharePoint Services Processes Workflow Activities” http://msdn.microsoft.com/en-us/library/ms442249.aspx

Windows SharePoint Services runs the workflow until it reaches a point where it cannot proceed because it is waiting for some event to occur: for example, a user must designate a task as completed. Only at this "commit point" does Windows SharePoint Services commit the changes made in the previous Windows SharePoint Services-specific workflow activities…

The not-so-clever workaround is to pause the workflow in between actions. When you add a pause, the workflow “cannot proceed because it is waiting for some event to occur” and therefore makes it a commit point. Not pretty – but works.

I hope that you find this article of use in your SharePoint Designer workflow troubleshooting endeavours. Below, I have pasted the complete stack traces (for the search engines).

Thanks for reading

Paul Culmsee

06/25/2009 12:25:20.46 w3wp.exe (0x0BA8) 0x16F8 Windows SharePoint Services General 0 Unexpected ERROR: request not found in the TrackedRequests. We might be creating and closing webs on different threads. ThreadId = 1, Free call stack = at Microsoft.SharePoint.SPRequestManager.Release(SPRequest request) at Microsoft.SharePoint.SPWeb.Invalidate() at Microsoft.SharePoint.SPWeb.Close() at Microsoft.SharePoint.SPSite.Close() at Microsoft.SharePoint.SPSite.Dispose() at Microsoft.SharePoint.Workflow.SPWorkflowManager.<>c__DisplayClass1.<StartWorkflow>b__0() at Microsoft.SharePoint.SPSecurity.CodeToRunElevatedWrapper(Object state) at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass4.<RunWithElevatedPrivileges>b__2() at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode) at Microsoft.SharePoint.SPSecurity.RunWithEl…

06/25/2009 12:25:20.46* w3wp.exe (0x0BA8) 0x16F8 Windows SharePoint Services General 0 Unexpected …evatedPrivileges(WaitCallback secureCode, Object param) at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode) at Microsoft.SharePoint.Workflow.SPWorkflowManager.StartWorkflow(SPListItem item, SPWorkflowAssociation association, SPWorkflowEvent startEvent, Boolean bAutoStart, Boolean bCreateOnly) at Microsoft.SharePoint.Workflow.SPWorkflowManager.StartWorkflow(SPListItem item, SPWorkflowAssociation association, String eventData, Boolean isAutoStart) at Microsoft.SharePoint.Workflow.SPWorkflowManager.StartWorkflow(SPListItem item, SPWorkflowAssociation association, String eventData) at Microsoft.SharePoint.WebControls.SPWorkflowDataSourceView.Insert(IDictionary values) at Microsoft.SharePoint.WebControls.SPWorkflowDataSourceView.Ins…

06/25/2009 12:25:20.46* w3wp.exe (0x0BA8) 0x16F8 Windows SharePoint Services General 0 Unexpected …ert(IDictionary values, DataSourceViewOperationCallback callback) at Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PerformCommit() at Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) at Microsoft.SharePoint.WebPartPages.DataFormWebPart.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includ…

06/25/2009 12:25:20.46* w3wp.exe (0x0BA8) 0x16F8 Windows SharePoint Services General 0 Unexpected …eStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) at System.Web.HttpApplication.ApplicationStepManager.ResumeSteps(Exception error) at System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData) at System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) at System.Web.HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr) at Sys…

06/25/2009 12:25:20.46* w3wp.exe (0x0BA8) 0x16F8 Windows SharePoint Services General 0 Unexpected …tem.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType) , Allocation call stack (if present) at Microsoft.SharePoint.Library.SPRequest..ctor() at Microsoft.SharePoint.SPGlobal.CreateSPRequestAndSetIdentity(Boolean bNotGlobalAdminCode, String strUrl, Boolean bNotAddToContext, Byte[] UserToken, String userName, Boolean bIgnoreTokenTimeout, Boolean bAsAnonymous) at Microsoft.SharePoint.SPWeb.InitializeSPRequest() at Microsoft.SharePoint.SPWeb.EnsureSPRequest() at Microsoft.SharePoint.SPWeb.get_Request() at Microsoft.SharePoint.SPWeb.InitWebPublic() at Microsoft.SharePoint.SPWeb.get_ServerRelativeUrl() at Microsoft.SharePoint.SPWeb.get_Url() at Microsoft.SharePoint.SPUser.InitMember() at Microsoft.SharePoint.SPUser..ctor(SPWeb web, ISecura…

06/25/2009 12:25:20.46* w3wp.exe (0x0BA8) 0x16F8 Windows SharePoint Services General 0 Unexpected …bleObject scope, String strIdentifier, Object[,] arrUsersData, UInt32 index, Int32 iByParamId, String strByParamSID, String strByParamEmail, SPUserCollectionType userCollectionType, Boolean isSiteAuditor) at Microsoft.SharePoint.SPUser..ctor(SPWeb web, ISecurableObject scope, String strIdentifier, Object[,] arrUsersData, UInt32 index, Int32 iByParamId, String strByParamSID, String strByParamEmail, SPUserCollectionType userCollectionType) at Microsoft.SharePoint.SPUserCollection.GetByIDNoThrow(Int32 id) at Microsoft.SharePoint.SPSite.get_SystemAccount() at Microsoft.SharePoint.WorkflowActions.Helper.ResolveUserField(WorkflowContext context, Object fvalue) at Microsoft.SharePoint.WorkflowActions.Helper.LookupUser(WorkflowContext context, Guid listId, Int32 listItem, Strin…

06/25/2009 12:25:20.46* w3wp.exe (0x0BA8) 0x16F8 Windows SharePoint Services General 0 Unexpected …g fieldName) at Microsoft.SharePoint.WorkflowActions.Helper.LookupUser(WorkflowContext context, String listIdOrName, Int32 listItem, String fieldName) at Microsoft.SharePoint.WorkflowActions.LookupActivity.Execute(ActivityExecutionContext provider) at System.Workflow.ComponentModel.ActivityExecutor`1.Execute(T activity, ActivityExecutionContext executionContext) at System.Workflow.ComponentModel.ActivityExecutor`1.Execute(Activity activity, ActivityExecutionContext executionContext) at System.Workflow.ComponentModel.ActivityExecutorOperation.Run(IWorkflowCoreRuntime workflowCoreRuntime) at System.Workflow.Runtime.Scheduler.Run() at System.Workflow.Runtime.WorkflowExecutor.RunScheduler() at System.Workflow.Runtime.WorkflowExecutor.RunSome(Object ignored) …

06/25/2009 12:25:20.46* w3wp.exe (0x0BA8) 0x16F8 Windows SharePoint Services General 0 Unexpected …at System.Workflow.Runtime.Hosting.DefaultWorkflowSchedulerService.WorkItem.Invoke(WorkflowSchedulerService service) at System.Workflow.Runtime.Hosting.DefaultWorkflowSchedulerService.QueueWorkerProcess(Object state) at System.Threading._ThreadPoolWaitCallback.WaitCallback_Context(Object state) at System.Threading.ExecutionContext.runTryCode(Object userData) at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading._ThreadPoolW…

06/25/2009 12:25:20.46* w3wp.exe (0x0BA8) 0x16F8 Windows SharePoint Services General 0 Unexpected …aitCallback.PerformWaitCallbackInternal(_ThreadPoolWaitCallback tpWaitCallBack) at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback(Object state)

 

06/25/2009 12:27:16.49 w3wp.exe (0x0BA8) 0x1CB4 Windows SharePoint Services Workflow Infrastructure 88xr Unexpected WinWF Internal Error, terminating workflow Id# 02334a17-3211-4d30-902f-bf34e20354c6

06/25/2009 12:27:16.49 w3wp.exe (0x0BA8) 0x1CB4 Windows SharePoint Services Workflow Infrastructure 98d4 Unexpected System.Workflow.Runtime.Hosting.PersistenceException: Object reference not set to an instance of an object. —> System.NullReferenceException: Object reference not set to an instance of an object. at DP.Sharepoint.Workflow.Common.RemoveListItemPermissionEntry(SPListItem item, String principalName, Boolean breakRoleInheritance) at DP.Sharepoint.Workflow.PermissionsService.<>c__DisplayClass1.<ProcessGrantRequest>b__0() at Microsoft.SharePoint.SPSecurity.CodeToRunElevatedWrapper(Object state) at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass4.<RunWithElevatedPrivileges>b__2() at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode) at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param)…

06/25/2009 12:27:16.49* w3wp.exe (0x0BA8) 0x1CB4 Windows SharePoint Services Workflow Infrastructure 98d4 Unexpected … at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode) at DP.Sharepoint.Workflow.PermissionsService.ProcessGrantRequest(PermissionRequest pr) at DP.Sharepoint.Workflow.PermissionsService.Commit(Transaction transaction, ICollection items) at System.Workflow.Runtime.WorkBatch.PendingWorkCollection.Commit(Transaction transaction) at System.Workflow.Runtime.WorkBatch.Commit(Transaction transaction) at System.Workflow.Runtime.VolatileResourceManager.Commit() at System.Workflow.Runtime.WorkflowExecutor.DoResourceManagerCommit() at System.Workflow.Runtime.Hosting.WorkflowCommitWorkBatchService.CommitWorkBatch(CommitWorkBatchCallback commitWorkBatchCallback) at System.Workflow.Runtime.Hosting.DefaultWorkflowCommitWorkBatchSer…

06/25/2009 12:27:16.49* w3wp.exe (0x0BA8) 0x1CB4 Windows SharePoint Services Workflow Infrastructure 98d4 Unexpected …vice.CommitWorkBatch(CommitWorkBatchCallback commitWorkBatchCallback) at System.Workflow.Runtime.WorkflowExecutor.CommitTransaction(Activity activityContext) at System.Workflow.Runtime.WorkflowExecutor.Persist(Activity dynamicActivity, Boolean unlock, Boolean needsCompensation) — End of inner exception stack trace — at System.Workflow.Runtime.WorkflowExecutor.Persist(Activity dynamicActivity, Boolean unlock, Boolean needsCompensation) at System.Workflow.Runtime.WorkflowExecutor.ProtectedPersist(Boolean unlock)

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

No Tags

Send to Kindle

Why InfoPath rocks

Send to Kindle

At the San Diego Best Practices SharePoint conference, I sat quietly and listened to the governance godfather himself, Robert Bogue, discussing with the other SharePoint "big kids" various reasons why InfoPath sucks in many situations and some of its current design faults. I mean, anybody who has used forms services knows what a pain redeployment is, how much of a pain managed code is, the horrible performance of web services, branding crap and the like. Thus, all were perfectly valid points.

But irrespective of how correct those points are, I’d like to categorically state why InfoPath 2007 and Forms Services are the best pieces of technology that Microsoft has ever invented.

The simple reason is this.

My wife likes it!

To fully explain the implications of why InfoPath rocks, I have created an IBIS issue map that makes the argumentation quite clear.

image

I rest my case.

 

Thanks for reading

Paul Culmsee

www.sevensigma.com.au

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

No Tags

Send to Kindle

Leave form article 6 up at SharePointMagazine.net

Send to Kindle

Due to a sudden burst of motivation (that has since evaporated :-), I finished off part 6 and part 7 of the InfoPath "leave form tribute" series. Arno has just posted part 6 which you can read here:

http://sharepointmagazine.net/technical/a-tribute-to-the-humble-leave-form-part-6

Looking for part 7? Ask Arno nicely as I’m not sure when he plans to publish that one 🙂

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

No Tags

Send to Kindle

A tribute to the humble leave form part 5

Send to Kindle

Arno over at Sharepoint Magazine has published part 5 of my "Leave Form Tribute" series of articles. This latest article is a bit of a graduation from the first four, that were pitched squarely at new users. In part 5 we are now getting into the more advanced stuff – like attempting to explain web services to the masses 😉 I have found this to be a very challenging article to write.

Read it now!

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

No Tags

Send to Kindle

Part 4 in the "Leave Form Tribute…" series posted

Send to Kindle

Hi all

Arno over at SharePoint Magazine has published part 4 of my "Tribute to the humble leave form" series. This series is aimed at beginners and those starting out in SharePoint. This particular post covers publishing to forms services for the first time.

cheers

Paul

p.s Want a new certification? Try the CCLFD 🙂

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

No Tags

Send to Kindle