Background

Hi and welcome to the third article that describes how to make a PowerApps-based activity feed based on SharePoint search results. If Google’s search algorithm has landed you here for the first time, then I suggest going back to parts 1 and 2 for background and context. What we are building can be seen in the picture below highlighted red..

Now there are 5 parts to this solution and in this third post, we are in the home stretch because we have done the first three steps and we are halfway through part 4.

1. Setting up SharePoint search
2. Querying the search index via a PnP PowerShell script
3. Creating the Azure function
4. Creating the custom connector
5. Testing in PowerApps

I covered parts 1 and 2 in the first post where we created a Result Source in SharePoint and and tested a PowerShell script that uses it to bring back the latest 20 search results. In the second post, we created and tested an azure function, and just deployed our custom connector as shown below:

Before we rush off to PowerApps to bask in our awesomeness, let’s test our custom connector first. Click the pencil button to edit your freshly minted connector. Using the breadcrumb, navigate to the Test menu.

You will be presented with a Test operation screen and (very likely) a prompt to create a connection. While newbies might find it weird to ask for a connection just after we just made one, it is worth paying close attention to the terminology used. We actually created a custom connector, not a connection. The PowerApps team describe it like this:

Data is stored in a data source, and you bring that data into your app by creating a connection. The connection uses a specific connector to talk to the data source

Therefore, it stands to reason that to test a connector, one has to create a connection. So let’s do that right now by clicking the + New connection button. On the resulting confirmation dialog box, click Create.

Now you will be redirected to the connections screen. This is somewhat counter-intuitive given at first, since we actually wont do anything with the connection from this screen, but if it makes you feel good, feel free to look for a new connection with the same name as your connector as shown below. This means all went to plan…

Using the left side navigation, head back to the custom connections screen. Edit your connector once again, and navigate back to the Test screen. This time the Test Operation screen will show your new connection listed…

Scroll down and you will see an Operations section. The code query string parameter that we worked with in part 2 is listed.

Note: It should be greyed out and not editable. If it is editable, you have likely forgotten to change the visibility parameter when we created the custom connector in part 2.

Click the Test operation button to give it a whirl. After a few seconds, you should see a successful response. Looking in the body of the response, you can see the Json search results produced by your Azure function. Yay!!

Part 5 – Testing in PowerApps

Now that we have created a connection and used it to test our custom connector, it is time to move to PowerApps. To keep things simple, lets build an app from scratch to show this working. In PowerApps studio, create a blank phone app and then follow the steps:

1. From the View menu, choose Data sources and click the Add data source from the Data panel. From the list of connections, find the connection we created moments ago to test the connector. Click it and it will be added to the app.

Note the name of your connector matches the Postman collection we created in part 2 of this series. Make a note of this because we will need it momentarily…

2. From the Insert menu, choose Icons and choose the Reload icon.

