cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
msawhney
Level: Power Up

Modifiyng a "filtered" Search function

I am trying to create a search function for our contact list PowerApp based on a SharePoint List. The default function for filtering is:

 

 SortByColumns(Filter('Industry Contacts', StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

 

I want to modify it to search all columns if they contain the search letters at all. Not just start with.

 

ie. I am looking at the picture below on microsoft's page and trying to replicate it.

 

 

I have messed around with replacing my filter with this statement but keep getting errors.

 

 

Filter('Industry Contacts',TextSearchBox1.Text in FirstName)) 

 

Can someone point me in hte right direction?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Modifiyng a "filtered" Search function

Hi @msawhney,

 

Do you get a delegation warning with the formula that you provided?

 

I suppose that you have gotten a delegation warning with the formula that you provided, is it true? 

 

I think you have faced a Non-delegable issue with the formula that you provided, because the in operator could not be delegated within SP list data source.

 

More details about the delegable data sources , functions and operators in PowerApps, please check the following article:

Delegable data sources

 

If you have faced Non-delegale issue with formula within your app, it means that the data process could not delegated to the data source (On your side, it is SP list), you could only process your data locally. In addition, you could only process 500 records (Non-delegable limits) locally at most in default.

 

More details about the Delegation in PowerApps, please check the following article:

Delegation in PowerApps

 

Currently, you could consider change the Non-delegable limits (Data row limit for non-delegable queries), the maximum value of the Non-delegable limits is 2000 currently within PowerApps. More details about changing the Non-delegable limits, please here.

Test1.gif

 

 

If you want to avoid the Delegation issue with your formula, you could consider take a try to save your SP list into a Collection, and then use the collection as the data source instead of the SP list within your formula.

 

Please take a try with the following workaround:

 

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

ClearCollect(ContactsCollection,'Industry Contacts')

Set the Items property of the Gallery control to following formula:

SortByColumns(
    Filter(
ContactsCollection,
TextSearchBox1.Text in FirstName ||
TextSearchBox1.Text in Column2 ||
TextSearchBox1.Text in Column3 ||
...
), "Title", If(SortDescending1, Descending, 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.
2 REPLIES 2
Super User
Super User

Re: Modifiyng a "filtered" Search function

Seach() is non-delegatable in Sharepoint lists and creates error messages if the list is your datasource. However, Search() works on collections so if your list is < 2000 items, you can create a collection from it and search that.  On a typical Browse Screen, Set the Screen's OnVisible property to ClearCollect(indcon,'Industry Contacts') // collects your contacts into a collection called indcon.

 

then set the items property in your gallery to:

SortByColumns(Search(indcon,TextSearchBox1.Text, "Title", "Company", "etc"), "Title", If(SortDescending1, Descending, Ascending)).  // you can use as many columns as you want to search as long as they are text columns.

 

BTW set the datarow limit in the advanced settings of your app to 2000.   Hope that helps.

 

Community Support Team
Community Support Team

Re: Modifiyng a "filtered" Search function

Hi @msawhney,

 

Do you get a delegation warning with the formula that you provided?

 

I suppose that you have gotten a delegation warning with the formula that you provided, is it true? 

 

I think you have faced a Non-delegable issue with the formula that you provided, because the in operator could not be delegated within SP list data source.

 

More details about the delegable data sources , functions and operators in PowerApps, please check the following article:

Delegable data sources

 

If you have faced Non-delegale issue with formula within your app, it means that the data process could not delegated to the data source (On your side, it is SP list), you could only process your data locally. In addition, you could only process 500 records (Non-delegable limits) locally at most in default.

 

More details about the Delegation in PowerApps, please check the following article:

Delegation in PowerApps

 

Currently, you could consider change the Non-delegable limits (Data row limit for non-delegable queries), the maximum value of the Non-delegable limits is 2000 currently within PowerApps. More details about changing the Non-delegable limits, please here.

Test1.gif

 

 

If you want to avoid the Delegation issue with your formula, you could consider take a try to save your SP list into a Collection, and then use the collection as the data source instead of the SP list within your formula.

 

Please take a try with the following workaround:

 

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

ClearCollect(ContactsCollection,'Industry Contacts')

Set the Items property of the Gallery control to following formula:

SortByColumns(
    Filter(
ContactsCollection,
TextSearchBox1.Text in FirstName ||
TextSearchBox1.Text in Column2 ||
TextSearchBox1.Text in Column3 ||
...
), "Title", If(SortDescending1, Descending, 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.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 269 members 3,461 guests
Please welcome our newest community members: