Home

Category : Workflow

Workflow – Understanding returned JSON format

Sometimes you need to see the data that is returned by a REST call to be able to work out how to use it. To view the data returned, I copy the HTTP Call Response into a Multi-Line field in a SharePoint list, query the list item and then cut and paste the string into a JSON Viewer (Like http://codebeautify.org/jsonviewer). I do not display the results of the call in the log as the Response content is often over the 255 character SharePoint print limit, which would result in a hung workflow.

The following steps assume that your REST Call is contained in the workflow variable ‘String01’

Setup your RequestHeaders dictionary

Create a dictionary variable as follows

… then click on the ellipsis (…) to define the names ‘Accept’ and ‘Content-Type’ in the dictionary. Set the value for both of these names to

‘application/json;odata=verbose‘

202-3

Make the HTTP Get Web Service Call

Specify the HTTP Get Web Service call using your REST statement (In my example, this is defined in ‘String01’). Be sure to use the ‘Properties…’ option to specify the correct ‘RequestHeaders’ variable otherwise the data may not be returned in the correct format

204-1

Save the response into a multi-line text field

Copy the response into a SharePoint Multi-Line field. In this example, I update the ‘Results’ field in ‘Current Item’ with the response returned by the call

204-2

Copy the data returned

Open the list item in SharePoint and copy the string from the field ‘Results’ to the clipboard

204-3

The example I used copied the following data to the clipboard

{“d”:{“results”:[{“__metadata”:{“id”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Ferrari 2.jpg’)”,”uri”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Ferrari%202.jpg’)”,”type”:”SP.Attachment”},”FileName”:”Ferrari 2.jpg”,”ServerRelativeUrl”:”\/sites\/dev\/Lists\/Utility Jobs\/Attachments\/154\/Ferrari 2.jpg”},{“__metadata”:{“id”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Ferrari.jpg’)”,”uri”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Ferrari.jpg’)”,”type”:”SP.Attachment”},”FileName”:”Ferrari.jpg”,”ServerRelativeUrl”:”\/sites\/dev\/Lists\/Utility Jobs\/Attachments\/154\/Ferrari.jpg”},{“__metadata”:{“id”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Maserati.jpg’)”,”uri”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Maserati.jpg’)”,”type”:”SP.Attachment”},”FileName”:”Maserati.jpg”,”ServerRelativeUrl”:”\/sites\/dev\/Lists\/Utility Jobs\/Attachments\/154\/Maserati.jpg”}]}}

View the data in a JSON viewer

