Make Lookup field connected to Business Central table case insensitive

(0) Share

Report

Posted on by Hagru

Hello!

I have been scratching my head because of this one for a while, so I am making another attempt at a Community Post.

I have a Dataverse table that contains a single field. This field is a lookup field to my Business Central Item table. The field itself works great, I can use it in a form, display its data, and select items from the lookup. I can also search in it, but this is where my problem stems from:

As an example, I will search for items with the word "Schestowitz", just because I thought it was a fun word.

As you can see in the first example, I am searching for "Sche" with a capital S, and I am getting three results.

In the second example I am searching for "sche" with a lower case S, and I am not getting any results.

The same would be the case for every other word. I will only get results for items with the exact case I am searching for, and it is quite annoying.

Here are some more info about the Lookup field that might be relevant.

Is there any way to make it case insensitive?

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

anandm08 1,936 Super User 2024 Season 2 on at

Like (0)

Report
hi @hagru ,
To make the search in your Dataverse table's lookup field case insensitive, you have a few options. Unfortunately, Dataverse itself doesn't provide an out-of-the-box setting to make lookup field searches case insensitive directly. However, you can work around this limitation using one of the following approaches:
1. Use a Custom Search Field
You can create a new calculated field in your Dataverse table that stores the lowercase version of the lookup field value. Use this field for searching instead. Here’s how you can do this:
Add a New Field: Add a new text field to your Dataverse table, for example, ItemSearchField.
Create a Flow or Plugin: Use Power Automate or a Dataverse plugin to populate this field with the lowercase version of the lookup field value whenever a new item is created or the lookup field is updated.
In Power Automate, create a flow that triggers on create/update of the record, converts the lookup field value to lowercase using an expression, and updates the ItemSearchField.
In a Dataverse plugin, write a plugin that triggers on create/update of the record, converts the lookup field value to lowercase, and updates the ItemSearchField.
Search Using the New Field: When performing searches, use the ItemSearchField instead of the original lookup field.

Let me know if my answer helped solving your issue.
If it did please accept as solution and give it a thumbs up so we can help others in the community.

Was this reply helpful? Yes No
Hagru 8 on at

Like (0)

Report

Hello @anandm08

Those lookup fields being strictly case sensitive is ridiculous to be honest. Hope an update allowing for case insensitive searches is on the roadmap for future updates.

I see you suggest one approach, which will probably work, but will still not cover every case, as people might be searching in all caps, or using upper case at the start of the search, and I feel it will not be sufficient enough for my case. Are there any other approaches that could fill the requirement?

Was this reply helpful? Yes No