How to do search with no respect to case.

(0) Share

Report

Posted on by martinav

3,347

I have this condition. I need to return no matter what the case is. Helpie?

Filter(SortByColumns(sData,"PartNumber", Descending), PartNumber in Gallery1.AllItems.'Part Number')

Categories:

Building Power Apps

All responses (6)

Answers (0)

Sort by

KennyJ 168 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,
I would wrap the field im searching and the search text in the Lower function, this way all results are in lower case and doesn’t matter how they were entered.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@martinav

Since you are using the IN operator, it will ignore case. The ExactIn function would be used if you want exact case match.

Please consider changing your Formula to the following:

SortByColumns( Filter(sData, PartNumber in Gallery1.AllItems.'Part Number' ), "PartNumber", Descending )

The difference is that you should only be sorting the results of the filter not sorting and then filtering.

Because - if you have 1000 records and your criteria for the filter will only end up returning 5 results, then with your original formula, you would have sorted 1000 records and then returned 5. With the above, you are filtering the 1000 down to 5 and then sorting them. It's better performance.

But, beside the performance part, your original formula should give you what you want - not case related. So, if it was not, what are you seeing?

I hope this is helpful for you.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes ,

Yes, this was taking case into account. Hence, my post. I had a different issue that was not returning only a third of the results. Also, I have the issue that subsequent refres of this command does not return all columns. Only PartNumber. This is a problem.

The internal sort was to eliminate the possibility that there was some odd thing going on with a return one result only scanning once in order... similar to a HLookup command in Excel, where the input data needs to be sorted.

Anyway, I removed the sort. Issue still remains. Case does matter, and also the odd issue only returning one column of data when I re-run the command. Yes, I am sure its the same command. I copy pasted it from the OnVisible of the paste to a button. I have to close the app, and reopen to see all columns. See below:

On first run:

I fix the source date for the case of t000001, I re-run the command from a button, then I get this:
I get my return for T00001, but now, I only get the single column. I checked the collection in a table of its own, and indeed, only one column is there. Where did the others go???

ClearCollect(Test_SQL, With({sData: VIEW_ALL_PDM_LATEST}, Filter(sData, PartNumber in Gallery1.AllItems.'Part Number') ) )

So, bottom line. This statement works, but only the first time the app is run, and case matters.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

Hmm, it seems the single column issue is an anomaly when running in edit mode. Running app published the columns work fine. However, the case issue remains. The statement shows is case dependent. Regardless.

This still needs a solution. Using Lower() on both sides of the 'in' statement does not help either.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@martinav

Turn the "Explicit column selection" feature in your app setting to off if you want all the columns to appear.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes ,

It does work in published execution. Does this setting only affect edit mode? I'm hesitant to mess with these settings unless its absolutely necessary. My experience is that there are often unexpected results in other areas when changing these settings.

Rod