Open a JSON viewer in a browser (I’ll use http://codebeautify.org/jsonviewer) and paste the clipboard into the viewer. In the example shown below, I pasted the data into the ‘JSON Input’ field. Once that is done, click on the format button in the viewer (‘Beautify’) and the system will display the data in an easier to read format as shown below on the right

204-4

If your ‘HeaderRequest’ dictionary had the ‘Accept’ and ‘Content-Type’ names set to ‘application/json;odata=verbose’, then your values should almost always have a “d” as the first element in the data, but there are some exceptions, especially when you are evaluating returned errors

The JSON formatted data from the above request is shown here for convenience (I replaced the ‘contosa.sharepoint.com/sites/dev/_api/Web’ with ‘…’ to make it easier to read:

{
    “d”: {
        “results”: [
            {
                “__metadata”: {
                    “id”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari 2.jpg’)”,
                    “uri”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari%202.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Ferrari 2.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari 2.jpg”
            },
            {
                “__metadata”: {
                    “id”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari.jpg’)”,
                    “uri”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Ferrari.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari.jpg”
            },
            {
                “__metadata”: {
                    “id”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Maserati.jpg’)”,
“uri”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Maserati.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Maserati.jpg”,
“ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Maserati.jpg”
            }
        ]
    }
}

Evaluate the data elements returned

Now, you go down the tree using a forward slash (/) between the levels of the elements (Values between square brackets ([]) are in themselves array elements and need to be accessed with a counter)

To get the full array of data, move the Response into a variable of type Dictionary called ‘CurrentResults’.
This is useful when you do not wish to make your code more readable by specifying a shorter key to get the data from ‘CurrentResult’ instead of from ‘Response’

Get d/results from Variable:Response (Output to Variable:CurrentResults)

To get all data of the first element into a variable of type String called ‘RespString’:

Get d/results(0) from Variable:Response (Output to Variable:RespString)

To get all data of the second element into a variable of type String called ‘RespString’:

Get d/results(1) from Variable:Response (Output to Variable:RespString)

To get the attribute ‘FileName’ of the 3^rd element into a variable of type String called ‘FileName’:

Get d/results(2)/FileName from Variable:Response (Output to Variable:FileName)
After this statement, the variable ‘FileName’ will contain ‘Maserati.jpg’.
The statement Get CurrentResults(2)/FileName from Variable:Response (Output to Variable:RespString) would yield the same result

To get the attribute ‘ServerRelativeURL of the 3^rd element into a variable of type String called ‘SRU’:

Get d/results(2)/ ServerRelativeURL from Variable:Response (Output to Variable:SRU)

Troubleshoot any errors returned

If you receive an error when issuing an HTTP Get action, the ‘ResponseStatusCode’ variable will have a negative comment such as ‘NotFound’ or something similar. In this case, the HTTP Get call will place the error results in JSON format into the ‘ResponseContent’ variable. As a standard I always write the ‘ReponseContent’ variable into the Workflow Log so I can tell if there was a problem with the call as follows (If you do not do this, it may be difficult to troubleshoot any problems):

204-5

In the screen shown above, if the HTTP Get Call was not successful (Variable:ResponseCode not-equal ‘OK’), I added some code to display the actual reason for the error

The Error message written to the ResponseContent variable does not have the same “d/results’ type format as the statement shown above. Instead, it returns a value similar to the one shown below:

{“odata.error”:{“code”:”-2146232832, Microsoft.SharePoint.SPException”,”message”:{“lang”:”en-US”,”value”:”Group cannot be found.”}}}

When you paste this message into the JSON viewer, it generates the following structure:

{
    “odata.error”: {
        “code”: “-2146232832, Microsoft.SharePoint.SPException”,
        “message”: {
            “lang”: “en-US”,
            “value”: “Group cannot be found.”
        }
    }
}

Using the same methodology as shown previously, we can move the error message into a variable with the following action

Get odata.error/message/value from Variable:Response (Output to Variable:ErrorMessage)

We can then display this variable in the workflow log to get more detail on our problem. In this example, ‘ErrorMessage’ contains the value ‘Group cannot be found’. Now I know that the User Group was not found, probably because the name was incorrect

7-05-2016 |

Workflow – Useful REST calls – Start a workflow

In SharePoint 2013 Microsoft modified the triggers on list items so that inserts or modifications to list items do not trigger the Workflows that are set to start based on those conditions. This was done in an effort to prevent recursive calls to the workflow initiation.

In summary then, even if you have set the workflow start options to ‘Start workflow automatically when an item is created’ or ‘Start workflow automatically when an item is changed’, this will not happen if an item is added or changed from WITHIN a workflow

206-1

However, even though it takes quite a few workflow statements, it is still possible to start the workflow manually from within the calling workflow. Most of the next steps that are performed in Workflow Designer are well documented in other online articles

Define the list and item that you want to start the workflow on

The first step is to specify the list and ID within that list that you want the workflow to run on

206-2

In this example, I set ‘String01’ to the name of the list that I wish to run the workflow on (‘Item List’) and ‘ID’ to the ID of the item within this list. In this particular case, I want to run another workflow on my current item, but in most cases you would use the ID from another list

Prepare the dictionary for the call

Insert a ‘Build Dictionary’ action and add the names ‘Accept’ and ‘Content-Type’, both with the value ‘application/json;odata=verbose’

202-3

Instead of using the defaulted name of ‘dictionary’ in the ‘Output to’ command, I use my own name of ‘Header’. This change of name is optional. At any rate, after the dictionary changes have been made, the command looks similar to the following:

Make the HTTP Web Service call

In the next line, insert a ‘Call HTTP Web Service’ action. This action is part of the process to get the GUID of the list on which the workflow will run. This will place something similar to the following in your workflow:

Click on ‘this’ and set the ‘HTTP web service URL’ to the following

206-5
[%Workflow Context:Current Site URL%]/_api/web/lists/GetByTitle(‘[%Variable: String01%]’)

Note that ‘[%Workflow Context:Current Site URL%]’ and ‘[%Variable: String01%]’ are inserted through the ‘Add or Change Lookup’ button. Also note that single quotation marks are required in the ‘GetByTitle’ value (Check that the single quotation marks are correct as these can sometimes be transformed in the cut-and-paste). The ‘HTTP method’ should be set to ‘HTTP GET’

Set the HTTP Call to use the Header dictionary that you defined earlier (the one with the ‘Accept’ and ‘Content‑Type’ values). Right mouse click on the call, and select the ‘Properties …’ option as shown below. Set the ‘RequestHeaders’ to the Workflow variable ‘Header’

204-1

Note: This ‘Header’ definition should be on every HTTP Call statement (even though I may not mention it again). Failing to have this value will result in 0 records being returned by the call

Change the Call’s defaulted names of ‘request’ to ‘Request’, ‘response’ to ‘Response9’, ‘responseHeaders’ to ‘ResponseHeaders’ and ‘responseCode1’ to ‘ResponseCode’. This is probably not necessary in your situation, provided you make the necessary substitutions when examining the code in this document. Once everything is complete, I have a statement that looks as follows:

This statement should execute without error, but if you have a problem, you can use a ‘Log’ action to display the value of ‘ResponseCode’ which should always be ‘OK’. Any other value denotes an issue (See my workflow posts about troubleshooting REST errors)

Get the GUID of the list

The previous statement makes a call to the SharePoint site and returns information into the dictionary we defined. We now need to interrogate the dictionary to get the GUID of the list. This is done with a ‘Get an item from a dictionary’ action

Click on the ‘item by name or path’ and type ‘d/Id’ in the fill-in. Then click on ‘dictionary’ and select the ‘ResponseContent to’ variable – in my case this is Response9. At the end of these two changes, your statement should look as follows:

This statement gets the GUID of the list (in my case the Current Item) and places it in the Workflow GUID variable ‘create’

Get the workflows for the list

Now we need to get a listing of all the workflows related to this list item. To do this, insert another ‘Call HTTP Web Service’ action and set the HTTP web service URL to the following 206-9

[%Workflow Context:Current Site URL%]/_api/SP.WorkflowServices.WorkflowSubscriptionService.Current/EnumerateSubscriptionsByList(guid’[%Variable: create%]‘)?select=Id,Name

This call will return a listing of all workflows related to the list (the GUID and Name are returned). Set the HTTP method to ‘HTTP POST’. I then reuse some of the variables that I defined before so that the final statement looks as follows (Remember to use the properties to set the HTTP Call to use the Header dictionary)

Prepare to loop thorough the Workflows

In some situations, as is the case with mine, there will be more than one workflow associated with a list item, so I need to loop through all of them to get the one I want. To perform this looping I use two variables ‘ContinueLoop’ (Boolean) and ‘calc’ (Number)

206-11

Use the Loop function from SharePoint designer to design a loop that continues until the returned workflow returns the name of the workflow I want. This assumes that the name I use for the Workflow (ItemWF02) actually exists otherwise your workflow will suspend itself

206-12

Read the data in the loop

The first statement within the body of the loop is a ‘Get an item from a dictionary’ action. Set the ‘item by name or path’ to type ‘d/results([%Variable: calc%])’ in the fill-in. Then click on ‘dictionary’ and select the ‘ResponseContent to’ a workflow variable ‘Response9’. At the end of these two changes, your statement should look as follows

This will return the details of the current entry (initially it will be the first entry as calc=0)

Using two more ‘Get an item from a dictionary’ actions, get the GUID and name of the Workflow as follows:

206-14

Now we have the name of the workflow. Is it the one we need? If so, set the value ‘ContinueLoop’ to ‘No’ so that we do not read any more values. If this is not the value we need, increment the counter (calc) by one so that the system will read the next workflow for the list, as shown below

206-15

Start the workflow

In the documentation I read online, the next statement that executes in the workflow will start the workflow on the list item. My experience is that on occasion, the system will NOT start the workflow on the first attempt, returning an error in the ResponseCode instead. The error appears to be random and may be due to demand on the servers processing the request. No matter, I cannot let this happen, so I create a loop to execute the workflow, pausing for a number of minutes between attempts. This has always worked for me, but you may see some of this code as unnecessary. I show the section of code in its entirety below, followed by a more detailed explanation of the key steps

206-16

Initialize the Workflow variables for the loop. Here we use the Counter ‘Count2’ (Number) and the Boolean ‘ContinueLoop’ defining them as follows:

206-17

Actual Workflow Start syntax

In the first part of the loop we increment the counter so that we are aware of which iteration we are within the loop, but the key statement is the second one. Here we add another ‘Call HTTP Web Service’ action and set the HTTP web service URL to the following

206-17

[%Workflow Context:Current Site URL%]_api/SP.WorkflowServices.WorkflowInstanceService.Current/StartWorkflowOnListItemBySubscriptionId(subscriptionId=’[%Variable: create%]’),itemId=’[%Variable: ID%]’

This is telling SharePoint that the system must start the workflow with the GUID = Variable:create on the item with ID = Variable:ID. Set the HTTP method to ‘HTTP POST’. I once again change the dictionary variables I use with this statement, but the final one looks like the following (Remember to use the properties to set the HTTP Call to use the Header dictionary)

If you do not believe me that the workflows do not always start, then ignore the entire loop and execute the statement above after you have the Workflow GUID

The code that follows this statement contain no magic and simply checks the ‘ResponseCode’ returned by the HTTP Call. Since I assume that the reason why the workflows do not execute is due to server load, I pause for a number of minutes before attempting the call again. If after 3 attempts the workflow has not started, I email the administrator that there is a problem

206-19

That is how to initiate a workflow from within another workflow

Workflow – Splitting the workflow into multiple parts

As of the time of writing, Microsoft has a limit of 5,000 on the number of operations that can be performed by a workflow within a 24 hour period, and workflows that exceed this quota are automatically suspended. More importantly no-one is notified of this suspension and if this workflow is not manually resumed within an arbitrary amount of time, the workflow itself is cancelled. Apparently, this is to prevent workflows from being stuck in infinite loops. That is all well and good, but my [largish] workflows were not in infinite loops and were still reaching the limit and going into suspension. With hundreds of workflows active at any time, it was a nightmare to manage this situation and resume each workflow

Looking carefully at my workflows, I found it was possible to divide them into 2 or more parts and call one workflow from the other (See ‘Start a workflow within a workflow’ for details on how to do this).

This has had a number of positive spinoffs, namely:

Whilst the original workflow was long and sometimes difficult to navigate through, the resulting workflows were smaller and easier to work with
The split workflows were quicker to publish
This is a subjective assessment, but the workflows seemed to execute much faster. This could be due to a smaller packet being passed between the servers between each step
The splits allow me to restart the workflow at different points in the process if needed
Since some of my workflows ‘live’ for lengthy amounts of time, the previously consolidated version would never function with a new update – it would run with the old functionality until it completed. In the split design, the system can execute the new designs when the split workflow runs.

Normally, I have two stages at the end of my workflows, the first of these uses the logic in ‘Start a workflow on another list within a workflow’ to start the second workflow. This stage then goes to the last stage which marks the calling workflow as complete.

A result of this issue has been that I always consider splitting the workflow between multiple SharePoint designer workflows and it has proven to be more of a help than a hinderance

Workflow – Scoping the App Step

If you are using the App Step in workflows (and I believe you should), then you need to specify which sites the workflow can update and the level of control that the workflow App Step will have within the sites (i.e. Can the workflow update the current site, the current site and the parent site or the entire site collection?). Failure to use the App Step can result in suspended workflows since the workflow object may be unable to read from or write to SharePoint lists)

Most of the data presented here is a direct copy of a great Microsoft article Create a workflow with elevated permissions by using the SharePoint 2013 Workflow platform

To enable the SharePoint Designer Workflows to have full control

Activate the “Workflows can use app permissions” site feature in the site where the workflow exists.

This is done in Site Settings>Site Actions>Manage site features>Workflows can use app permissions

207-4

Get the Workflow App Identifier

Navigate to Site Settings> Site app permissions where you will see an item called workflow as shown below

207-2

Copy the client portion of the App Identifier as shown below (the data between the | and @ characters , exclusive)

207-3

Give the workflow Full Control

Navigate to <site url>/_layouts/15/appinv.aspx to configure permissions. Then add the client section of the App Identifier in the App Id section and click Lookup to populate the content.

207-5

Paste the scope of the Full Control into the ‘Permission Request XML’ fill-in as follows:

For rights to the current site (Most common usage), paste this into the Permission Request XML exactly:

For rights to the entire collection, paste this into the Permission Request XML exactly (You will need tenant access to do this):

For rights to the current, parent and sub-sites, paste this into the Permission Request XML exactly:

In some cases you may be dealing with user related data, such as getting the manager for a particular user. In this case you will need to scope as follows (I found that I was unable to get the manager details unless I had this scope set):

Note 1.

Previously I experienced an ‘Unauthorized’ response from a workflow REST call to add a user to a group. This problem was fixed by specifying the Workflow Request scope, as ‘http://sharepoint/content/sitecollection’ since the users are defined for the entire collection and not for the individual sub-sites

Note 2.

If you want a workflow to have multiple scope requests, you need to add the Application Requests Scopes into a single statement, for example:

Trust the workflow

You will then need to trust the workflow by clicking on the ‘Trust it’ button in the dialog box that is displayed

207-6

Ensure that the App Step’ option is available in the Designer

If the App Step option is not available in the SharePoint Designer menu, exit from the Designer and restart it. The ‘App Step’ should then be available

207-7

Once, you have clicked on ‘App Step’, add all your code within this group.

For more information on scoping, check out this excellent article by Fabian Williams: Gotcha on SharePoint Designer Workflows in App Step

Workflow – Useful REST calls – Attachments in email

From a workflow, you may want to find the attachments that are linked to a list item or even display these attachments within the body of an email. The next steps will guide you through this process. For those among us who are impatient, most of the entire process is shown in the figure below

203-1

Build a dictionary variable

Select the ‘Build a dictionary’ action and click on ‘this’ and use the ‘Add …’ button to define the dictionary names ‘Accept’ and ‘Content Type’, both og which are defined with exactly the same value: ‘application/json;odata=verbose’

202-3

Change the name ‘dictionary’ to specify a new dictionary variable ‘Header’. The end result looks as follows

Define the REST call

Set a variable (String01 in this case) to the HTTP Web Service Call (REST Call) that will get the attachments. This is set to a combination of your site, list and list item as follows

<site>_api/web/lists/getbytitle(‘[%Workflow Context:List Name%]/items([%Current Item:ID%]/AttachmentFiles

203-4

This will return all attachments related to the current item. If you were looping through all items in the list, you could leave out the ([%Current Item:ID%])

Call the web service

Use the REST string previously defined in the ‘String01’ variable to call the web service, setting the ‘RequestHeaders’ in the Properties to the dictionary variable previously defined

203-7

203-6

Data Returned from HTTP Web Service call

When the HTTP Call executes, it will return a packet of data to the ResponseContent variable ‘Response’ that looks similar to the following (Note that is your ‘RequestHeaders’ are set as defined previously, the system will return values in JSON format:

XML (Multiple iterations of ‘value’ removed to save space):

<?xml version=”1.0″ encoding=”UTF-8″ ?>
<odata.metadata>https://contoso.sharepoint.com/sites/dev/_api/$metadata#SP.ApiData.Attachments</odata.metadata>
<value>
 <odata.type>SP.Attachment</odata.type>
 <odata.id>https://contoso.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari 2.jpg’)</odata.id>
 <odata.editLink>Web/Lists(guid’f2c6ac85-9999-222a -a306-2433922ff36d’)/Items(154)/AttachmentFiles(‘Ferrari%202.jpg’)</odata.editLink>
 <FileName>Ferrari 2.jpg</FileName>
 <ServerRelativeUrl>/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari 2.jpg</ServerRelativeUrl>
</value>

JSON:

{
    “d”: {
        “results”: [
{
                “__metadata”:
{
                    “id”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari 2.jpg’)”,
                    “uri”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari%202.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Ferrari 2.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari 2.jpg”
},
{
                “__metadata”: {
                    “id”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a -a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari.jpg’)”,
                    “uri”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Ferrari.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari.jpg”
            },
            {
                “__metadata”: {
                    “id”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Maserati.jpg’)”,
                    “uri”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Maserati.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Maserati.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Maserati.jpg”
            }
        ]
    }
}

Note: If you forgot to specify that your ‘ResponseHeaders’ are in JSON format, then the above values will be returned with ‘value’ instead of ‘d/results’ as shown below. This will cause the following commands not to work:

{
    “odata.metadata”: “https://contosa.sharepoint.com/sites/dev/_api/$metadata#SP.ApiData.Attachments”,
    “value”: [
        {
            “odata.type”: “SP.Attachment”,
            “odata.id”: “https:// contoso.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari 2.jpg’)”,
            “odata.editLink”: “Web/Lists(guid’f2c6ac85-9999-222a-a306-2433922ff36d’)/Items(154)/AttachmentFiles(‘Ferrari%202.jpg’)”,
            “FileName”: “Ferrari 2.jpg”,
            “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari 2.jpg”

If you look closely at the JSON data above, you will notice that there are multiple iterations of the ‘value’ data element. Since we need to know how many attachments there are, we must move all these iterations into a separate data dictionary to count them. Add a ‘Get’ action to move the ‘’d/results’ elements from variable ‘response’ into the dictionary variable ‘ItemData’

203-9

Count the number of items (Attachments) returned

Once the ‘d/results’ iterations are in the ‘ItemData’ dictionary variable, we count them with the ‘Count Items’ action and place the value in the integer workflow variable ‘CountOfItems’

Initialize the variables for the loop

Initialize the workflow variable (Count) that will indicate which iteration we are working through in the list. While the kneejerk reaction is make this an integer variable, I recommend making it a ‘Number’ variable since it will save a workflow step when this value is incremented later (Don’t concern yourself that it sets the value to 0.0 – it is still 0)

Since we will be creating a ‘special’ HTML string that will contain the images of the attachments (In this case I know the attachments are images), we need to empty this string first. Use the following command to set ‘String03’ to empty

Loop through the attachments (items) returned

Insert a ‘Loop n Times’ action that will loop the number of times indicated by the attachment count you set earlier in the ‘CountOfItems’ variable. This loop will be used to iterate through the ‘values’ returned by the call and build up the data that we wish to include in an email

203-13

Read each attachment

Use a Get statement to read the ServerRelativeURL from the data returned by the HTTP Call for the current iteration indicated by [%Variable:Count%] (the first iteration will be 0, the second 1 and so on). You will notice that I included this statement in a step to make the coding more readable

203-14

It is also possible to get this value from the ItemData dictionary variable defined earlier to get the count from the ‘ItemData’ dictionary instead of the ‘Response’ dictionary using the following action:

Get ([%Variable:Count%])/ServerRelativeURL from Variable:ItemData (Output to Variable:String01)

While this possibly makes it easier to read, I decided to use the structure from the top down to provide more understanding about what is going on

Use a Get statement to read the ‘FileName’ from the data returned by the HTTP Call for the current iteration indicated by [%Variable:Count%]. Follow the same process as you did for the ServerRelativeURL

203-15

‘Fix’ the data by replacing special characters

Replace any special characters (such as spaces and exclamation marks) in the ServerRelativeURL to ensure that if the user clicks on the link it will open up correctly. In my example I replace commas (,), exclamation marks (!) and spaces ( ) with the appropriate encoded characters (%2C, %21 and %20)

Thereafter, log the result (optional) if you wish to view the final URL

203-16

Add the image into the final string that will go into the email

Now that we have the Server Relative URL and the File Name of the attachment, we can construct the body of the email. In my case, I wish to embed the pictures within the email but you could also define a link to the image instead

203-17

As a convenience, I have included the text values here so you can cut and paste if necessary (Note that the [%Variable:String01%] and [%Variable:String03%] will have to be substituted with actual look-ups

[%Variable:String03%]
<tr>
 <td style=”width: 193px”><a href=”[%Variable:String01%] “>
 <img src=”[%Variable:String01%] ” width=”175″/></a></td>
 <td style=”width: 9px”> </td>
 <td style=”width: 600px”>[%Variable:String02%] 
 
</tr>

‘String03’ is made up of String03 and the new image contained in ‘String01’ together with the title of the attachment in ‘String02’

Increment the counter

Once we have finished with the list item, increment the counter and restart the process for the next iteration. You will notice that because the variable ‘Count’ is a number (as opposed to an integer), you can simply increment its value by adding 1. If the count variable was created as an integer, you would have to first increment a number value and then set the integer value to the incremented number value, requiring two steps

203-18

Define the email

The last step is added AFTER the end of the loop and emails the filenames and images to someone. In this case, the system will email the individual that is maintained in the people picker field ‘Current Item:User’. This is a very simple email but should serve to illustrate what to do. Feel free to add any additional details

203-19

Final result

You should be good to go and your code should then send an email similar to the following (The Maserati was too expensive and was trimmed off to save space)

203-20

7-04-2016 |

Workflow – Set string to empty

Surprisingly there is no standard function is SharePoint Designer to set a string field to empty. After many workarounds I realized that the easiest way to do this is through the ‘Extract Substring of String from Index with Length’ action, specifying the length as 0.

For example, assuming that we wanted to set the string ‘String01’ to empty, we could use the following ‘Extract string …’ statement:

Copy from Variable:String01, starting at 0 for 0 characters (Output to Variable:String01)

Since 0 characters are being copied, the resulting string ‘String01’ is empty

7-03-2016 |

Workflow – Useful REST calls – Get users in a group

From within a workflow you may want to get a listing of users within a specific group. This requires the use of a REST call which builds a dictionary of users within the group and then loops through them one by one

Define the REST call in a string

Set a Workflow variable to contain the partial contents of the REST call to the user group
202-1
[%Workflow Context:Current Site URL%]_api/web/sitegroups/GetByName(‘UserGroupName’)/usersWhere UserGroupName is the name of the group that you wish to get the users from (Note the quotation marks are important)

Build a dictionary to define the return format

Next, insert a ‘Build Dictionary’ action. Initially, it appears as follows:

Click on ‘this’ and use the ‘Add …’ button to define the dictionary names ‘Accept’ and ‘Content‑Type’ as follows:
202-3
Both of these items have exactly the same value ‘application/json;odata=verbose’

Use a ‘Call HTTP Web Service’ action to make the REST call

In the next line, insert a ‘Call HTTP Web Service’ action. This will place something similar to the following in your workflow:

Click on ‘this’ and set the ‘HTTP web service URL’ to the workflow variable ‘String01’ and the HTTP method to ‘HTTP GET’

202-6

202-7

I once again change the Call’s defaulted names of ‘request’ to ‘Request’, ‘response’ to ‘Response’, ‘responseHeaders’ to ‘ResponseHeaders’ and ‘responseCode1’ to ‘ResponseCode’. This is probably not necessary in your situation, provided you make the necessary substitutions when examining the code in this document.Once everything is complete, I have a statement that looks as follows

Get the number of items (users) returned by the call

The previous statement makes a call to the SharePoint site and returns information into the dictionary we defined. We now need to interrogate the dictionary to get the results of the call. This is done with a ‘Get an item from a dictionary’ action

Click on the ‘item by name or path’ and type ‘d/results’ in the fill-in. Then click on ‘dictionary’ and select the ‘ResponseContent to’ variable’ Response’ and Output the result to a dictionary variable called DataSet. At the end of these changes, your statement should look as follows:

You then should count the number or items (users) that have been returned and place the result in a workflow variable. This is done using a ‘Count Items in a dictionary’ action as shown below:

Prepare the initial values to loop through the list of users

Since I intend to loop through all the items in the dictionary, and write data to a variable, I need to prepare the initial values. This is done by setting ‘Count’ to 0 and ‘String02’ to an empty string. I also write to the log file to indicate how many items have been returned and the ‘ResponseCode’ of the call in case there are problems
202-12

Loop through the users returned by the REST call

The next group of statements loops through the returned values and writes the ID of the user name to a variable (I write the value of the ‘LoginName’ to a variable update the Current Item as shown below). These values (LoginName, Id) are a couple of the properties that are returned by the HTTP GET, but there are others that can be used, such as ‘Email’. For a comprehensive listing of fields available, visit https://msdn.microsoft.com/en-us/library/office/dn531432.aspx

202-13 The key part of this step is the second last statement which concatenates all the user ID’s into a single workflow variable, ‘String02’. A word of caution here: Designer Workflows have a limit of 255 characters, and if your code attempts to write a larger value to a string, the workflow will suspend. If this happens, two of the options available for addressing the problem are:

Write the data to a multi-line text field instead. I sometimes include such a field in a temporary table and use an ‘Update List Item’ action to update the list. Then, in an InfoPath form, you can validate against this data. In the example above, because I expect that the values of the User Account ID’s will exceed 255 characters, I do exactly this in the ‘then Update item in Current Item’ statement which writes the following value to TempMultiLine : [%CurrentItem:TempMultiLine%];[%Variable: String01%]

..or ..

Write the data to a Multiple Value People Picker field. This means that you will have to prepare a valid People Picker format in each iteration and write it to the field

Once the loop is finished, I have all the ID’s of the users in the group in the workflow variable ‘String02’. I then prepare this data in a format the SharePoint Person/Group field will accept. The next few statements will refer to the following figure (do not expect the string containing the number of users in this particular group to be greater than 255 characters)
202-14

Build the user string

If the string is empty, I set the update string ‘String02’ to a special sequence, not an empty string. This is because the empty string representation in a multiple Person/Group picker field is different to an empty string in a single Person/Group picker field. In this case, the value is set to

{“__metadata”:{“type”:”Collection(Edm.Int32)”},”results”:[]}

If the string is not empty, I set the initial part of the update string ‘String01’ to the following:

{“__metadata”:{“type”:”Collection(Edm.Int32)”},”results”:[

I then join the user ID’s that I assembled in the previous statements to the string ‘String01’

[%Variable: String01%][%Variable: String02%]

To terminate the string, I set a ‘String02’ to the final characters as shown below (this is done because SharePoint Designer sometimes complains about special characters being used together, and this is a workaround since it does not object to variables being used together)

]}

I now concatenate the prefix (String01) and suffix (String02) to formulate the final update string, reusing ‘String02’ as the workflow variable that contains this value

[%Variable: String01%][%Variable: String02%]

The String is now ready for writing to a SharePoint Person/Group field and would look something like the following if you displayed the value in a log

{“__metadata”:{“type”:”Collection(Edm.Int32)”},”results”:[16,330,19,27,1960]}

… where the numbers 16,330,19,27, and 1960 are the user ID’s within the group

Update the user list

In the last ‘Update item in Current Item statement, I write the string to a Person/Group string in my current item (Please pardon the field name)
202-15

7-02-2016 |

Workflow – Add users to People Picker

You may wish to update a list of individuals in a SharePoint People Picker field for a number of reasons including filtering a View based on the users in this field. This is basically performed through string manipulation and in the example shown below we will update the list of viewers for a list item within a workflow step. The entire process is shown in the figure below and then broken out in separate steps (These steps apply to the multiple value People Picker field – to assign a single value people picker field is much simpler and involves setting the field to the login name)

201-1 Get the current list of viewers of the list item

We first need to get the current list of viewers of the list item into a workflow variable. To do this we set the Workflow Variable to the User ID’s of the multiple value People Picker field. In our case we use the People Picker field that is called ‘Viewers’

201-2

Get the user login ID

The next step is to add the login ID of the user who modified the list item to the list of current viewers. Here we reuse the the Workflow Variable ‘String01’ and add the User ID Number of the person who modified the list. Note the comma between the value shown below and that it makes no difference if the User ID already appears in the viewer list as SharePoint will ignore the duplicates

201-3

We now have a comma separated list of User ID’s stored in the Workflow Variable ‘String01’

Define the prefix of the final People Picker string

This step stores the first part of the multiple value People Picker string in the variable ‘String02’. This value is exactly

{“__metadata”:{“type”:”Collection(Edm.Int32)”},”results”:[

201-4

Define the suffix of the final People Picker string

The last part of the people picker string is stored in the Workflow Variable ‘String03’ (This cannot be done during the final construction of the string since it will generate an error). The value is set to:

]}

201-5

Combine the temporary variables to formulate the final viewer list

We now combine the elements of the People Picker string into a final string that we will use to update the list of viewers. Once again we reuse the ‘String01’ for this purpose. Note that these strings are not separated by any characters.

201-6

After this step the ‘String01′ should look something like the following but with different ID’s in the body (the value ’22’ is duplicated here to underscore the fact that duplicate ID’s will not cause an error):

{“__metadata”:{“type”:”Collection(Edm.Int32)”},”results”:[109,98,167,22,36,22]}

Update the list item with the new viewer list

Now overwrite the viewer list with the list that you have constructed into ‘String01’

201-7

The list of viewers now contains the user who has just modified the form. We then log the final list of viewers to help with troubleshooting (Unlike the code shown here, it is better to show the ‘Log’ statement before the update)

Create a view that filters with the ‘[Me]’ option

In SharePoint we then modify the view of the current item to only display the list items when the ‘Viewers’ equal [Me]. Note that here we use ‘is equal to’ instead of ‘contains’. This view will only display the items when the Viewers field contains the ID of the individual who is currently accessing the view

201-8