0

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 'me@awesome.com'

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.

Community
  • 1
  • 1
Zaid Masud
  • 13,225
  • 9
  • 67
  • 88

1 Answers1

0

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.

Zaid Masud
  • 13,225
  • 9
  • 67
  • 88
Andrew Butenko
  • 5,048
  • 1
  • 14
  • 13
  • Please see the two "here" links in my question, which show examples of how this can be done. – Zaid Masud Feb 07 '14 at 09:11
  • You should recheck [SDK](http://msdn.microsoft.com/en-us/library/gg309461.aspx). Tolower is not supported function for Odata usage in Microsoft CRM. – Andrew Butenko Feb 07 '14 at 11:55
  • But why does it say so on this page: https://learn.microsoft.com/en-us/dynamics-nav/using-filter-expressions-in-odata-uris and still I am getting the error `Error sending request: Not Implemented. Code: 0x0 Message:The "tolower" function isn't supported.` – CularBytes Dec 24 '21 at 10:27