Hiya

Pretty much every time I run a project kick-off meeting involving tools like SharePoint or PowerApps, I always ask the group the following question:

“If you had <insert goal here>, how would things be different to now”?

(Replace <insert goal here> with any common IT platitude like “improved collaboration”, “knowledge management”, “big data analytics” or “digital transformation”. )

Now inevitably, someone will say something like “I would have information at my fingertips”. While that may sound like a cliche, these days, it is pretty easy to meet this goal. So easy in fact, that I can do it in around 10 minutes using a SharePoint Document Library, a quick PowerApp and a Flow of only 5 steps. I then get to be a smartass, hand them my phone and say “there you go – at your fingertips… That’ll be $250 thanks”.

Now if you want to score $250, I have bad news for you. No client has ever actually paid me when I used that line. But nonetheless, if you have been looking for a demo to show just how powerful these tools have become, then one is pretty good… If you check the image below, you can see I have a PowerApp that is browsing a SharePoint document library and irrespective of whether the document is Word, Excel or PowerPoint, is loading it as a PDF into PowerApps for preview. Better still, this solution is very much in the citizen developer realm…

A common use-case for this sort of solution is a field worker who needs to access schematic data like drawings or equipment documentation. I have also deployed something similar for quality management and safety apps. In all cases, users had a need to be able to retrieve documents quickly and easily…

How to build this app

This app consists of a simple PowerApp, a SharePoint doclib and a 5 step Flow.

Step 1: Create a document library in SharePoint – in my case I called my library “FingertipsBaby”…

Step 2: Start PowerApps studio and make a blank tablet app.

Step 3: Connect the document library as a data source using the SharePoint connector. Note, although document libraries are not listed by default, you can definitely connect them. Just scroll to the end of the lists and in the last box, type in the name of your document library…

Step 4: On the left side of the screen, add a blank vertical gallery and linked it to the data source.

Step 5: Add a label to the gallery and set its Text property to ThisItem.’{FilenameWithExtension}’. If you check the image below, you can see my three documents listed from my library. Not pretty I know, but this is a 10 minute demo remember?

Step 6: Go to the Action menu and click the Flow icon. in the Data panel that opens on the right, click Create a new flow. A new tab will open in your browser and log you into flow. A new flow will be created using a PowerApps trigger as shown above. Take the opportunity to give your flow a name, such as “PDF Viewer”

Explanation interlude:

Now at this point I feel some explanation is needed. We are about to use a few recent enhancements to Flow and SharePoint. Kudos needs to go to Brian Edwards who made me aware of a powerful new SharePoint API that among other things, generates thumbnails and PDF’s of documents. Brian already has a blog that explains how to do thumbnails – so consider this article a companion one that does PDF’s.

The key to the whole thing is an API called RenderListDataAsStream. This is a very powerful API that has a truckload of goodness, but for now I will keep it simple. I will pass a SharePoint List Item ID of a document to the API and it will give me back a PDF. The API call looks like this:

_api/web/lists/GetbyTitle(‘FingerTipsBaby’)/RenderListDataAsStream?FilterField1=ID&FilterValue1=<List Item ID>

In the body of the request, I need to specify the data I’d like returned. The parameter is called RenderOptions and looks like this:

{ 

    “parameters”: {

       “RenderOptions” : 4103

    }

)

What is the deal with the number? Well, the documentation includes a table of different interesting things you can return, which you can do by adding the Values together.

So we are asking this API not just to bring back the data associated with a list item, but also some additional useful stuff. The last entry is particularly interesting as it mentions a mysterious beast known as the Media TA service which I assume means “translation”. Basically what happens is if we total the numbers listed in the above table (4103), we will end up all the data we need to do PDF conversion.

Now at this stage, I don’t want to do an exhaustive examination of the JSON data returned by this API call, but I will call out a couple more things before we get to flow…

First up, you will find this entry…

“.pdfConversionUrl”: “{.mediaBaseUrl}/transform/pdf?provider=spo&inputFormat={.fileType}&cs={.callerStack}&docid={.spItemUrl}&{.driveAccessToken}

This parameter refers to a URL that will convert the list item to a PDF. All you need to do is access this URL. How easy is that eh? Now all of the stuff in curly braces are tokens but they are also returned as part of the API call, so they can be grabbed from elsewhere in the output. For example, {.mediabaseURL} is a few lines up and in my case is:

“.mediaBaseUrl”: “https://australiasoutheast1-mediap.svc.ms”

So if we find each token in the .pdfConversionUrl and replace them, we basically have converted our document to PDF without needing to store a PDF. The document can stay in its native format!

Back to Work…

Okay so let’s deal with this flow.

Step 7: Add an SharePoint action called Send an HTTP Request to SharePoint to the flow. Set the Site Address to the site that contains your document library and set the Method to POST. Set the URI to _api/web/lists/GetbyTitle(<docLib>)/RenderListDataAsStream?FilterField1=ID&FilterValue1=, where <Doclib> is the name you specified for the document library (for example, mine is _api/web/lists/GetbyTitle(‘FingerTipsBaby’)/RenderListDataAsStream?FilterField1=ID&FilterValue1=). On the end of the URI, click Dynamic Content and choose Ask in PowerApps as shown below:

Step 8: In the Body section, paste the following configuration (watch the quotes when pasting from this article):

{ 

    “parameters”: {

       “RenderOptions” : 4103

    }

}

\

Step 9: Click the Save and then Test icon in the top right. Choose the option I’ll perform the trigger action and click the Save and Test button. Click the continue button and on the next screen, type in the ID number of one of the documents in your library and click the Run Flow button. Your flow will start and you can click Done. Assuming it worked, you will see a green tick of happiness.

Step 10: Click on the Send an HTTP Request to SharePoint action to expand it. We need to grab the output from the API call for the next action. Find the OUTPUTS section and copy the entire contents to the clipboard….

Step 11: Go back to edit mode and add a Data Operations action called Parse JSON to your flow. This action will allow us to make use of the output of the API call in the subsequent flow step.

Click the Use sample payload to generate schema link and paste your clipboard contents into the window and click the Done button. In the Content field, go to Dynamic content popout and choose Body from the Send an HTTP Request to SharePoint action.

Step 12: Add an Initialize Variable action to your flow. Name the variable PDFURL (or something similar) and set it to a string. Now we come to the most complex bit of the flow where we have to substitute the token we examined in the interlude earlier. Be careful here… this is the most likely place to make an error…

In the Value textbox, click the dynamic content flyout and find .mediaBaseUrl from the Parse JSON action…

Next, add the following text to the Value textbox, taking care not to delete what you just added in the previous step. <edit>Please note the image has a quote that is not meant to be there, so ignore it!</edit>

/transform/pdf?provider=spo&inputFormat=

Now we come to a slightly tricky bit. The next bit of content we need is the file type of the document we are dealing with. Now this is formatted as a single value array, so despite there being only a single value, we need to create an expression to handle it. Click the Expression tab and type in the following:

first(body(‘Parse_JSON’)?[‘ListData’]?[‘Row’])?[‘File_x0020_Type’]

For the uninitiated, this is known as Workflow Definition Language and is well worth learning…

Next, add the following text to the Value textbox, taking care not to delete what you just added in the previous step.

&cs=

Also be super careful here because at the time of writing, the cursor in this textbox can randomly move and wipe out your edits…

Now in the Value textbox, click the dynamic content flyout and find .callerStack from the Parse JSON action…

Next, add the following text to the Value textbox, taking care not to delete what you just added in the previous step.

&docid=

Now we come to another array that needs to be handled. This is the URL of the document we are dealing with. Click the Expression tab and type in the following:

first(body(‘Parse_JSON’)?[‘ListData’]?[‘Row’])?[‘.spItemUrl’]

Okay we are almost done… Add an ampersand ( & ) to the Value textbox, and then click the dynamic content flyout and find .driveAccessToken from the Parse JSON action…

Step 13: Add a the PowerApps – Respond to PowerApps action to the flow. Click the Add an output icon and choose Text from the list of output types. Name the output PDFURL and set the value to the variable you created in step 12 (PDFURL).

Ok we completed the flow. Save it and give it a quick review. It should look something like this…

Step 14: Back in your PowerApp, now, select the label gallery you created in step 5. From the Action menu, choose Flow and you should see your newly created flow listed. Click on it to add it to your PowerApp. Once added, the label will partially fill in the OnSelect property to run your flow.

Step 15: Complete the flow invocation by adding a reference to the ID of the document from the gallery by using the following function:

Set(PDF,PDFViewer.Run(ThisItem.ID))

This function will set a variable called PDF to the output from our newly minted flow. When you click on a file in the gallery, the flow will get called. In fact you can try this out before we move on. Press the play icon to test your app and click one of the files. Then go back into edit mode and click the Variables icon from the View menu. You should see a global variable called PDF that has a data type of record.  Clicking the variable will bring up further details and if you click on the record icon, you will see the output returned from flow.

If you get this far, that’s great because you are almost home….

Step 16: From the Insert menu, Add a PDF Viewer from the Controls menu. Place it to the right of the gallery and set the Document property to PDF.pdfurl. (if you look closely at the above image you can see that PowerApps has decided that the output of the API is a record with a column name of pdfurl).

Now click on some of the documents… if you did it correctly, you will be seeing PDF’s!  Wohoo!

Conclusion

Not bad eh? A 5 step flow has enabled a very powerful use-case and once again showcases how well PowerApps and Flow work together. I should note that there are three recent innovations that have enabled this scenario, namely the recent Send an HTTP request to SharePoint action, the RenderListDataAsStream API and the Respond to PowerApps action.

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

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

Hiya

First up, if you are not aware, the Digital Workplace Conference is about to land in Sydney. I strongly recommend you attend this conference. In my opinion there has never been a more fascinating time to be involved in the world of Office365/SharePoint and Azure and some top people are going to be speaking.

I have spent the better part of the last two years working with cloud first organisations, and frankly I’m amazed at what is available in the base edition of Office365. I suspect many organisations are not particularly aware of the capabilities available to be exploited – which brings me onto my topic.

In case you are not aware, I am a bit of a fan of PowerApps. I see it having huge potential for citizen developers and more technically-minded folk alike. Most recently I have been teaching it to my daughter, who at 18 years of age, is busy studying her first year of psychology at university. For someone with no formal programming experience, she has come a long way, as this video demonstrates. In this video, we cover a real-world scenario and make use of a lot of tricks to get a nice, solid solution.

So why am I telling you this? Well I am presenting at the Digital Workplace Conference in Sydney this month, and my session is called “A Certificate in Advanced PowerApps Awesomeness” and I will be covering some of the tricks behind what we did in this video, as well as a couple of other apps that Ashlee wrote. I will be sharing various pearls of wisdom from Ashlee, who presents a fun perspective on Microsoft products. Ever seen those “kids react to?” videos on YouTube?  Well, watching Ashlee work on PowerApps is kind of like that

Therefore I will share some Ashlee-penned pearls of 18 year old wisdom such as…

“Do not put spaces in the name of your SharePoint libraries, because PowerApps can’t handle it and will have a huge hissy fit”

So I hope to see you in Sydney, where I will delve into this beast known as PowerApps and show you what an 18 year old with no formal experience can do with the platform.

I’ll cover PowerApps deeper than the typical demos, but at the same time business-oriented folks shouldn’t be too off-put by it.

Hope to see you there!

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

Hi and welcome to part 8 of my series of articles around SharePoint 2013 workflows. Hopefully as you progress through this series, as well as getting a heap of new conceptual baggage, you are getting a better sense of that SharePoint 2013 workflows are capable of and whether they have enough to be a seriously considered solution and whether they can empower citizen developers.

If you have been following proceedings thus far, we just successfully connected to the Process Owners list via REST web services and we manipulated dictionary variables to get to the data returned by the web service. We just started using the looping functionality of SharePoint 2013 workflow and successfully created a loop to iterate through the process owners list.

Now it’s time to finish the job. Here is the stage that we successfully tested at the end of the last post…

Adding conditional logic…

Our next task is to add some conditional logic into the workflow where if a match is found between term GUID’s, stop looping through the rest of the process owners. This will make the workflow more efficient because if the process owners list was big and the matching term GUID was one of the first few entries, we can save a lot of unnecessary loops.

Step 1:

Click in between the second and third workflow actions in the loop. From the ribbon, click the Condition icon and from the list of conditions, choose If any value equals value.

Now back in part 6, in the “Getting the GUID…” section, we added a series of steps to extract the Term GUID from the current item. The variable was called TermGUID. Now we are going to compare the TermGUID with each of the GUID’s returned by each iteration of the loop.

Step 2:

Click on the leftmost value hyperlink of the If statement and click the fx button. In the Define Workflow lookup dialog, choose Workflow Variables and Parameters as the Data source and choose the TermGUID variable in the Field from Source dropdown. Click OK

Step 3:

Click on the rightmost value hyperlink of the If statement and click the fx button. In the Define Workflow lookup dialog, choose Workflow Variables and Parameters as the Data source and choose the ProcessOwnerTermGUID variable in the Field from Source dropdown. Click OK

Now we have configured the test for when the Organsiation matches between a document and the process owner list. The next step is to get the value of the Assigned To column, since that has the username of the process owner for a given organisation. But If you review the web service call that was made to to the Process Owners list (http://megacorp/iso9001/_vti_bin/client.svc/web/lists/getbytitle(‘Process%20Owners’)/Items?$select=AssignedToId,Organisation), you might notice that I used the column name AssignedToId rather than the actual column name reported in SharePoint called Assigned To. So what gives?

Turns out that one of the current limitations of using the oData/REST approach is that columns that store People/Group data do not return the persons name. Instead, they return a numeric ID via a different column name. For example: the Created By and Modified By columns are represented in rest by AuthorId and EditorId respectively.

The implication? We will need to call yet another web service, passing it the value of AssignedToId to get the actual username so we can assign them a task.

“Oh come on,” I hear you say, “this is messy enough already and we still have to mess with web services?”

I feel your pain, but at least now you have more of an  idea of what it takes to be a citizen developer when it comes to SharePoint Designer 2013 workflows! Let’s get on with it…

Finding the user…

Our first step is to grab the value of AssignedToId from the InnerProceessOwnerList dictionary variable. This is similar to what we did to create the ProcessOwnerTermGUID variable in part 7 – we use a Get Item from a Dictionary action.

Step 1:

Add a Get an Item from a Dictionary action, click the item by name or path hyperlink and click the ellipses to bring up the string builder screen. Type in a left bracket “(“ and then click the Add or Change lookup button. In the Lookup for String dialog, choose Workflow Variables and Parameters as the data source and find the variable called loop  and click OK. Finally, type in the following string “)/AssignedToId” and click OK.

Step 2:

Back at the workflow action, click on the dictionary hyperlink and choose the InnerProcessOwnerList variable from the list.

Step 3:

Click the item hyperlink and choose to create a new variable to bring up the edit variable screen. Name the variable AssignedToID and set its type to Integer.

Now that we have our user (well an integer representing the user in the form of the variable AssignedToID), there is not much point in looping around for more process owners, so lets add a workflow step to make the loop end straight away.

Step 4:

Add a Set Workflow Variable action and click on the workflow variable hyperlink. From the list of variables, choose loop. Now click the value hyperlink and click the fx button to bring up the Lookup for Integer dialog. In the Data source dropdown, choose Workflow Variables and Parameters and in the Field from source dropdown, choose the variable count.

To understand what step 4 does, let’s look at the entire loop. Note that the loop will run repeatedly while the value of the loop variable is less than the value of the count variable. Step 4 now means that this is not the case and the loop will end.

Next we have to grab the user that AssignedToId variable refers to via calling another web service as I previously mentioned. This is a little tricky, so it is best we do it in a new workflow stage. Thus, we will tidy up a few loose ends with this stage.

Step 5:

Add a new workflow stage and name it “Obtain UserID”.

Step 6:

In the Transition to stage section of the Find Matching Process Owner stage, delete the Go to End of Workflow action and replace it with an If any Value equals Value condition. In the first value hyperlink, set it to the variable AssignedToID. Click the equals hyperlink and change it to is greater than. Finally, click the last value hyperlink and set it to zero “0”. The logic behind this workflow action is that the default value for integer variables are 0. If a process owner has been found for a document, the value of AssignedToID will be greater than zero. Therefore the workflow can use this fact when it decides whether to proceed to the new stage created in step 5.

Step 7:

Add a Go to a stage action underneath the If condition and set it to go to the Obtain Userid stage created in step 5.

Step 8:

Add a Go to a stage action underneath the Else condition and set it to End of Workflow.

A new web service…

Right, let’s now turn our attention to the Obtain Userid stage created in step 5 above. By now you should be a REST/JSON guru after being able to successfully connect to and parse the data from the Process Owners list in part 6 and part 7. By comparison, the web service method we are about to call is quite simple. It is called GetUserByID and the URL looks like this:

http://megacorp/iso9001/_api/Web/GetUserById().

The one thing we have to do is pass it an integer that refers to the user – value of the AssignedToID variable.

To demonstrate how this works, I will pick an ID at random and we can have a look at the data returned by this web service call. Using the Fiddler composer function to return the data in JSON format, we get the following output.

http://megacorp/iso9001/_api/Web/GetUserById(7)

A quick scan of the data above and it appears that the data we are interested in is LoginName. Based on what we learnt from accessing JSON data to get hold of the term GUID of the Organisation column, we will have to use the Get an Item from a Dictionary action, to get at the LoginName entry. The item reference should be d/LoginName, so let’s test it.

Step 1:

Add a Call HTTP Web Service action and click the this hyperlink. Click the ellipses button to display the string builder dialog. Use the Add or Change Lookup button and the text editor to build the following string:

• 1) [Workflow context: Current Site URL]
• 2) “_api/Web/GetUserById(“
• 3) [%Workflow Variables and Parameters: AssignedToID%]
• 4) “)”

Step 2:

Right click on the Call HTTP Web Service action created in step 1 and choose properties. In the Call HTTP Web Service Properties dialog, set the RequestHeaders dropdown to the RequestHeader dictionary variable. (If you stretch your mind back to part 6, we created the RequestHeader variable to ensure our data comes back in JSON format. We can re-use that here to do the same thing).

Step 3:

On the Call HTTP Web Service action,  click the response hyperlink and create a new variable and call it UserDetail. Click the ResponseStatusCode hyperlink and choose the existing variable called responseCode. (It first made an appearance in part 6 and we can re-use it here).

Step 4:

Add an If any value equals value condition and configure it to test of the variable responseCode equals OK

Step 5:

Add a Get an Item from a Dictionary action, click the item by name or path hyperlink and type in “d/LoginName”. Click the dictionary hyperlink and choose the UserDetail variable created in step 3. Click the item hyperlink and create a new string variable called AssignedToName.

At this point it makes sense to log the value of AsssignedToName to workflow history to make sure the have set everything up correctly.

Step 6:

Add a Log to history action and set the message to: “The User to assign a task to is: [%Variable: AssignedToName%]”

Step 7:

Add a Go to a stage action and set it to end the workflow. The complete configuration for the stage should look like the image below.

Now it’s time to test the workflow. In my case, I went back to the documents library and triggered the workflow on the document called Burger Additives Standard which is assigned to the organisation of Megacorp Burgers. Here is the result…

Wohoo! we have our user! If we check the process owners list for Megacorp Burgers on the left image above and compare it to the workflow history next to it, we can indeed confirm that the lookup reported is right. (In my test environment, the userid for Teresa Culmsee is teamsevensigma\terrie).

Assigning a task…

Now I think I speak for all of us when I say HOLY CRAP WE ARE ALMOST THERE!!!. I mean, it has taken 8 posts, but we have one more workflow action to add. Drumroll….the action that we started this series with way back in part 2.

Step 1:

Add an Assign a task action and click on the this user hyperlink. In the Select Task Participant Dialog, click the ellipses next to the Participant textbox. This will bring up the Select User dialog. In the select from existing users or groups list, select Workflow Lookup for a User and click Add. In the Lookup for Person or Group dialog, choose Workflow Variables and Parameters from the Data source dropdown and choose the AssignedToName variable and click OK.

