MSDyn365 MVP Jonas Rapp threw down a challenge on Twitter last week:

Since I’d just been doing some work with Web API queries I decided to take a look. Thankfully a lot of the code was already in place from the existing OData 2 query builder (Web API uses OData 4), but there were a few interesting quirks that needed working through. Of course, now the work’s done you hopefully won’t need to dig into this too much, you can just use FetchXML Builder and let it do it for you! And because Web API is used by the Common Data Service Flow connector List Records action this also forms the basis for generating the required parameter values to help connect your CRM data with Flow.

Although you now shouldn’t need to dive into the details of OData syntax in order to use Web API or Flow with this tool, for the interested among you:

Property Names

The most difficult part for me in hand-crafting Web API queries has been working out the property names to use. As far as I can tell, it’s not described anywhere in the Web API documentation, so there’s a chance I’ve got some of this wrong, but here goes:

  • Most property names are the same as the attribute logical names (not schema names as in OData 2). The logical name is shown in the customization screens and is all lower-case
  • Lookup properties (if you want to select the guid of the related record) use the format _logicalname_value. I believe this is to differentiate them from…
  • Navigation properties (if you want to join to the related record to get additional details) use the navigation property name defined in the relationship metadata. This is largely the same as the schema name for the relationship, but I don’t believe this is required to be the same. In particular, lookup attributes that can target multiple different entity types (such as Customer, Owner and Regarding attributes) will have multiple different relationships, so remember it’s the relationship name, not the lookup attribute name, that’s required here
  • Query functions (more on these below) use the attribute logical names rather than the property names, even for lookup attributes

Filtering

OData 4 supports only a few simple filter operators:

  • eq (=)
  • ne (<>)
  • lt (<)
  • le (<=)
  • gt (>)
  • ge (>=)

On top of these operators, you can also use some standard functions to compare string values:

  • contains
  • startswith
  • endswith

However, FetchXML defines a huge number of additional comparisons, such as “Equals Current User”, “Last X Financial Periods” etc. These are translated into custom query functions and can be used as $filter=Microsoft.Dynamics.CRM.LastXFiscalPeriods(PropertyName='createdon',PropertyValue=5)

For these functions, any lookup attributes should be identified by their logical names, not with the _logicalname_value syntax. This is another example of where we need to use trial and error to work things out – the documentation talks about property names but trying to use that format gives an error.

For “parent” related entities, i.e. records related to the main entity via an N:1 relationship, you can extract any of the values from the related record using the $expand query option in the format $expand=navigationproperty($select=property1,property2).

In pre-v9 there seems to be a bug that you cannot include a lookup property from the related entity. $expand=primarycontactid($select=_parentcustomerid_value) will give an error in v8.2, but work as expected in v9+.

Only the primary key field of the related entity can be used in filters. You can find accounts with a particular primary contact using $filter=primarycontactid/contactid eq <guid>, but you can’t find accounts with a primary contact that has a particular email address using $filter=primarycontactid/emailaddress1 eq '<email>' – this will give an error.

There’s no way to change the type of join being performed – it will always be an outer join. However, we can simulate an inner join by including a non-null filter on the primary key of the related record, e.g. $expand=primarycontactid($select=fullname)&$filter=primarycontactid/contactid ne null. This conversion is handled for you automatically as part of FetchXML Builder.

“Child” related entities, i.e. records related to the main entity via a 1:N relationship can be included using the same $expand syntax, e.g. $expand=contact_customer_accounts($select=fullname) to get the list of contacts from an account.

If you look at the results of the query however, you probably won’t actually see the contacts and their names. What you’ll see is a list of results that each look like:

{
  "@odata.etag":"W/\"718683439\"",
  "name":"Data8",
  "accountid":"0e3419fd-0ecd-de11-822e-0014224c6a9a",
  "contact_customer_accounts":[ ],
  "contact_customer_accounts@odata.nextLink":"https://contoso.crm.dynamics.com/api/data/v9.1/accounts(0e3419fd-0ecd-de11-822e-0014224c6a9a)/contact_customer_accounts?$select=fullname"
}

You might expect the list of contacts to be in the contact_customer_accounts property, but this will normally be an empty array. Instead, you need to use the URL given in the contact_customer_accounts@odata.nextLink property to actually retrieve the related records. Presumably this has been done for performance reasons.

The documentation states that you will need to use the @odata.nextLink property to retrieve the related records. In my testing however, there do seem to be some instances where the details of the records are actually returned in the main body of the first response, so make sure to check both!

Again it’s not possible to change the join type – it will always be an outer join. You can apply sorts and filters to the list of child entities, but not use the child entities to filter or sort the main entity list. Those sorts and filters are included in the same $expand option as the $select that lists the fields to be included from the child entities, but separated by semicolons instead of ampersands, e.g. $expand=contact_customer_accounts($select=fullname;$filter=firstname eq 'Mark';$orderby=lastname)

Date Value Oddities

Date values can be either in yyyy-MM-dd format, or yyyy-MM-ddTHH:mm:ss.ttttt+ZZ:ZZ format. However, there seems to be some problems with how time zones are handled – a time zone behind UTC (i.e. starting with a -) seems to be handled correctly, but a time zone ahead of UTC (i.e. starting with a +) seems to trigger an error message. Playing safe and just using Z as the time zone (i.e. UTC) works consistently.

String Value Escaping

String values should be enclosed in single quotes, which immediately throws up the question of “what about values that contain single quotes”? What if I want to search for a value of “Mark’s Opportunity”?

The simple answer is that any single quotes should be doubled up, so the query would become $filter=topic eq 'Mark''s Opportunity'

Aggregates & Grouping

Aggregate queries are handled through the $apply query option. This doesn’t seem to be supported prior to v9 though (again, I can’t find any documentation detailing this for sure).

The count aggregate option isn’t supported by OData 4, only a distinct count. However, if you use the primary key column for your distinct count then you will still get a total number of records.

Grouping can be combined with filtering, but does not appear to work with sorting. The OData 4 specification indicates that it should be possible to combine aggregation with sorting, but I haven’t been able to get this to work. It should also be possible to use $select to specify the columns you want to return after the aggregation, but again, I haven’t been able to get this to work with Web API.

Breaking FetchXML Builder!

Finally, a quick reminder to myself – XrmToolBox and FetchXML Builder are used by a lot of people, so don’t break them by not thinking about how changes will work on different versions (sorry Jonas)!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.