Select the refresh icon you just created and choose the OnSelect property. Type in “ClearCollect(Feed,” and then enter the name of the data source you just added (mine is called “CATalogue”). The intellisense in PowerApps will also present the name of your function that you specified back in part 2 when creating the postman collection. In my example it was called “GetMewsFeed”.

Note: If you are presented to provide a code as a parameter to your data connection as shown below, you missed an important step in the custom connector setup. Recall in part 2 I mentioned that the code parameter in the custom connector had to be changed from none to internal. For now, you can add the same code that was generated as part of the Azure function URL, otherwise update the custom connector.

3. Click the new icon to trigger a data refresh and then navigate to Collections from the File menu. You should see a collection called Feed with some of the data returned. If you see a table of data then congratulations! You have successfully queried SharePoint search and returned the data to PowerApps via an Azure function.

The columns that are of interest to us for the purposes of this post are:

• Title
• ListID
• ModifiedBy
• LastModifiedTime
• OriginalPath

So now that we have gotten some data, let’s now show how we can display it in the form of a data feed.

4. From the Insert menu, choose Gallery and pick a Blank flexible height gallery. In the data panel, choose the Feed collection from the Data source dropdown.

5. Ensure your Gallery is still selected via the left side navigation. Click the pencil icon inside the gallery and then from the Insert menu, choose Label. This label will bind to a column in your data source and you should see something like the second screen below, where data is repeated down the page. If the label control is still selected, the Text property will be set to something like ThisItem.Author.

Modify the Text property of the label to “Updated by ” & ThisItem.ModifiedBy & ” on ” & ThisItem.LastModifiedTime. The result is a line that shows who modified the item in the feed and when they modified it. While you are here, rename this label control and call it something more meaningful. In PowerApps I use Hungarian notation as it makes it easier to understand complex formulas later. Thus I called this one lbWhoWhen. Finally, resize the gallery to fit the screen and drop the font size of the label so everything fits on one line…

6. Add another label to the gallery (remember to use the pencil icon to ensure the new label control is added to the gallery and not the screen). Place it below the label you created in step 5 and set it’s text property to ThisItem.Title. Adjust the font size and position it appropriately. Rename this label to something meaningful (I used lbTitle ).

7. Add an icon to the screen and choose Rectangle from the Shapes section. Resize it so that it looks like a horizontal line (Set the Height property to 1) and change its colour to grey either via the toolbar or via setting the Fill property to “LightGray”. Also rename it to something like Separator.

Preview the app and the feed should start to take shape…

Hopefully you can see by now that we are more or less done, as we are now moving into aesthetics in terms of how our feed is to look. So I will round out this post by showing you a couple of tricks that I think make this feed more interesting…

6a. Pimping your feed (easy)

If you look closely at the sample data I used to build this feed, it includes images as well as list item content. In fact, to remind you of where we started, I mentioned that my daughter wrote an app called CAT-alogue which used three SharePoint lists/libs: There was a list called Cat Directory, a list called CatImageRegister and a library called CatImages.

So let’s make the following enhancements:

1. If the search results contain a Description field, use that instead of the Title field (the Cat Directory list utilises the built-in SharePoint site column called Description, which happens to be a managed property in SharePoint search)
2. If the search result is the image library, display the image rather than the file name.

Handling description requires a couple of changes to our Title label. First, we need to set Auto height to On because the description field is multi-line. This is easiest done by toggling it to On via the right-side properties panel when selecting the label as shown below:

Next, change the Text property in the label to If(IsBlank(ThisItem.Description),ThisItem.Title,ThisItem.Description). This tells PowerApps to display the Title field if no description has been crawled. The effect should be immediate…

But this is a flexible height gallery, so let’s take advantage of this. We will now set the position of our separator rectangle to be based on the height of the Title/Description label. Remember this label is now set to auto-height… To do this you will need to take note of the name of your Title/Description label. Mine is called lbTitle.

Set the Y property of your rectangle to: lbTitle.Y + lbTitle.Height + 20. Previewing this change should result in a change to your screen for the better…

6b. Pimping your feed (less easy)

Right! So what we can do about displaying images in the feed? Turns out this is one of those examples that might seem easy, but is actually quite tricky and also not particularly bandwidth efficient either. It also has a lot to do with how you set up your data in SharePoint, so some refresher is needed before we start.

First up, Ashlee’s CAT-alogue app is structured with 3 lists. There is a list called Cat Directory, a list called CatImageRegister and a library called CatImages. The second list is the one that matters here, as each time a photo is taken, the metadata is stored in this list rather than the CatImages library. It then links to the actual photo in CatImages as shown below. Note that the title field for CatImageRegister is the name given to the photo in CatImages.

Why set it up this way you ask? Well for a start, PowerApps cannot connect to libraries yet, and these days, PowerApps is smart enough to be able to retrieve images from a list like this. So let’s take advantage of this…

Also some more useful nuggets of context: another thing we can take advantage of is the built-in SharePoint search property called ListID. Way back in Part 2 when we did the PowerShell script, I included a line that specified some search managed properties to be returned that are not by default. The line was:

$returnproperties = @(“ContentType”,”ListID”,”ModifiedBy”, “ListItemId”)

Specifically, the property of ListID is what matters here. This property returns the unique ID of each list. Can you see where I am going with this yet?

From the Insert menu, choose Media from the toolbar and insert an Image control into the gallery. Rename it to CatPhoto and resize it and place it under the first label.

Set the Visible property to only display if the list ID of the feed item is the CatImageRegister list. It will be something like: If(ThisItem.ListID=”11d474d0-5725-4ec5-b273-8bb09a3e097e”,true,false). Of course the ID you need to specify depends on our setup. This should now hide the image control when the ListID does not match my CatImageRegister list.

Next, let’s get the image control to display the photo itself. To do this, we will connect to SharePoint using the native PowerApps connector. From the View menu, choose Data Sources and add a SharePoint data source. Choose the SharePoint list that holds a link to your photos (in my case, CatImageRegister).  

Now set the Image property of the Image control to something like this: LookUp(CatImageRegister,Title=ThisItem.Title).CatImage. Although things look ugly, you should see the photos…

So what we did here was utilise the PowerApps Lookup function which returns first record in a data source that satisfies a formula. In my case I wanted to bring back the record where the Title field in CatImageRegister matched the Title field for each row in the feed gallery. From here, I specified that I specifically wanted the CatImage property and voila!

Next, we make the feed look less sucky by making the position of the Title/Description label more dynamic. If an image is displayed, we will reposition the Title/Description label to the right of the image. To do this, set the X property of the Title/Description label to: If(CatPhoto.Visible,CatPhoto.X + CatPhoto.Width + 10,0). This tells PowerApps to shift the label to the right of the image control when it is visible and sets the X position to 0 when its not.

Now let’s set the Y position. In this case, if the image control is visible, let’s vertically align the label to the middle of the image control. But if the image control is hidden, let’s set the label to positioned relative to the top label. To do this, set the Y property of the Title/Description label to: If(CatPhoto.Visible,(CatPhoto.Y + CatPhoto.Height) / 2,lbWhoWhen.Y + lbWhoWhen.Height + 10).

Nice… the feed is looking a lot tidier…

Now let’s fix the separator rectangle we created earlier. Here, if the image control is visible, the Y position of the separator should be offset from it, however if the image control is hidden, then it should be based on the Title/Description label. To do this, set the Y property of the separator to: If(CatPhoto.Visible,CatPhoto.Y + CatPhoto.Height + 20, lbTitle.Y + lbTitle.Height + 20).

Preview the app and you should have yourself a nice feed!

Conclusion

At this point, I think we have covered enough to give you a really good feel for how PowerApps an play nicely with SharePoint search and Azure functions. While I fully accept this is an advanced scenario for the typical citizen developer, it is still a very low-code scenario and working knowledge of Azure functions and PowerApps/Flow custom connectors is very handy indeed.

I think it also shows that these platforms are highly flexible. This activity-feed scenario was one of those tricky scenarios that in the past, might have been added to the “too-hard” basket or resulted in custom development work.

Finally, I have recorded a series of videos to accompany these posts. I split the videos up into easy to consume chunks. The first one can be found below:

Thanks for reading

Paul Culmsee

Hiya

I have been recording various videos over time of some advanced PowerApps and Flow concepts/solutions. All of these are either workarounds for current limitations in PowerApps or Flow or work I have done with my daughter, Ashlee.  I have listed each here with explanations…

How to Save Photos from PowerApps to SharePoint via Flow

This video outlines a robust and flexible method for uploading photos from PowerApps to SharePoint. At the time of writing, it is the best option despite having to create OpenAPI files.

Calling Cognitive Services Vision API from PowerApps via Flow

This video demonstrates a simple receipt tracker that uses the OCR capability of Microsoft cognitive services to find price information from a scanned receipt.

How to set SharePoint list permissions using Flow

This video shows the high level view on how Flow can be used to set SharePoint permissions, much like an app step that is used in SharePoint Designer. It also demonstrates the idea of breaking up flows into reusable chunks – called service flows.

It’s not a Flow, nor a Proxy… It’s a Floxy!!

This is an example of utilising flow to display document library content in PowerApps. I also wrote a detailed post about this one…

How To Rickroll Your Friends Using PowerApps

A funny app with some very clever design considerations. This was actually done by my daughter, Ashlee. She explains how she did it below…

Paul and Ashlee on PowerApps

More nerdy fun with my daughter, who is already an accomplished PowerApps coder as you will soon see. In this video, she build me a sophisticated audit/checklist app using Microsoft PowerApps and Flow. This app demonstrates offline support, calling external API’s and photo handling.

and finally….the famous fidget spinner…

Build a FidgetSpinner using PowerApps

Demonstrating the power of the PowerApps platform for citizen developers, Ashlee won a contest from Microsoft to create a fidget spinner using PowerApps. In this video, Ashlee explains to me how she built the app and shames me for my dodgy high school maths…

p.s don’t miss out the Solar System PowerApps by MVP Daniel Christian, who was inspired by Ashlee’s fidget spinner. Amazing stuff…

I think these videos highlight the flexibility and power of this platform. Let me know if you would like me or Ashlee to record others or expand on them!

Paul Culmsee

Hi and welcome to the final post in this series of articles that examined how we can work around an unsupported function in PowerApps and Flow. Along the journey we have learnt about app principals, the SharePoint REST API and written some crazy JSON.

In this final post, we will tie up a few loose ends. Currently the flow hard-codes the term ID from managed metadata when in fact, we want it to be sent from PowerApps. I will also also give you some insight into handling more complex situations, like multiple managed metadata fields as opposed to a single one.

But first, the (giant) weak spot…

As I write this I if any of you, as you read the first 3 posts, realised the weakness to this method. A strong hint was given in the last post. Recall that the JSON to update a managed metadata field required a term GUID…

{
   "__metadata": { "type": "SP.Data.Coffee_x0020_Shop_x0020_RatingsListItem" },
   "Bean":
   { "__metadata": {"type":"SP.Taxonomy.TaxonomyFieldValue"},
     "TermGuid":"0937fbc2-0dfe-439e-a24f-ba6d13897abd",
     "WssId":"-1"
   }
}

Now right now in PowerApps, there is no managed metadata control. This means you cannot browse a term store like you can in SharePoint. Among other things, one likely reason for this is that there is no REST API support for managed metadata. This has been a constant source of frustration for many as you can see in the 761 votes (as I type this) in uservoice.

What’s the implication of this limitation? For a start, there is no way we can build a REST call that will return us all of the terms in a term set. We have to either hard-code the term GUID’s in our application (ugh) or find another way to get the list of terms (ugh again). One thing that should be noted is that sometimes, hard coding terms and GUID’s isn’t as bad as it sounds. For example, you might have a term set for your corporate offices in certain countries. Since those terms are unlikely to change quickly, one can embed their GUID’s into PowerApps without too much concern.

For the first part of this article, we will use this approach and hard-code our term set of coffee beans into our Powerapp. Then we will modify our flow to expect this parameter from PowerApps.

Back to PowerApps…

If you go back to the PowerApp we built way back in part 1 and also the term set of coffee beans, they looked like this:

To hardcode our terms into PowerApps, I am going to use the ClearCollect function to create a collection called BeanDetails. I have grabbed the term label and GUID from managed metadata and constructed this function:

ClearCollect(BeanDetails,
{
   Name:  "Fiori",
   Id:  "96a085d2-ed7d-4a75-8d63-c6ca56d0b358"
},
{
   Name:  "Jungle Coffee",
   Id:  "eb04988d-acc4-4bde-9861-506e90de94c5"
},
{
   Name:  "Alfredo",
   Id:  "38db966f-3b0c-4872-a7d3-982ecd8528dd"
},
{
   Name:  "Death Wish Coffee",
   Id:  "e33c65f9-4202-40d7-89f2-aa1a1016ad9f"
})

Next, I use the OnStart property of the screen and paste the ClearCollect function as shown below.

Now you will need to save, close and reopen the app for this to take effect. Alternatively add a temporary button and use the above code on the OnSelect property. Either way, you should be able to see your terms and GUID’s when going to Collections from the File menu…

Now that we have our term set stored in PowerApps, let’s add a dropdown list to the screen that lists our coffee beans. Choose Insert > Drop Down and set its Items property to BeanDetails.

Now you should be able to see the choice of beans in the dropdown as shown below:

Next, we have to go back to Flow and modify it to accept a term GUID as a parameter (if you recall in part 3 the term is currently hard-coded).

Tweaking Flow…

If you have followed our Flow adventure so far, you should have a flow action called Set Test Term GUID that looks something like this:

First, rename this flow step to Get Term GUID and then delete the GUID that we previously had in the Value field. From the Dynamic Content pane, choose Ask in PowerApps.

Update your flow and before returning to PowerApps, click the Run Now button. Ensure you are now prompted for three parameters, rather than the original two.

Back to PowerApps…

Now in PowerApps, we have to delete and flow and re-add it so it finds the new parameter we just added.

Select the Submit button and review the OnSelect property. It should show the function below…

Now go to View > Data Sources and remove the NewCoffeePlace data source. You should see an error appear on the Submit button. It will say “Invocation of unknown or unsupported function”. Makes sense right? After all, we just took away its connection to flow.

Once again make sure your Submit button is selected, and select Action > Flows. Choose the flow called New Coffee Place. In a few moments, it will ask you to complete the parameters in the formula bar. If you did this correctly, you should see a prompt for a third parameter – the term GUID.  

The third parameter will be the GUID that matches the term in the dropdown list. The formula for this is:

Now to explain Dropdown1.Selected.Id, remember that we set the Items property of the dropdown to BeanDetails collection, which has two columns, ID and Name. Essentially, by specifying Dropdown1.Selected.Id, whichever bean name is the currently selected one in the dropdown, the GUID will be sent to Flow…

Test this by putting the Powerapp into preview and adding a new café. If all things go to plan, you will see your new entry along with the managed metadata term. Yay!!

Hard-coding sucks – surely there a better way?

At this point you might still be hating the fact we had to hard-code the term ID’s into our app. I can sympathise, as it irritates me too. So is there a way to dynamically load the term set into PowerApps? Turns out there is, provided you use an Azure function along with PnP PowerShell.

I won’t cover this in detail here, unless there is sufficient demand, but I will explain the high level overview.

1. Set up an Azure function using PowerShell
2. Load PnP PowerShell modules
3. Write a PowerShell script to connect to your tenant and pull the terms from the term set.
4. Convert the terms to JSON output
5. Create a custom connector for PowerApps to consume it.
6. Add the custom connector to your app, and pull down the latest term set on startup.

Now I wrote another blog post where I went through another example of using Azure functions with PowerApps, and that covers a lot of detail on steps 1, 2, 5 and 6.

In relation to step 3 and 4, here is some PowerShell code sample to do this that you can use as a starting point. Be warned though… you will need to have some understanding of PnP PowerShell to make sense of it!


Import-Module "D:\home\site\wwwroot\modules\2.19.1710.1\SharePointPnPPowerShellOnline.psd1" -Global
$requestBody = Get-Content $req -Raw | ConvertFrom-Json
$name = $requestBody.name
$username = "paul@culmsee.onmicrosoft.com"
$password = $env:PW;
$siteUrl = "https://culmsee.sharepoint.com"
$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
$creds = New-Object System.Management.Automation.PSCredential ($username, $secpasswd)
Connect-PnPOnline -url $siteUrl -Credentials $creds
$tg = Get-PnPTermGroup -Identity 2830778b-dbfa-4441-bb45-0cbb97b582e7
$ts = Get-PnPTermSet -TermGroup $tg -Identity "Coffee"
$tl = Get-PnPTerm -TermSet $ts -TermGroup $tg -Includes Terms
foreach ($term in $tl) {
   $terms+=$term.terms
}
$output = $terms | Select Name, Id | ConvertTo-Json
Out-File -Encoding Ascii -FilePath $res -inputObject $output


Like I said, if you are interested in knowing more about this, let me know and if there is sufficient demand, I will make a dedicated blog post about it…

Handling multiple managed metadata columns…

I will end this series by showing you how to handle updating multiple managed metadata columns. This is an advanced topic, but is a good test for developing JSON manipulation skills in Flow. Like the previous section I will cover it in a high level only and leave it to you to figure it out for your environment…

So far we have updated a single column, but my real-world project that led to this series actually had 3 columns that were managed metadata. What made this interesting and tricky was that these columns were entirely optional. The complexity is easy to illustrate by looking at what a JSON payload looks like when updating multiple columns. In the example below, I am updating 3 columns called ProjectType, ProjectPhase and ProjectElement respectively…

{
   "__metadata": {
     "type": "SP.Data.ProjectListItem"
     },
   "ProjectType": {
     "__metadata": {
     "type": "SP.Taxonomy.TaxonomyFieldValue"
     },
     "TermGuid": "d4e4494f-34a4-4278-b0ee-7eba372a3f80",
     "WssId": "-1"
   },
   "ProjectPhase": {
     "__metadata": {
     "type": "SP.Taxonomy.TaxonomyFieldValue"
     },
     "TermGuid": "78d647c7-8b92-4ee0-a29d-22346b1af560",
     "WssId": "-1"
   },
   "ProjectElement": {
     "__metadata": {
     "type": "SP.Taxonomy.TaxonomyFieldValue"
   },
     "TermGuid": "6f9f653f-09ec-4771-9063-676d16734b37",
     "WssId": "-1"
   }
}

Contrast the above example with the one below where only one is updated…

{
   "__metadata": {
     "type": "SP.Data.ProjectListItem"
   },
   "ProjectElement": {
     "__metadata": {
     "type": "SP.Taxonomy.TaxonomyFieldValue"
     },
     "TermGuid": "6f9f653f-09ec-4771-9063-676d16734b37",
     "WssId": "-1"
   }
}

So the trick is to build a flexible enough flow that could generate the JSON needed to update 1, 2 or 3 columns. Without knowing whether the column would have a supplied value, meant we have to find a way to handle any combination. The pattern I ended up using was to build the JSON for each of the columns individually before joining them together. Here’s how…

First, I used an Initialize Variable flow action to create the base JSON for each column. The key was to make this conditional, so if the column value was empty, I added an empty string. E.g.

The expression used for the value of this variable was this crazy looking Workflow Definition Language Expression:

if(not(empty(triggerBody()['GetProjectTypeTermID_Value'])), concat('"ProjectType":{"__metadata":{"type":"SP.Taxonomy.TaxonomyFieldValue"},"TermGuid":"',triggerBody()['GetProjectTypeTermID_Value'],'","WssId":"-1"}'),'')

Basically this grabs the term ID for a column (in the above example the columns is called ProjectType). I test if the column value is supplied – which is what the “if(not(empty” bit does. I then then construct the necessary JSON to update that managed metadata column. (Note if you need a refresher on the JSON in this function, review part 3)

I repeat this action for the other columns, and then perform the following steps in Flow to construct the complete JSON…

First I initialize an array variable that I call JsonPayloadArray.

Next I add the outer JSON for a managed metadata update to the array using the Variables – Append to array variable workflow task…

Here I am using the concat() function because I need to use the ListItemEntity variable. Note, if you have forgotten about ListItemEntryFullName property,  re-read part 3.

concat('{ "__metadata": { "type": "',variables('ListItemEntity'),'" }')

Next, I add the JSON for each column to the array, provided it is not null. I do this by adding a condition for each column.

The first image below shows the three conditions and the second image shows an expanded view of one of them. You can see in the example below, if a ProjectType was supplied, I add the variable initialized in the above steps to the JsonPayloadArray variable….

Finally, I use the Join() function to take this array and turn it back into a single string, delimited by commas. This elegantly handles the situation in JSON where the last name/value pair does not have a comma separator.  I do this by initializing a new variable called JsonPayload as a string as follows…

concat(join(variables('JsonPayLoadArray'),','),' } ')

Conclusion

This is one of those series that I know only a few people will get some use out of. Hopefully this whole issue of managed metadata will be gone by year-end and these approaches are not necessary. In any event the techniques outlined in these four posts apply to various other scenarios, so learning about JSON manipulation using Flow, or talking to SharePoint via REST/App principals might come in handy for you in other situations.

As for me, this is probably the geekiest series I have written in a long time! I think I better get back to management consulting now!!

Thanks for reading

Paul Culmsee

www.hereticsguidebooks.com

Hi all

Welcome to part 3 of my posts that are intended to teach people more about the intricacies of Flow, SharePoint Web Services and oAuth. The cover story for this journey was the issue that presently, managed metadata fields are not supported natively in PowerApps nor Flow.

As a result, we are taking an approach where we update managed metadata values by talking to the SharePoint REST web service.

To recapitulate our journey, we have managed to achieve the following thus far. We have a basic powerapp, and a flow that now has an access token that will allow us to update the managed metadata column via API. If you are new to this series, then I strongly suggest going back and reading parts 1 and 2, before continuing here…

A SharePoint Web Service Interlude…

Those of you who got past part 2, and think the hard stuff is over, think again. You are about to take a dive into the weird world of talking to SharePoint via API, so go get yourself a coffee and I will do my best to provide an accessible explanation to what is going on.

To start with, let’s do a couple of general queries to SharePoint via API and get a feel for things. To do this, use the Chrome browser and sign into your SharePoint site collection. Once signed in, try the following URL. Note, if you are using a different list name to the one I have been using in this series, adjust accordingly:

https://<site collection url>/_api/web/lists/GetByTitle('Coffee Shop Ratings')

If this worked, you will be rewarded with a ton of data related to the Coffee Shop Ratings list. It won’t be overly meaningful in the Chrome output, but rest assured this is a good thing…

Now let’s make things a little less scary by being more specific about the information we want to retrieve…

https://<site collection url>/_api/web/lists/GetByTitle('Coffee Shop Ratings')/items?$select=Title

This API call is basically saying “get all items from this specified list, and only show me the Title column for each item” (that is what the $select=Title bit is doing). While the formatting in Chrome sucks, you can nevertheless see the output being returned in XML format. Note the title columns below showing the café names.

Now let’s get specific and select a particular item from the list by its ID. I can achieve this in the following way…

https://<site collection url>/_api/web/lists/GetByTitle('Coffee Shop Ratings')/items(16)?$Select=Title

This call is still only asking for the Title column from the list ($Select=Title), but now it only wants the item 16 in the list. I happen to know in my list this is Café Gelato and the output confirms this…

Finally for now, let’s bring back the 3 columns we are using, including the one that’s been causing us all the trouble… I do this by specifying multiple columns in the $select bit at the end of the URL.

https://<site collection url>/_api/web/lists/GetByTitle('Coffee Shop Ratings')/items(16)?$Select=Title,Rating,Beans

This time I will format the XML output a bit nicer for you and strip out some uninteresting stuff. In the <m:properties> section, you can see the information returned about our 3 columns…

A cursory glance shows the Title and Ratings columns are pretty simple. The ratings column differs from title in being numeric (m:type=”Edm.Double”) but still is a single line and fairly easy to understand. But take a look at the Beans column. It is a lot more complex than the others in that instead of being a single value, this column actually consists of three properties, Label, TermGuid and WssId.

At this point I should say that the SharePoint List API has a lot more to it than the simple examples above. If you are interested in learning more then start here. The key thing I want you to take away from this section is this: We are going to use Flow to perform queries just like we have done. We will query the lists web service to get some data and more importantly, we will also use calls to the web service to update the managed metadata column.

So let’s have a look at how we can do that…

Updating a List Item via REST API

Note: when I wrote this I was not aware you could reduce the amount of JSON manipulation you have to do via the odata=nometadata directive that Marc wrote about. I strongly suggest you use that article to sense-check what I write about here.

First up we need to understand the general pattern to updating a list item using the API. We need to issue a POST call to the list web service and in the body of our post, send our instructions about what to update. The URL is unchanged from what we have been working with…

http://<site collection url>/_api/web/lists/GetByTitle('Coffee Shop Ratings')/items(item id)

In terms of the format of the update instruction, the example below shows updating the Title field. While some bits of it might not make sense right now, we can see that the Title column is being updated to the value “TestUpdated”…

{ '__metadata': { 'type': 'SP.Data.TestListItem' },
  'Title': 'TestUpdated'
}

So what is the SP.Data.TestListItem? A hint comes from Microsoft’s own documentation that states: “The ListItemEntityTypeFullName property is especially important if you want to create and update list items. This value must be passed as the type property in the metadata that you pass in the body of the HTTP request whenever you create and update list items”

We will return to this mysterious ListItemEntityTypeFullName when we start building the flow, but for now lets park it and look at what needs to be sent in the HTTP Headers when posting to the API..

Authorization: "Bearer " + accessToken
IF-MATCH: etag or "*"
X-HTTP-Method:"MERGE"
accept: "application/json;odata=verbose"
content-type: "application/json;odata=verbose"

Wow – that’s quite a bit to do, and we have not even gotten to the Managed Metadata column yet! So let’s take a look at that now since we have already dug ourselves a bit of a theory-hole. Soon enough we will put all of this stuff into practice…

Updating a Managed Metadata Column

Now that we have seen the anatomy of a POST to update a list item, let’s now look at example of updating a managed metadata column. In a nutshell, the body of the POST looks like this: Note that in this example I have specified the ListItemEntityTypeFullName property for my Coffee Shop Ratings list. Don’t worry, I will show you how to find this out in the next section.

{
   "__metadata": { "type": "SP.Data.Coffee_x0020_Shop_x0020_RatingsListItem" },
   "Bean":
     { "__metadata": {"type":"SP.Taxonomy.TaxonomyFieldValue"},
     "TermGuid":"0937fbc2-0dfe-439e-a24f-ba6d13897abd",
    "WssId":"-1"
   }
}

Unlike the title column above,  here we need to set two parameters – a TermGuid and a WssID. The former is the GUID of a term in managed metadata and can easily be found when looking at a term: for example:

The WssID parameter you can ignore. We need to specify it, but we will always be putting “-1” in the value. If you really want to know what it does, check this post. Also for those who have read other articles on this topic and are wondering why I left out the Label parameter… the answer is that with my testing, it was not necessary, so I omitted it.

So let’s summarise our journey so far… we need to do the following:

So let’s get back to the land of Flow and finish this!

Finishing off Flow…

1. Picking up from where we last left off, add a HTTP action. Rename it to “Get ListItemEntity”, set it to a GET, and set the URL to:

https://<your site collection>/_api/lists/GetByTitle(%27Coffee%20Shop%20Ratings%27)?$select=ListItemEntityTypeFullName')

in my example the site collection is:

https://culmsee.sharepoint.com/_api/lists/GetByTitle(%27Coffee%20Shop%20Ratings%27)?$select=ListItemEntityTypeFullName')

Check the URL carefully. You can see that I am using the $select to only bring back the property we are interested in… ListItemEntityTypeFullName. Also note that I have encoded the spaces in the list, I.e. ‘Coffee Shop Ratings’ has now become ‘Coffee%20Shop%20Ratings’.

2. In the headers section of the request, add:

• a key called Accept, with a value of application/json; odata=verbose
• a key called Authorization, with a value of “Bearer “ and then the AccessToken variable from the Dynamic Content panel…

Now before we get to the final bit, we have another minor theory interlude. The output of the web service you just created follows a particular structure. An example output is below and I have highlighted the bit we need.
{"d": 
   {"__metadata":
     { "id": <url>,
     "uri": <url>,
     "etag":"id",
     "type":"SP.List"
   },
   "ListItemEntityTypeFullName":"SP.Data.Coffee_x0020_Shop_x0020_RatingsListItem"
   }
}

This means we will need to parse this output and just grab the ListItemEntityTypeFull name parameter. The expression in Flow to do this will be:

actionBody(<Name of the workflow action you just created>)['d']['ListItemEntityTypeFullName']

So using my tenant, the expression is:

actionBody('Get_ListItemEntity')['d']['ListItemEntityTypeFullName']

Note: if you want to know more about these expressions, this is the place to go…

So with that bit of context, lets continue…

3. Add an Initialize Variable action and rename it to “Set ListitemEntity”. Name the variable ListitemEntity and make it a string. In the value, use the expression builder and use the expression I outlined above:

The final task…

Now we get to the final and most mind-bending piece of the puzzle. It is time to build the JSON payload that we will POST to SharePoint to update the managed metadata column. Let’s begin this bit with the end-in-mind and work back.

First up, we need to make a variable to store the Term ID that we will write to managed metadata. This eventually come from PowerApps, but right now we need to test so we will hard code a term.

1. Add an Initialise Variable action and name it “Set Test Term ID”. Call the variable TermGuid and make it a string. Set the value to any term in your managed metadata term set that the Beans column is using. In my case, the GUID 96a085d2-ed7d-4a75-8d63-c6ca56d0b358 refers to the term “Fiori”.

2. Add an Initialise Variable action and name it “Build Update Managed Metadata JSON payload”. Call the variable JsonPayload and make it a string. In the value field, type in

{ "__metadata": { "type": "'

…and then add the variable ListItemEntity from the Dynamic Content tab.

3. Next append the following text to the value…

" },"Beans":{"__metadata":{"type":"SP.Taxonomy.TaxonomyFieldValue"},"TermGuid":"

4. From the Dynamic Content tab, insert the TermGUID variable that you created in step 1

5. Next append the following text to the value…

", "WssId":"-1"}}

This completes the JSON payload that will update the managed metadata column. To recap, it has built the following string which is entirely consistent with the theory at the start of this article.

{ "__metadata": { "type": "SP.Data.Coffee_x0020_Shop_x0020_RatingsListItem" },
   "Beans":
     {"__metadata":{"type":"SP.Taxonomy.TaxonomyFieldValue"},
     "TermGuid":"96a085d2-ed7d-4a75-8d63-c6ca56d0b358",
     "WssId":"-1"
   }
}

Now bear in mind this payload is a string and not in JSON format yet. We will take care of that in the final HTTP call.

6. Add a HTTP action, name it “Update Managed Metadata” and set the method to POST. In the URL field, enter the following:

https://culmsee.sharepoint.com/_api/lists/GetByTitle('Coffee%20Shop%20Ratings')/items(

7. From the Dynamic content menu, find the CoffeePlace content, find and insert the ID field and then add a close bracket to the end…

In case you are wondering, we already know the ID of the newly created coffee shop entry because of the SharePoint – Create Item action that we created way back in part 1. Fortunately for us, that action returns the ID of the newly created item, making it easy for us to specify it in our API call. Neat eh?

8. In the headers section, add an entry for each of the headers I described earlier in this article, namely:

• IF-MATCH: etag or “*”
• X-HTTP-Method:”MERGE”
• accept: “application/json;odata=verbose”
• content-type: “application/json;odata=verbose”

9. Now let’s add the access token that took up the bulk of part 2. In the headers, add a key called Authorize and in the value, type in:

“Bearer “ (showing quotes to illustrate a space on the end)

Then insert the variable AccessToken from the dynamic content pane…

10. Finally, let’s convert our payload to JSON and send it off. In the Body field, use the expression builder and enter the following:

json(variables('Jsonpayload'))

Are you ready???

Okay, so we have wired everything up. Save the workflow and either submit a new entry via PowerApps or use the Run Now function in Flow. If all things go to plan, you should see a bunch of green. In the screenshot below, I ran from Flow and added a title and rating (no metadata of course because we are not asking PowerApps for it.

After running the workflow I see green goodness

Now let’s check SharePoint for the evidence… Yeah baby!!!

Conclusion

Okay so that was quite an effort. If you have stuck with me through this, well done! Messing with JSON payloads and web services is bloody fiddly, but I have to say that understanding app principals and the SharePoint API is very useful knowledge.

You will be pleased to know that at this point the worst is over. In the next post we will return to PowerApps and set it up to send the term set item to flow, so it is not hard-coded like it is now. We will also discuss some weaknesses and limitations with this approach and look at ways we can make this whole thing a bit more resilient.

Until then, thanks for reading…

Paul Culmsee

Hi and welcome to part 2 of my series on showcasing the utility of Flow. Specifically, we are updating managed metadata fields – which is something that neither PowerApps or Flow can do natively as I type this. Having said that, if you are reading this in 2018 and beyond, it is likely this post is out of date the the gap has been addressed. Nevertheless, there are lots of good nuggets of info in this series that actually go beyond this particular use-case and will arm you with the knowledge to use Flow in all sorts of interesting ways.

At the end of our last exciting instalment, we had created an app principal, a flow and a powerapp. In this post we are going to focus on the first of the three – the app principal. Recall my analogy from part 1 where I described how the type of oAuth we are using is roughly analogous to getting into a nightclub. Basically our flow is like someone waiting in line to get into the club. Once they get past the bouncer at the door and pay the entrance fee, they are stamped so that they can enter and exit the club as they please, without having to wait in line and pay the entrance fee all over again. The stamp is temporary though, so if the patron returns the next night they will have to show their ID all over again and pay up.

The following diagram illustrates the idea…

In the last post, we registered an application principal. It looked like this:

This is what our virtual doorman is going to use to verify who we are. Our “dooman” is actually a Microsoft API sitting in Azure named https://accounts.accesscontrol.windows.net and not only will we need the Client ID and Client Secret from the app principal, but he also wants to know which Office365 tenant that we belong to. For that, we need the Office365 tenant ID, so let’s go get it.

Getting the Tenant ID (and other bits)…

As Microsoft states, “Your Office 365 tenant ID is a globally unique identifier (GUID) that is different than your tenant name or domain. On rare occasion, you might need this identifier, such as when configuring Windows group policy for OneDrive for Business”.

Guess what – this is one of those rare occasions.

To find the ID, either use one of the methods specified in this article, or go back to your site collection and append /_layouts/15/appprincipals.aspx. This URL will display your registered apps and also display your tenant ID (highlighted yellow below).

Now that we have a Client ID, Client Secret and Tenant ID, we can look at what a request to the doorman known as https://accounts.accesscontrol.windows.net actually looks like…

First up we need to add the tenant ID to the URL. E.g. https://accounts.accesscontrol.windows.net/[Tenant ID]/tokens/OAuth/2 where [TenantID] is replaced with your actual tenant ID we just discussed.

The second requirement is to send a payload of data to that URL via a HTTP POST. In the header of the post, we set a parameter called Content-Type to a value of application/x-www-form-urlencoded. This tells the web service to expect the rest of the data we send to be in a single string where name/value pairs are separated by the ampersand (&), and names are separated from values by the equals symbol (=). Trust me this will make sense in a moment…

Inside the payload, our doorman is expecting the following parameters:

• grant_type – this is where we specify the type of oAuth request we are making. As discussed in part 1, we are using the Client Credentials approach, so the value will be: “client_credentials”.
• client_id – this is actually a combination of the client id and tenant id. eg: “417ab7b8-c1bb-4475-8117-38ecec3cc63c@777e0ae8-66e6-4e0e-9ba4-0a8d9f02e915”
• client_secret – exactly as specified in the app principal
• resource – this specifies the actual data the access token is intended for. It is a combination of “00000003-0000-0ff1-ce00-000000000000” which is the ID for SharePoint within Office365, then it has the domain name of your tenant, followed by the tenant ID. Eg: “00000003-0000-0ff1-ce00-000000000000/culmsee.sharepoint.com@777e0ae8-66e6-4e0e-9ba4-0a8d9f02e915”

If we supply these parameters correctly, the service will return a data structure that contains various parameters. The only one we need is called access_token, which is highlighted below.

{

   "token_type":"Bearer",

   "expires_in":"3599",

   "not_before":"1507958657",

   "expires_on":"1507962557",

   "resource": “…”,

       "access_token":"…"

}

Our first few flow steps will create variables to store the parameters we need, and then we will try to get past our virtual doorman with a correctly formed HTTP request.

Back to Flow…

Let’s reopen the flow we created in part 1. It should have a trigger and one action – the create item action used to write the new coffee stop to SharePoint.

1. Add a new action and choose Variables – Initialize Variable. Call the Variable ClientID, make it a string and set its value to the client ID from the application principal. Also rename it to Set Client ID.

2. Add a second initialize variable action. Call this variable ClientSecret, make it a string and set its value to the Client Secret from the application principal. Rename it to Set Client Secret

Note: If the client secret has symbols in it like a +, you will see the following error:

Error validating credentials. AADSTS50012: Invalid client secret is provided. error_codes”:[70002,50012]

This error occurs because we need to encode the key to account for these characters. Use an expression and call the function encodeUriComponent. E.g.

encodeUriComponent(‘xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx+xxxxxxxxxxxxx’)

3. Add a third initialize variable action. Call this variable TenantID, make it a string and set its value to the tenant ID you obtained in the last section. Rename it to Set Tenant ID

As described above, we need to take the client ID and tenant ID and join them together to get our access token. So we will make a new variable that is a combination of the existing ones…

4. Add a fourth initialize variable. Rename it to Combine Tenant and Client ID. Call this variable Realm and make it a string. This time though, instead of typing in a value, click the expression tab to the right.

Now follow this section carefully as the UI is not obvious here. First type in concat() and place your cursor inside the two brackets. Then click on the “Dynamic content” tab and click the ClientID variable you created in a step 1. If you did it right, the concat function will now have a reference to that variable as shown in the 3rd image below.

Now type in ,‘@’, exactly as shown below (include the single quote)…

Finally, click on the Dynamic content tab and click the TenantID variable you created in a step 3. If you did it right, the concat function will now have a reference to that variable as shown below.

For reference, the expression should look like this:

• concat(variables(‘ClientID’),’@’,variables(‘TenantID’))

Click ok and the value in the step will look like the image below:

5. Add a fifth initialize variable. Rename it to Set Resource. Call this variable Resource and make it a string. using a similar approach to step 4, add the expression as follows:

• concat(‘00000003-0000-0ff1-ce00-000000000000/<tenant name>.sharepoint.com@’,variables(‘TenantID’))

Make sure you replace <tenant name> with the name of your tenant. I.e. mine is culmsee.sharepoint.com, resulting in:

• concat(‘00000003-0000-0ff1-ce00-000000000000/culmsee.sharepoint.com@’,variables(‘TenantID’))

At this point your workflow should look like this:

Let’s do a quick test and see if things are working as we expect. Go back to PowerApps and add a new café… eg:

In flow, check the history to confirm things ran successfully… lots of green ticks will be there if you got things right…

In particular, make sure the last two actions worked. Click on them and check the output to ensure your concat expressions worked as expected.

Getting the token…

Now it’s time to craft the HTTP action that will get our access token. As a reminder, the URL is actually: https://accounts.accesscontrol.windows.net/[Tenant ID]/tokens/OAuth/2. Our POST to this URL will supply grant_type, client_id, client_secret and resource. Let’s get this done!

1. Add a HTTP action and set the Method to POST. In the URL field, type https://accounts.accesscontrol.windows.net/

2. Click Add dynamic content and add the TenantID variable

3. Finish the URL by typing in /tokens/OAuth/2. You now have the URL ready to go.

4. In the header section, add a key called Content-Type with a value of application/x-www-form-urlencoded

5. In the body section, add the text grant_type=client_credentials&client_id=. From the dynamic content menu, add the Realm variable.

6. Add the text &client_secret=. From the dynamic content menu, add the ClientSecret variable.

7. Add the text &resource=. From the dynamic content menu, add the Resource variable.

8. Finally, rename the action to Call Token Service. Your action should look like the following:

9. Add a new initialize variable action. Call this variable AccessToken and make it a string. Rename the action to “Get Access Token”. Click the expression tab to the right and enter the following expression…

actionBody(<Name of your HTTP Action>’)[‘access_token’] – eg: actionBody(‘Call_Token_Service’)[‘access_token’]

This basically says to flow “Take the body text that came back from the HTTP step and pull out just the parameter called “access_token”

10. Finally, save the flow and submit another coffee place review, either via PowerApps, or by clicking the Run Now button in flow. Flow will ask for the same parameters that it expects from PowerApps.

In the debug of the flow, check the HTTP task and ensure it has a green tick.

Note: Setting all this up us tricky, so take your time, check everything and above all, DO NOT PROGRESS UNTIL YOU HAVE A GREEN TICK! If you do, examine the output of the Get Access Token. If everything has worked, you should see a string that looks like the second image below…

Conclusion:

If you have made it this far, congratulations! You have just done something quite tricky and usually the realm of developers. In terms of the breakdown of work we need to do, we have now progressed quite far…

In the next post, we are going to use the access token to call the SharePoint API that allows us to update Managed metadata.

In the meantime, thanks for reading and I hope these articles are starting to make this whole process a little less mysterious…

Paul Culmsee

Hi budding PowerApps and Flow users…

One (current) issue affecting both PowerApps and Flow is that neither support Managed Metadata Columns properly. I have a method to get around this issue, but it will take more than one post to explain. Lately I have refrained from writing epic multi-part posts because things change quickly. In fact this issue will likely go away by the end of 2017. <update> Native support for Managed metadata is now available. Be careful when you use this content</update>.

Due to this pace of change, this is very likely the last multi-part blog series I will do. So consider this a last hurrah from a bygone era of Cleverworkarounds

Now let’s get to work. To illustrate the managed metadata issue we currently have to contend with, consider the list below that tracks the most business critical task ever – where the best coffee is near the office…

The beans column is actually a managed metadata column, linked to the following term set which has a list of locations and coffee beans from that location. As you can see above, each café sells a certain type of coffee bean…

Now that we all understand the vital importance of the data contained in this list, it is obvious that we need an app for it so that our workforce can have locations of the best coffee at their fingertips. So let’s quickly examine the degree to which PowerApps is able to handle this list. I created a blank app and connected it to the above list. I then tested with a gallery, data table, display form and edit form. Here are the results below…

Gallery: Fail

As you can see in the image below, the gallery is bound to the Coffee Shop Ratings list, but in the dropdown highlighted below, the managed metadata “Beans” column is not listed.

Data table: Pass

The data table does work. You can see below that the Beans column is visible….

Display Form: Pass

Like the data table, a display form also will display the managed metadata value…

Edit Form: Fail

As you can see below, the edit form sees the Beans column, but will not render it as editable in PowerApps… bummer. Not a great solution if you want your users to add new coffee places to the list via the app.

Flow to the rescue?

If you have read some of my recent posts, I have found Flow to be very useful to work around various obstacles. For example, I use flow to handle photo uploads from PowerApps to SharePoint. So how does Flow fare with Managed Metadata? Unfortunately this is also a bust at present. The built-in “SharePoint – Create Item” action will not show the managed metadata columns. Notice the Beans column is missing from the image below…

Now all is not lost. Just because the built-in Flow actions do not work, does not mean we cannot achieve our goal. One can also talk to SharePoint via it’s API’s. Flow makes this possible because one of its built-in actions called the HTTP action. This is a super powerful action because you can use it to pretty much make any form of web request you want.

So let’s do an experiment and find out how easy it is to support managed metadata via Flow, by talking to SharePoint via API.

But there is one issue we have to contend with first. The SharePoint actions in flow take care of authentication for us, whereas the HTTP action is generic by definition. So how are we going to make an authenticated connection to SharePoint using the HTTP action?

The answer is we are going to use oAuth to request an access token. For the non-developers reading this who’s eyes start to glaze over when terms like oAuth start getting bandied about, let me use an analogy…

An oAuth primer using beer…

Let’s say that you are going to a nightclub or a bar, and you have to prove that a) you are over the legal age and b) you are wearing suitable attire for the venue. You present your drivers license or other form of ID. The doorman checks your ID, decides if you are good looking enough and permits you to enter. You then walk into the club, pay your entrance fee, and an ink stamp is used to mark your arm. This tells staff that are are legally allowed to be in the club and can enjoy a frothy beer or two. Think of that stamp as your access token. If you walk out of the club for a break, you can skip the line to get back in, because you have already been stamped. You just show them your hand…

So the basic pattern is to get an access token is:

Once you have access, no one has to check your ID, instead they just make sure the stamp on your arm is the same as the one they issued you earlier. Eg:

Now access tokens don’t last forever. If you turn up the next day, try and skip the line and present your stamp, the doorman will turn you away because today they used the blue stamp and last night they used the green stamp. So you will have no choice but to go to the other line, get your ID checked, and pay up before getting a new blue stamp issued…

So let’s now turn our attention to SharePoint and how this works in real life. What I just described above is roughly equivalent to something in oAuth called the Client credentials grant. In short, before we do anything related to managed metadata, we need to get into the club. So we will need to get some identification so we can get our hand stamped. So who issues the ID in the first place?

Enter the app principal

In our analogy above, there was an implicit trust going on. You showed your identification to the doorman, and they trusted that the ID you flashed before him was genuine. In the context of SharePoint, we need to have SharePoint itself issue your ID for it to be deemed trustworthy. After all, even bouncers have problems with fake IDs.

So our flow is going to need to use an app principal (a genuine ID) before it can be issued a “stamp” (or access token). Fortunately for us, the plumbing to do this in SharePoint has been in place since the oft-derided app/app-in model was brought in. I am not going to get too caught up in the details here, because all-round guru Wictor has detailed the process in a few posts. In particular, have a read of this one, and in particular the “Create the App Principal” page…

Here are the basic steps to create an app principal to use in flow:

1. Navigate to your site collection and append /_layouts/15/AppRegNew.aspx

2. Generate an App ID, App secret and enter some other identifying info

3. Copy the client id and secret to clipboard.

4. Append /_layouts/15/AppInv.aspx to your site collection and grant the permissions needed by this app principal.

In our example, we are updating a managed metadata field in a single list, so this app principal is going to be given the ability to write to SharePoint lists. The XML is below, and the schema for other scenarios is here:

<AppPermissionRequests AllowAppOnlyPolicy=”true”>

    <AppPermissionRequest Scope=”http://sharepoint/content/sitecollection/web/list” Right=”Write”>

    </AppPermissionRequest>

</AppPermissionRequests>

6. Choose the list that the app principal will be working with. In my case it is the coffee shop rating list with the managed metadata column.

Although I said I would not get into too much detail, here are some handy notes around this process:

• The permission XML is very fussy. It will not tolerate spaces between elements
• This app principal is registered/stored in azure active directory. You need privileged access in Azure AD to do this (site collection admin is not enough)
• You can also do all of this in PowerShell
• The app principal is valid for 1 year. So just like your drivers license expires periodically, the same happens here. If you want something longer, do this in PowerShell. (in our case native support for Managed Metadata in PowerApps/Flow is a few months off, so a year is fine
• The oAuth method we are using doesn’t actually use the App Domain nor the request URL. But other methods do, hence why we have to enter something anyway
• Microsoft are now steering people to the Graph API, which is a unified API that allows access to all of Office365 services. This will become the approach to take, but Wictor Wilen told me on Twitter that currently you cannot scope an access token down to a particular site collection.

Back to Flow…

Now that we have an app principal with permission to our site collection, it is time to start work on our Flow.

The basic operation for the flow is follows… First, the flow will be triggered by PowerApps, which will send Coffee shop name, rating and Bean. We will create a new entry in the list using the standard SharePoint flow action, before using the HTTP action to get an access token, and then update the newly created item with the coffee bean used by the shop.

Now the actual flow is going to look different to what I have outlined above. There are multiple actions involved for each of the steps, and some architectural and security considerations too. So we will adjust our flow as we go…

So let’s end this post by dealing with the trigger and step 1 above, as they are easiest. Part 2 will deal with the access token and part 3 will deal with the updating of the managed metadata column.

1. Create a new flow and choose the PowerApps trigger…

2. Add a SharePoint – Create Item action.

3. Specify the site collection where your list is stored and then the Coffee ratings list. Flow will read the list and display the fields it has found (excusing Beans)

4. Rename the flow to CoffeePlace and for the Title and Rating columns, set it to ask PowerApps for the data

5. Save the flow and give it an appropriate name…

Build a PowerApp…

Now let’s go to PowerApps and build a basic app to add a new coffee place.

1. In PowerApps create a new blank app using the phone layout

2. Insert 2 text boxes and 2 labels as shown below… note that I have named my controls to be more meaningful. I suggest you do the same…

3. Add a button to the bottom the app and label it “Submit”

4. Select the button, and from the actions menu, choose “Flows”

5. A panel will open to the right of your app screen, and you should see the flow we made earlier. Basically PowerApps will show any flows that you have access to, that use a PowerApps trigger

6. Choose your Flow, and PowerApps will add a submit action to the button that calls the flow. Any “Ask in PowerApps” parameters will be displayed in the function as shown below. Now hopefully you can see why I made you rename your Create Item action when building the flow! The image on the right is the flow action where we told it to ask PowerApps. Note the names in both images…

7. Fill in the parameters by choosing the text boxes we added in step 2. Now you will see why I recommend naming them…

8. Save your flow and then preview it… Enter a shop name, rating and click the button. Check your SharePoint list and you should see the new entry…

Conclusion…

Awesome! If you have gotten this far, we have all of our raw ingredients set up. We have an app principal, a PowerApp and a flow. In the next post, we will focus on getting our access token, so we can update the managed metadata field.

Thanks for reading…

Paul Culmsee

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:

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

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).

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.

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

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:

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!).

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!!!

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.

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.

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.

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…

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).

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

www.hereticsguidebooks.com

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.

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.

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.

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.

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

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

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…

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.

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.

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)

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.

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:

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.

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.

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.

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.

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

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.

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

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

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

Your dictionary should look like this:

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

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:

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!

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’>

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>”}”

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:

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

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.

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.

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:

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:

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…

Thanks for reading

Paul Culmsee

www.hereticsguidebooks.com

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.

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.

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.

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.

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.

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…

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.

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.

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…

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>”}

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.

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

www.hereticsguidebooks.com

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’

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

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?

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.

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

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

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:

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?

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

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.

Step 7:

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

Step 8:

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

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.

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.

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.

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

www.hereticsguidebooks.com