Trouble with Gallery search, generates a blank list for a simple search

[RAOV]

Thank you all for your time to look at my question.

I am new to PowerApps and have made it through to the end of developing an application when I ran into a very unexpected problem.  The searchbox in my gallery causes the gallery items to disappear when I enter a search string of any search value into it.  So in trying to find ‘test’, I enter ‘test’ and get this:

The name of the Sharepoint column that I want to search by is ‘Risk ID’.  So, I duplicated the field and named it ‘RiskID’ in the SP table, then set it in the Gallery and hid it, the Search formula was altered to so I could use that field to search.  Now, the records that were there when I made the change can be searched but any new records I create cannot be searched.  That is, if I use the names of any new records then the Gallery goes blank, again.

No errors are generated.

Here are the columns in my sharepoint List:

Here is the formula I’m using:

SortByColumns(Filter([@SWG_Risk_Register], StartsWith('Risk ID', TextSearchBox1.Text)), "Title",            If(SortDescending1, Descending, Ascending))

I have tried to change ‘Risk ID’ to ‘RiskID’ (no quotes) as described.

I have checked the table name settings, spelling and anything else I could think of.

I hope I have been clear.  Please let me know your questions.

[v-qiaqi-msft]

Hi@RAOV,

Based on the issue that you mentioned, do you want to filter the Gallery when you enter a search string of any search value into the search bar?

Let me make it easy for you.

• StartsWith function only used to test whether one text string begins with another. If you want to filter the items by entering any letter in a word, the Search function works instead
• StartsWith( Text, StartText ) In this formula, the column is a Text type by default. If you have any other types, please make them matched.

I saw that you enter "test" in the search bar in order to filter the "TestDeepLink" which cannot work using StartsWith function.

I have a test on my side, please take a try as below.

SortByColumns(Search([@SWG_Risk_Register],TextSearchBox1.Text,"RiskID")),"Title",If(SortDescending1, Descending, Ascending))

 Best Regards,

Qi

[RAOV]

 Thanks very much for your reply Qi.  I tried your formula and it should have worked, I think.  But, I get this error, suggesting that the field cannot be detected by the query:

I tried to see if I could clear cache? (not sure if that is the right move) But I don't have Admin access.  I don't understand why it does not recognize that column.  The same response came when I used the "Risk ID" column.  This may be an error at the table level.?

Thank you for your time.

[v-qiaqi-msft]

Hi@RAOV,

"So, I duplicated the field and named it ‘RiskID’ in the SP table, then set it in the Gallery and hid it." What does the 'hid it' mean, is this 'hide it'?

I have made a test on my side which goes well, could you please share the formula you put into the Items property of the Gallery?

Best Regards,

Qi

[RAOV]

Hi Qi,

Yes, by "hid it" I did mean "hide it".  In answer to your question, the formula I'm using in Items is:

SortByColumns(Filter([@RiskRegister], StartsWith(RiskID,TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

Thanks for your help,

Rao

[v-qiaqi-msft]

Hi@RAOV,

Actually there is something syntax wrong with your formula.

This is a formula I provided below. 

SortByColumns(Search([@SWG_Risk_Register],TextSearchBox1.Text,"RiskID")),"Title",If(SortDescending1, Descending, Ascending))

This is a formula you put in your Items property below.

SortByColumns(Filter([@RiskRegister], StartsWith(RiskID,TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

I have a few explanations as to why the Search function is used instead of the StartsWith function.

• StartsWith function only used to test whether one text string begins with another. If you want to filter the items by entering any letter in a word, the Search function works instead

I saw that you enter "test" in the search bar in order to filter the "TestDeepLink" which cannot work using StartsWith function.

Please try the formula I provided one more time.

SortByColumns(Search([@SWG_Risk_Register],TextSearchBox1.Text,"RiskID")),"Title",If(SortDescending1, Descending, Ascending))

Best Regards,

Qi

[RAOV]

Thank you for taking the time to help me with this Qi.  I appreciate your diligence with the issue.

This worked for me. Although it does say that I should not have more than 500 items in the record.  I will use this and let the customer know that I should be notified if his database becomes really large.

Best Wishes,

Rao

[Drrickryp]

@RAOV 

FYI In the File > Settings > Advanced settings  you can increase the Data row item limit from 500 (default) to 2000 in your app without a measurable decrease in performance. 

[v-qiaqi-msft]

Hi@RAOV,

If you worry about the delegation issue, you can increase the total limit items you can fetch. @Drrickryp has provided the answer, I just do some addition.

Click file (top left corner) > App settings > Advanced settings > set value for non-delegable queries.

This method has one hard limit (limitation of the limitation) – 2000 is a maximum value you can set in PowerApps.

If it solved your issue consider Accept it as the solution to help the other members find it more.

Best Regards,

Qi

[RAOV · Accepted solution]

Yes, I was concerned about the delegation issue.  But your solution will help alleviate that.

Thanks everyone.