Small and medium business | Business Central, N...

Substring with case Insensitive filter is not working OData V4

(0) Share

Report

Posted on by vinoth vk

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:

Dynamics 365 Business Central

I have the same question (0)

All responses (4)

Answers (0)

Lars Lohndorf-Larsen on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Frank Hillsnipe 1,332 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

awicz 19 on at

Like (1)

Report

Use the @ symbol to enable case-insensitive results:

$filter=tolower(Description) eq 'dininG chaiR'

https://learn.microsoft.com/en-nz/dynamics365/business-central/ui-enter-criteria-filters#fine-tuning-the-search-with-filter-criteria

Was this reply helpful? Yes No
Suggested answer

NR-10050744-0 4 on at

Like (1)

Report

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*'

Was this reply helpful? Yes No

Join the conversation

Community site session details

Substring with case Insensitive filter is not working OData V4

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates

Community site session details

Substring with case Insensitive filter is not working OData V4

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates