Search only working some of the time

[AAK1]

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")

[davidstone]

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

[AAK1]

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.

[davidstone]

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.

[AAK1]

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]

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

[AAK1]

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.

[davidstone]

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.

[AAK1]

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.

[davidstone]

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!