cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AAK1
Helper I
Helper I

Search only working some of the time

I have an app that is working quite well, except for the search. It is searching a gallery. I'm looking through three fields based on input in a text box. Sometimes the search works perfectly well, other times the gallery is showing too many items, but if I delete one letter or number and then add it back in, it works fine. I can't find any reason for this. I tried using filter, but got the same intermittent problems. My code is below.

 

SortByColumns(Sort(Search('[dbo].[myTable]',txtSearch.Text, "Field1", "Field2", "Field3"),ID,Descending),"Field1")

I also tried

 

SortByColumns(Sort(Filter('[dbo].[myTable]',txtSearch.Text in Field1 || txtSearch.Text in Field2 || txtSearch.Text in  Field3),ID,Descending),"Field1")
10 REPLIES 10
davidstone
Kudo Kingpin
Kudo Kingpin

Do you know in what circumstances it shows too many results? Is it updating as you type?

It does update as you type. I'm not too sure beyone that. It seems like it may be different for each user as well. Someone was telling me they were having a problem with a specific search, but when I tried the same search, it worked fine. However, a different search did not work properly for either of us.

Your code looks right to me. I'm working on the latest build in improved rendering, and search seems to work fine.

 

Is it the same search terms that either do or don't work? If so, I'd compare that to what's in your data.

No, it's different all the time. The same search term might work this time, but not the next or vice versa.

v-xida-msft
Community Support
Community Support

Hi @AAK1,

Do you want to filter your SQL table records within a Gallery based on the search box value?

Based on the formula that you provided, I don't find any syntax error with it.

Please take a try to modify your formula as below:

SortByColumns(
Filter(
'[dbo].[myTable]',
txtSearch.Text in Field1 || txtSearch.Text in Field2 || txtSearch.Text in Field3
),
"ID",Descending,
"Field1",Ascending
)

then check if the issue is solved.

Please also take a try to turn off/disable the "Explicit column selection" option and "Improved app rendering (final validation)" option within Advanced settings of App settings, then check if the issue is solved.

If the issue still exists, please consider take a try to re-create your app, then try above formula and solution again, check if the issue is solved.

In addition, if your SQL table does not store too large records, you could also consider take a try to save your SQL table records into a Collection within your app, then use the Collection as a data source within your app instead of your Original SQL Table. Please take a try with the following workaround:

Set the OnVisible property or OnStart property of the first screen of your app to following:

ClearCollect(RecordsCollection, '[dbo].[myTable]')

Set the Items property of the Gallery control to following:

SortByColumns(
Filter(
RecordsCollection,
txtSearch.Text in Field1 || txtSearch.Text in Field2 || txtSearch.Text in Field3
),
"ID",Descending,
"Field1",Ascending
)

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yes, that's exactly what I'm doing. Filtering a SQL table based off text input.

 

I tried modifying the formula and am having the same intermittant issues. After closer inspection, it appears that it isn't seeing the last character of the search. A search for "4014" returns 3401, 4018, 4019, etc. If I delete the last character and add it back, the filter works properly so I think the formulas are correct.

 

I tried disabling those settings with no luck either.

 

I don't really want to rebuild the app, it took a long time and there are a lot of pieces. Would collecting a large table into a collection really work? I'm guessing I would have to patch the data back to SQL so that I could work with it in other programs. I'm also concerned with performance, but maybe it would work just fine.

Ahhh, I think you may be affected by the same thing (I think it's a bug) as I described here:-

 

https://powerusers.microsoft.com/t5/General-Discussion/Dynamic-changes-to-gallery-Items-property-not...

 

Basically on your first attempt to change the filter set on a gallery, nothing happens. On a second attempt it works.

Yes, that does sound like the same problem. And, I'm also assuming it must be a bug of some sort. Your workaround sounds like a good solution. I'm not sure what I could use though.

The problem in your case is 2 galleries won't help, you have an infinite number of things people might search for.

 

I wonder if you could somehow 'double hop' the change in the filter/search, whereby you effectively make every change twice.

 

Something I often do when a global variable doesn't seem to dynamically update when it should is to create a hidden label with the value of the global variable, and then reference the Text property of the label, which is always dynamically updated.

 

I tried that with my gallery filter and it still didn't help. I wonder if you could do something different with an OnChange, but you'd have to be careful not to create a circular action.

 

The other thing is, this could be a temporary glitch that's gone by the next build, and any time you invest in creating a clever workaround, while good experience, might end up having been for nothing!

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (2,871)