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.

View solution in original post

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.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,479)