Now you will be back at the Select Task Participant Dialog. Enter a task title of “Test approval task” and click ok. (For now we are not going to wire up the rest of the task, this is enough to prove that it works. So click Ok to go back to the workflow designer and review the action.)

Looks good, so let’s now publish the workflow and let’s test it by taking it from the top.

Now as a side note, when writing this post, my colleague Chris Tomich insisted that he had to be process owner for Megacorp’s Iron Man Suits division, and you will see that below. Checking the documents library and using metadata navigation, we see all the documents that are owned by the Iron Man Suits subsidiary of Megacorp. It stands to reason that we would have suit schematics and the backup and recovery process for Javris eh?

Choosing the Jarvis backup and recovery procedure document, we run our workflow. Checking the status of the workflow, what do we see? Yeah baby! Chris has been assigned a task!

Conclusion…

So at this point, I can’t help but wonder how many readers are actually happy that it took 8 posts to finally assign a task to a user. But nevertheless, the journey has been a good example of the sort of issues that you encounter when using SharePoint 2013 workflows.

Now in part 7, I  alluded to the fact that this method had a flaw. Our Megacorp example has been a simple one with just a few entries in the process owners list. But what if there were say, 5000 process owners and the one we want is number 4864? The current workflow would have to iterate through the first 4863 entries before it gets to this one. This isn’t overly efficient to do and might introduce some performance penalty (which plays into the hands of developers who don’t like the idea of scary citizen developers meddling with forces they don’t understand). So it begs the question whether we can come up with an alternate way to do this?

In part 9 and onward from there, we will answer that question.

Thanks for reading

Paul Culmsee

www.hereticsguidebooks.com

Hi and welcome to part 7 of my series of articles around SharePoint 2013 workflows. We have been examining the trials and tribulations (mainly trials so far) of implementing a relatively simple document approval workflow for a mythical organisation called Megacorp Inc. In the last post, I started illustrating the first of two approaches to filtering a list based on a managed metadata column using the Call HTTP Web Service capability. If you have been following along with me, we just successfully called the SharePoint lists web service via REST. The diagram below shows where we are at… Now it is time to work with the data that is returned.

Very soon we are going to use the loop function of workflows to go through each process owner and check the GUID of the Organisation field. This is probably best done as a new workflow stage that only runs if the response code from the webservice is 200 (OK). So let’s make some minor changes to the workflow…

Step 1:

Add a new workflow stage and name it “Find Matching Process Owner”

Step 2.

In the Transition to stage section of the new stage, add a Go to a stage action and set the stage as “End of Workflow”

Step 3:

In the Transition to stage section of “Get Process Owners” Stage, delete the “Go to End of Workflow” action. In its place, add an “If any value equals value” condition

Step 4:

Click the first value hyperlink of the newly added condition from step 3 and click the fx button. In the Define Workflow lookup dialog, choose Workflow Variables and Parameters as the Data source and choose the responseCode variable in the Field from Source dropdown. Click OK

Step 5:

Click the second value link of the newly added condition from step 3. Type in the value “OK” and press enter.

Step 6:

Under the If Variable: responseCode equals OK condition, add a Go to a stage action. Set the stage as Find Matching Process Owner (the stage created in step 1).

Step 7.

Under the Else condition, add a Go to a stage action. Set the stage as End of Workflow

Ok, so now we have a new workflow stage to work with called Find Matching Process Owner. This is where we are going to manipulate dictionary variables and in particular, the ProcessOwnersList variable that contains the data returned by the call we made to the REST webservice in part 6. The first thing we will do is count how many items are in the ProcessOwnersList dictionary, otherwise we won’t know when to stop the loop. This is where the Count Items in a Dictionary workflow action comes in. Let’s try it on the ProcessOwnerList variable now.

Step 8:

In the Find Matching Process Owners stage, add a Count Items in a Dictionary action. Click the dictionary hyperlink and choose Variable: ProcessOwnersList from the list of dictionary variables.

Now we should check to see if the value of count is what  we are expecting. For your reference, here are the 9 entries currently in the Process Owners list…

