Tag : number of rows

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

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:

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)

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

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)