Search code examples
restdynamics-crmodata

Error when using tolower with OData REST API call: An entity member is invoking an invalid property or method

As suggested here and here, I should be able to apply tolower to do a case-insensitive field match with a REST call to the OrganizationData.svc endpoint.

Here's my GET request:

/XRMServices/2011/OrganizationData.svc/ContactSet?$filter=tolower(EMailAddress1) eq '[email protected]'

And I get the following result:

<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
  <code>-2147220715</code>
  <message xml:lang="en-US">
    Invalid 'where' condition. An entity member is invoking an invalid property or method.
  </message>
</error>

What am I doing wrong? How can I do a case-insensitive match on the EmailAddress1 field on the Contact entity using the REST endpoint?

(Note: I know I can change the SQL Server collation to case-insensitive but I need this to work for Dynamics online as well).

SOLUTION

So it looks like tolower is not supported at all in the OData implementation for the Dynamics REST API. If you need to do a case-insensitive match, the only way I found is to change the SQL Server database collation in an on-premise install.

For Dynamics online, you'd probably need to write a plugin that fires on entity create/update and converts the field to lower-case.

Solution

  • You would not be able to do that. By default all information (and in CRM Online) as well is stored in case-sensitive DBs so you just would not be able to do case insensitive search.