Wednesday, February 14, 2018

OData Queries in Azure Logic Apps

I am building some Azure Logic Apps and came across a problem where I wanted to get a List of records from Dynamics 365 Customer Engagement (CRM) and update some data in SharePoint. In order to get the records I wanted from CRM, I needed to use an OData filter expression. I was not sure what it should look like so I turned to the XRMToolbox FetchXML Builder which generates OData expressions like this

https://xxxxxx.api.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/AnnotationSet?$top=50&$filter=ObjectId/Id eq (guid'9a19c4eb-bc3b-45e1-adc8-c5cf1716101b')

I could see the filter used an unusual notation of “ObjectId/Id eq (guid'9a19c4eb-bc3b-45e1-adc8-c5cf1716101b')” and if I executed that REST call then I could get a response from CRM. But when I use the filter expression in the Logic App, I would get this error message.

“Could not find a property named ‘ObjectID’…”

clip_image002

So I created a stand-alone Logic App that had a single List Records from CRM (oops, Dynamics CE) to see what it returns:

image

I noted that GUID field names were converted to using an underscore in front of the field name, and “_value” after the field name, and that the field name was all lower case. This told me that under the covers, OData is dealing with EntityReference fields by converting the GUID to a string value. So I changed the Filter to use the adjusted field name together with “eq” and the unique identifier from a prior query like this:

clip_image002[4]

which now returns exactly the list I needed.

No comments: