cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
llacefield
Level: Powered On

Search Box while Filtering SharePoint List on two Drop Downs

I have a large PowerApp that I'm in the process of building, so far it is coming along well but I am running into an issue with the search text box. The allery is filtering the selection of what the user selects in the drop downs, Category and  Group. The Group Drop down is hidden until a selection in Category is made first, then the filtering process begins. I approached this obstacle that way becuase the SharePoint List that the gallery pulls from is over 1000 records and was wanting the app to perform well without being sluggish and slow. With the SharePoint List being sliced and diced with the two drop down selections, I know the end users will want the functionality to search but I can't seem to figure that piece of code out!

Any help would be greatly appreciated!

14 REPLIES 14
Dual Super User
Dual Super User

Re: Search Box while Filtering SharePoint List on two Drop Downs

Since this works in stages you will probably have to apply your filter in layers using If statements.  Something like this:

 

If(isBlank(dpr_Category.SelectedText.Value),tblEquipment,

    IF(isBlank(dpr_Group.SelectedText.Value),Filter(tblEquipment,drp_Category.SelectedText.Value = Category),

   Filter(tblEquipment,drp_Category.SelectedText.Value = Category && drp_Group.SelectedText.Value = Group)

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
llacefield
Level: Powered On

Re: Search Box while Filtering SharePoint List on two Drop Downs

@Pstork1 

Looks great, this appears to be better coding practice than the the current code I have. My concern is how to incorporate the Search function in with that code you provided. My hope are if the use is unaware of the category and group of a tool, that they can utilize the Search Bar on the left. When trying to utilize the Search function, I continuously am getting a delegation warning.

IS there a way a around the delegation, if not what is the harm of having it there. Performance?

Community Support Team
Community Support Team

Re: Search Box while Filtering SharePoint List on two Drop Downs

Hi @llacefield ,

Could you please share a bit more about your scenario?

Do you want to combine the Search Text box with your Filter formula in your app?

 

I have made a test on my side, I think the nested Search function could achieve your needs. Please consider take a try with the following workaround:

Set the Items property of the Gallery to following formula:

Filter(
         Search(tblEquipment, TextSearchBox1.Text, "SearchColumn1", "SearchColumn2", "SearchColumn3", ...),
If(
IsBlank(dpr_Category.SelectedText.Value), true,
IsBlank(dpr_Group.SelectedText.Value), Category = drp_Category.SelectedText.Value,
Category = drp_Category.SelectedText.Value && Group = drp_Group.SelectedText.Value
) )

Note: The "SearchColumn1", "SearchColumn2", "SearchColumn3", ... represents the Text type columns in your SP list. Currently, the Search function is only supported to use Text type columns as Search column.

More details about Search function, please check the following article:

Search function

 

In addition, if you faced a Delegation warning issue with your formula, it means that you could not delegate the data process to your SP list data source, you could only process data locally within your app. In default, you could only process 500 records locally at most. You could change the limit to the maximum value -- 2000, please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#changing-the-limit

 

If the amount of your SP list records is not more than 2000 (you have already changed the "Data row limits for Non-delegable queries" option to maximum value -- 2000), you could ignore this Delegarion warning issue.

If the amount of your SP list records is more than 2000, as an alternative solution, please check and see if the solution mentioned within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

 

Best regards,

 

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.
Dual Super User
Dual Super User

Re: Search Box while Filtering SharePoint List on two Drop Downs

What function are you using as part of your Search function?  That may be where the delegation warning is coming from.  This is particularly common if you try to filter on a date since those columns are non-delegable.

 

The harm of delegation is that you aren't guaranteed to get all the records if your list is bigger than the maximum size (default 500).  If its a small list you can ignore the delegation warning.  If itcould become a list bigger than 500 then you need to fix the Search formula so its delegable.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
llacefield
Level: Powered On

Re: Search Box while Filtering SharePoint List on two Drop Downs

Hi @v-xida-msft ,

Yes, that is exactly what I'm trying to achieve. If the user starts the app and is unaware of how to the tool they need to select and add to the cart, they can utilize the search textbox on the left wihtout having to filter by using the drop downs. The column on SharePoint that it would be searching through is a Text field but when utilizing the search box with the code provided gives no results.

I utilized the drop downs in an effort to increase the performance of the app and delegate the work to SharePoint rather then PowerApps to filter and search through a 1000+ record list of tools on SharePoint. If there is a better way to do that, than please let me know. 

Highlighted
llacefield
Level: Powered On

Re: Search Box while Filtering SharePoint List on two Drop Downs

@Pstork1 Thanks for th reply.

The function in my head would be something like this,

If(IsBlank(drp_Category.SelectedText.Value), If(IsBlank(drp_Group.SelectedText.Value), Filter(tblEquipment,drp_Category.SelectedText.Value = Category && drp_Group.SelectedText.Value = Group), Search(tblEquipment, txt_Search.Text, "Descritpion") 

But ideally I'd like to see a nested If statement saying, If(IsBlank(drp_Category.SelectedText.Value & drp_Group.SelectedText.Value, Search(txt_Search.Text, "Description", Filter(tblEquipment, drp_Category.SelectedText.Value = Category && drp_Group.SelectedText.Value = Group))

(Forgive me for the novice level of coding here. Looking at that should give you guys a good chuckle.)

 

I see, than the delegation could be a severe issue as this apps purpose is for user to "Purchase" small toolls and consumable material. If the delagation affected the ability of it to pull all the records then it would appear to the user that those tools aren't on the list to "purchase", 

Dual Super User
Dual Super User

Re: Search Box while Filtering SharePoint List on two Drop Downs

First, the way you've got it written search would only apply if the user hasn't selected anything from the dropdowns.  I think it would be better to treat the full IF() statement as the datasource and put the Search on the outside of the whole thing.  That could alleviate some of the search delegation issues since the IF would pre-filter the list in most cases and the search would be applied to the result. so it would be something like this:

Search(

    If(isBlank(dpr_Category.SelectedText.Value),tblEquipment,

        IF(isBlank(dpr_Group.SelectedText.Value),Filter(tblEquipment,drp_Category.SelectedText.Value = Category),

       Filter(tblEquipment,drp_Category.SelectedText.Value = Category && drp_Group.SelectedText.Value = Group),

txt_Search.Text, "Descritpion") 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
llacefield
Level: Powered On

Re: Search Box while Filtering SharePoint List on two Drop Downs

CodeError.jpeg

@Pstork1 

with the code supplied, I'm getting the above error? Strange.

Dual Super User
Dual Super User

Re: Search Box while Filtering SharePoint List on two Drop Downs

The error would suggest that I misplaced one of the closing parentheses.  I would turn on formatting of the text and trace through where the Parantheses break things up.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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
Users Online
Currently online: 244 members 6,362 guests
Please welcome our newest community members: