Unanswered

Search and Filter a Sharepoint list

(1) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi all,

I have a Sharepoint List with a couple of Choice (dropdown) columns.

In the powerapp, I have the sharepoint list as a data connection with a gallery that should display all items. I have added a text input search bar which allows a user to search by Title. This works perfectly.

Search('SharepointListName', SearchBarName.Text, "Title")

I'd also like to add a filtering option for users via a dropdown that filters one, ideally two of the choices in the sharepoint list. So far, I've got a form that is successfully connected to the correct Sharepoint Choice. The problem I'm having is I can't get the get the Gallery to filter using the form dropdown and search using the text input.... actually, the new code is absolutely broken.

Sort('SharepointListName',(Search(, SearchBarName.Text, "Title") Filter(DataCardValue51, "SharepointChoiceName"))

Appologies for the coding nightmare above, as you might be able to tell, it's not one of my strengths. Can anyone help me to get this working please?

Categories:

Building Power Apps

I have the same question (0)

All responses (19)

Answers (0)

Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
try something like this:

Search(Filter('SharepointListName', DropDownField.Selected.Value = SharePointChoiceField.Selected.Value), SearchBarName.Text, "Title")
You will need to change the data source and field names to what your app is using. The formula should filter the list to items that match the selected drop down value and then if there is text in the search box it will filter the list down further.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi Jeff,

Here's what I'm using on the Items value for the Gallery:

Search(Filter('Fixed Assets Register', BrowseFilterForm.Selected.Value = Site_x0020_Location.Selected.Value), BrowseSearchBar.Text, "Title")

But I'm getting the blue exlamation mark with following message:

Suggestion: Part of this Search formula cannot be evaluated remotely due to service limitations. The local evaluation may produce suboptimal or partial results. If possible, please simplify the formula. For more information, please see the Search function documentation.
Any ideas?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Search() is not a delegateable function in Sharepoint lists. Instead, use Filter('SharepointListName',StartsWith(SearchBarName.Text, "Title") . This will enable you to search lists larger than 2000 items.

Was this reply helpful? Yes No
tianaranjo 497 on at

Like (0)

Report
Copy link

Link copied!

@Anonymous - were you able to get this to work? I have tried same method on an Indexed SP list with greater than 33k items with no luck. If it works for you -- what fx did you apply?

Thank you.

Was this reply helpful? Yes No
Shanescows 2,218 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I had a customer with a similar issue. In the end, we had to find ways to get the data in smaller chunks and not use the Search function. Sorry, it is not delegatable so you can't use it with large lists.

Was this reply helpful? Yes No
tianaranjo 497 on at

Like (0)

Report
Copy link

Link copied!

@Shanescows Thank you. I have been scouring for solutions and was coming to the same conclusion. When you say you brought the data in in small chunks, did you find that affected the performance when the PowerApps was opened?

Was this reply helpful? Yes No
Shanescows 2,218 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

No. What we did was had the screens like such:

Projects > Office Buildings > Floors in building > List of items to install on the floor

You could not search/filter or much of anything until you got to the floor.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

To hell with it, I gave up in the end and took a complete different approach to the project without using powerapps. Nevermind.

Was this reply helpful? Yes No
terrbo85 2 on at

Like (0)

Report
Copy link

Link copied!

I have a sharepoint list that I am trying to use the search function and its working but will only allow me to search for the title on the browse gallery page. I want the user to be able to search by using keywords from the info in the columns of the list. This works perfectly when I am using a excel table as the datasource but not with the sharepoint list.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

adding in @emmac to review.

@Anonymous

Was this reply helpful? Yes No