Step 9:

Add a Log to Workflow History action. Click the message hyperlink and click the ellipses button. In the string builder dialog, type in “Process Owners count: “

Step 10:

Click the Add or Change Lookup button. In the Lookup for string dialog, choose Workflow Variables and Parameters from the data source dropdown and choose the variable count. Click OK. You should see the following text in the string builder dialog. Click OK. Review the workflow stage and confirm it looks like the second image below.

Publish the workflow and run it. Take a look at the workflow history and see what we get. Uh-oh… what now? It says we only have 1 item in the dictionary, but we have 9 process owners. What the…?

A JSON (and fiddler) interlude…

In part 6, I stressed the point that dictionary variables can contain any type of variable available in the SharePoint 2013 Workflow platform, including other dictionary variables! This now becomes important because of the way JSON works. To explain better, it is time for me to formally introduce you Fiddler.

Fiddler is a multipurpose utility that sits between the web browser and web sites. It logs all HTTP(s) traffic and is used to view and debug web traffic. Any web developer worth their salt has Fiddler as part of the troubleshooting toolkit because of its ability to manipulate the data as it travels between browser and server. Bets of all, it can be used to compose your own HTTP requests.

So first up, install Fiddler and then start it. In your browser, head over to a web site like Google. Go back to Fiddler and you should see entries logged in the left hand panel. Click on an entry and click the inspectors tab. From here you can see all the gory detail of the conversation between your web browser and the web site.  Neat eh?

If you look in the tabs in the Fiddler window, you will see one labelled Composer. Let’s use it right now… Paste the webservice URL that we used in part 6 into the composer textbox as shown below. Also, paste 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.

Click execute and Fiddler will send the request to the server. It will also switch to inspector mode for you to see the request and the response as it happened. If you look closely at the response (by clicking the JSON tab), you will see a structure that looks a bit like that I have pasted below. This is the same data that is now being stored in the ProcessOwnerList dictionary variable.

So why did the workflow report that there was only one item in the dictionary then? The short answer is, because there is only one item in the dictionary! A dictionary called “d”. To understand better, take another look at the JSON structure below. What is the first entry in the JSON data? a section called “d”. If you were to collapse d, all other data would be inside it. Therefore, as far as the dictionary variable is concerned, there truly is only one entry.

- JSON
  + d

Note: In case you are wondering what the deal is with the whole “d” thing, given that the it appears to be somewhat redundant. The answer that Microsoft oData standards stipulate that all data returned is wrapped by an outer “d”. Why you may ask? Well you really don’t want to know, but if you must insist, it is for security reasons related to JavaScript (see, I told you that you didn’t want to know!).

So if there is only one entry in the dictionary, than how can we get to the data we need buried deeper? The answer is that this is one of those “dictionary in a dictionary” situations that I described in part 6. The dictionary variable ProcessOwnerList has a single item. That item happens to be another dictionary with multiple items in it. So our first task is to get to the inner dictionary that contains the data we need!

Getting to the real count…

Now we will make use of the Get Item from a Dictionary action to get stuff under the d branch. Looking at the fiddler JSON output above, we need to get to the children of the results branch.

Step 1:

In the Find Matching Process Owner stage, add a Get Item from a Dictionary action as the first action in the stage.

Step 2:

Click the item by name or path hyperlink and type in “d/results”. Note: This is case sensitive, so has to match the JSON data shown in the fiddler screenshots. Click the dictionary hyperlink and choose the ProcessOwnersList dictionary variable as the source data.

Step 3:

Click the item hyperlink and choose the create a new variable (the bottom option in the dropdown). Call the variable InnerProcessOwnerList and make sure it is set to type dictionary.

Step 4:

Now we need to modify the count items action to count the new variable InnerProcessOwnerList. In the Count Items action below the action we just created, click the Variable: ProcessOwnersList and change it to InnerProcessOwnerList.

Right! Let’s retest this workflow by republishing it and running it. Now that’s more like it!!!

Building the loop…

Now we get to a powerful new feature of workflows in SharePoint 2013 – the ability to perform loops. What we need to do here is loop through the 9 process owners, and for each, compare the GUID of the Organisation column to the GUID on the document from which the workflow was triggered. If the term matches, we exit the loop and assign a task to the person in the Assigned To column.

Let’s go through the process step by step.

Step 1:

Make sure that your cursor is below the last action in the Find Matching Process Owner stage. Add a Set Workflow Variable action. Click the workflow variable hyperlink and choose to create a new variable. Call the variable loop and make it an integer. Click the value hyperlink and set it to zero “0”.

We are going to use this variable in the looping section  of the workflow to get to each process owner. It’s purpose will become clear soon…

Step 2:

In the ribbon, click the Loop button and choose Loop with Condition. This will add a loop step into the stage. The name “1” is not exactly meaningful so lets change it. Click the title bar of the loop and change the name to For each process owner…

Now we will set the condition for how long this loop will run for. Note that we have a variable called count that stores the number of entries in the process owners list and in step 1, we created a variable called loop. We will set the workflow to loop around while the value of loop is less than the value of count.

Step 3:

Click the leftmost value hyperlink. In the LoopCondition Properties dialog, click the fx button for the top value. In the Define Workflow Lookup dialog, choose Workflow Variables and Parameters as the data source and find the variable called loop. Click the equals dropdown and choose Is less than. Finally, click the fx button for the bottom value. In the Define Workflow Lookup dialog, choose Workflow Variables and Parameters as the data source and find the variable called count.

Step 4:

Inside the newly created loop, add a Get Item from a Dictionary action. Click the item by name or path hyperlink and click the ellipses button to display the string builder. Type in a left bracket “(“ and then click the Add or Change lookup button. In the Lookup for String dialog, choose Workflow Variables and Parameters as the data source and find the variable called loop  and click OK. Finally, type in the following string “)/Organisation/TermGuid”.

If you look closely at this string, it is referring to the TermGuid in the JSON data. The value of loop (currently 0) will be used to create the string. Hence “(0)/Organisation/TermGuid” will be used to grab the first Organsiation GUID, (1)/Organisation/TermGuid for the second and so on…

Step 5:

Click the dictionary hyperlink and choose InnerProcessOwnerList.

Step 6:

Click the item hyperlink and choose to create a new variable. Call the variable ProcessOwnerTermGUID and set it to a string.

By this stage, we should have the value of the term GUID for the process owner. Let’s log the value to workflow history so we can confirm things are working…

Step 7:

Add a log to workflow history action and click the message hyperlink. Click the fx button and in the Lookup for String dialog, choose Workflow Variables and Parameters as the data source and find the variable called ProcessOwnerTermGUID.

Step 8:

Now we need to increment the value of the loop variable so it can select the next process owner from the InnerProcessOwnerList variable. Add a Do calculation workflow action, click the first value hyperlink and click the fx button. In the Lookup for Number dialog, choose Workflow Variables and Parameters as the data source and find the variable called loop. Click the second value hyperlink and type in the value “1”.

Note that this action creates a new workflow variable (called calc1 by default) that stores the value of loop + 1. We will use this variable in the next workflow step.

Step 9:

Add a Set Workflow Variable action. Click the workflow variable hyperlink and choose loop. Click the value hyperlink and click the fx button. In the Lookup for Number dialog, choose Workflow Variables and Parameters as the data source and find the variable called calc1.

Right, that should be enough to test. The each iteration of the loop will log the Organisation term GUID for each process owner. It then increments the value of loop and does it again. Once the value of loop is equal to the value of the variable count, the loop should finish. So publish the workflow and let’s see what happens.. Brilliant! Below the count of process owners (line 3), we have looped though the process owners list and extracted the GUID for the organisation term!

Conclusion…

Well, it has taken us a while to get here, but we now have all of the information we need to be able to assign a task to the process owner. if you look in the log above, the first entry was the GUID for the organisation assigned to the document this workflow was run against. Scanning the list of GUID’s from the process owners list, we can see that the matching GUID was on line 5. So in the next post, we will modify our loop to stop when it has a match, and we will examine what we need to do to assign a task to the appropriate process owner.

Until then thanks for reading…

Paul Culmsee

www.hereticsguidebooks.com