Search Box while Filtering SharePoint List on two Drop Downs

[llacefield]

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!

[Pstork1]

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)

[llacefield]

@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?

[v-xida-msft]

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,

[Pstork1]

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.

[llacefield]

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. 

[llacefield]

@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", 

[Pstork1]

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") 

[llacefield]

@Pstork1 

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

[Pstork1]

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.