Skip to main content

Notifications

Dynamics 365 Community / Forums / Business Central forum / Substring with case In...
Business Central forum
Suggested answer

Substring with case Insensitive filter is not working OData V4

(0) ShareShare
ReportReport
Posted on by vinoth vk 15

Working on the filter options through ODataV4 endpoints.

In the API I have a field name called Description and it has the value.

No: "BC001",

Description: "Black poly chair"

No: "BC002",

Description: "Dining Chair "

If I search for the word "Chair". that should bring all the Items ( Case shouldn't matter).

This is the Filter Option I'm using for it.

$filter=contains(tolower(Description),'chair').

Actual URL I'm trying:

api.businesscentral.dynamics.com/.../

Note: $filter= tolower(Description) eq 'dininG chaiR ') is Working, showing empty result.

When I apply the same, under the contain option., that time only its not working.

Example : In the below example they provided the solution that is for services.odata.org. I tried the same code for filter. but still I'm getting empty result.

https://github.com/OData/ODataSamples/issues/66#issue-257575195

Can you help me for solve this.

Thank in Advance

Categories:
Reports and Business Intelligence
Other
Sales
  • Suggested answer
    NR-10050744-0 Profile Picture
    NR-10050744-0 2 on at
    Substring with case Insensitive filter is not working OData V4
    Just replying as this was top result when I was searching for an answer as this might help someone else. 
     
    I acheived this by setting an equals filter but wrap the search term in '@*....*'
     
    So the filter above would be $filter = Description eq '@*chair*'
  • Suggested answer
    awicz Profile Picture
    awicz 17 on at
    Substring with case Insensitive filter is not working OData V4
    Use the @ symbol to enable case-insensitive results:
     
    $filter=tolower(Description) eq 'dininG chaiR'
     
  • Frank Hillsnipe Profile Picture
    Frank Hillsnipe 1,330 on at
    RE: Substring with case Insensitive filter is not working OData V4

    As of September 30th 2022 - This is a documented limitation, note the "Nested function calls" section in the article.

    learn.microsoft.com/.../use-filter-expressions-in-odata-uris

    "Nested function calls in filter clauses aren't supported in the current OData implementation. This means that filter clause expressions like contains(tolower(field), 'some') don't return the expected results - in this case a partial case-insensitive text search - but will instead either throw an error or return an undefined result."

    If anyone has a way around this issue I would greatly appreciate it.

  • Lars Lohndorf-Larsen Profile Picture
    Lars Lohndorf-Larsen on at
    RE: Substring with case Insensitive filter is not working OData V4

    Hello,

    I am interested too - I can get this to work:

    {{BaseURI}}/companies(2e396114-00ff-ea11-bc53-00155df3a615)/items?$filter=tolower(displayName) eq tolower('LONDOn Swivel Chair, blue')

    but this returns nothing:

    {{BaseURI}}/companies(2e396114-00ff-ea11-bc53-00155df3a615)/items?$filter=contains(tolower(displayName),tolower(%27chair%27))

    I know this does not help you much, but I will try to research a bit and return here if I find out something, and meanwhile I hope others also have some ideas.

Helpful resources

Quick Links

Take the Community feedback survey!

Answer this brief 15-question survey about your Community experience…

Demystifying Copilot: Service Edition with Sundar Raghavan

Sundar answers more questions about Copilot for Service...

Dynamics 365 Business Central vs Finance and SCM

Take a look at the key differences between Business Central and…

more Community News

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,377 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 223,308 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,140

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans