Tag : InternalServerError

Workflow – Troubleshooting REST calls

When the REST/HTTP Call to a Web Service is not returning correct results, the reason why may not be immediately obvious. In these cases you may need to interrogate the data returned to discover what went wrong

Check the Response Status Code

Add a Log action after the rest call to display the value of the ‘ResponseStatusCode’ variable ‘ResponseCode’ as shown below

205-1

Check the logged Response Status Code

Run your workflow and check your workflow log. If everything is OK, this will normally return a positive indication such as ‘OK’ or ‘Created’. If there is a problem, it will display something similar to the message shown below:

205-2

If it returns a message such as ‘NotFound’ (as shown above) or ‘InternalServerError’ you may need to display the error in more detail, by logging the actual error message to the workflow log. This is done using a ‘Get’ action on the ResponseContent Variable (‘Response’) as follows:

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

205-3

The field ‘ErrorMessage’ is a workflow variable that I created to store the contents of the error message. Alternatively, you can display the entire ‘Response’ but displaying this value if the length is greater than 255 characters would hang your workflow.

Run the workflow again

Publish and run the workflow again and check your workflow log again. In my case it displayed the following

205-4

In my case, the message is saying that it could not find the SharePoint resource ‘GetByNameXXX’.

Aha! – It should be ‘GetByName’ not ‘GetByNameXXX’. I change my workflow to specify ‘GetByName’ instead of ‘GetByNameXXX’ and republish it. When it runs without error, I remove the error message from the workflow and the world is in balance

Other issues

If no error is being generated, it could be that the field names or their assignments are incorrect in the ODATA call. Check the following:

The body of the REST/HTTP call is normally in an ODATA format similar to the following:

[%Workflow Context:Current Site URL%]_api/Lists/GetbyTitle(‘Quotes’)/items/?$select=ID&$filter=Form_x0020_Status eq ‘In Approval’ and Active eq 1&$Top=500&$orderby=SpecApprover

Some common mistakes in the REST calls:

  1. Your field names are not in the correct case. If your SharePoint field is ‘Title’, then in the REST call it should be ‘Title’ not ‘title’
  2. Your field names are incorrectly stated in either the select, filter or orderby options. This can sometimes be tricky especially if your SharePoint fields have spaces in them (Not the best practice, but it happens). For example the SharePoint field Form Status must be specified as Form_x0020_Status. To find the real value of the field, open the list and select Library Settings. Scroll down to the field you are interested in and click on it. Then cut the full URL it navigates to and view the last bit after ‘Field=’. This is the internal field name. In my case, Form Status is actually Form%5Fx0020%5FStatus (Note how the system substitutes %5F for underscores and x0020% for spaces)
  3. You have mistakenly used ‘=’ instead of ‘eq’ or vice-versa in your ODATA statement
  4. Your Boolean vales are specified as true or false or yes or no. Boolean values must be specified as 1 for true and 0 for false. To check for records that have the field ‘Active’ equal to Yes, the ODATA statement should contain Active eq 1 and not Active eq Yes
  5. Your Top query option ($top) is not specified and will therefore only return 100 rows. If you expect more, then set ‘$Top’ to the maximum amount you expect (say ‘$Top=500’). At the time of writing, the maximum number of rows returned is 500
  6. Your string filter values are not enclosed in single quotes (for example BU eq AB should be BU eq ‘AB’ or BU eq ‘[%Current Item:BUShortName%]’
  7. Your number values are placed in single quotes (ID eq ‘307’ should be ID eq 307)
  8. When an HTTP Call statement returned more records than expected, I often found that either the names of fields or values was specified incorrectly (Boolean values in ODATA appear to be 1 or 0 instead of true or false for example, or perhaps the field name was misspelled or used spaces instead of the substitution characters)

 


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

203-3

… 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

202-8

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