Filtering gallery on text input and dropdown

[kriggo15]

I need some assistance with the filtering of my gallery. I have a sample dataset containing car data. I want the gallery to be able to be filtered based on any of the following:

TextInputBox - searches across Manufacturer, Make, and Model
drpMfr - contains all Manufacturers by default
drpMake - contains all Makes by default unless a Manufacturer is chosen then it is filtered
drpModel - contains all Models by default unless a Manufacturer or Make is chosen then it is filtered

Couple areas I'm having issues:
1) Filtering the gallery to show everything by default or filtering based on any of the above controls.
2) The values in the dropdowns should be dynamic. If I type "General Motors" in the textbox, the gallery should filter and only Makes/Models that are part of General Motors should show up in drpMake and drpModel.

[Drrickryp]

Hi @kriggo15 

Watch Shane Young's @Shanescows  video to see how to do that you are requesting. https://www.youtube.com/watch?v=kLGglidmPxg 

[kriggo15]

Thanks @Drrickryp . I really like Shane's videos and hadn't seen this one yet. The video helped with part of my first question but didn't address the second.

How do I use the search input to filter down the dropdowns? I assume I'm going to have to add a condition where that textinput.text is in a specific field?

[yashag2255]

Hi @kriggo15 

To address the second part, you can apply the filters like:

Manufacture : Items -> If(IsBlank(TextInput1.Text),ManufactureList, Filter(ManufactureList,StartsWith(ColumnName,TextInput1.Text)))

drpMake: Items -> If(IsBlank(TextInput1.Text),Filter(Makers,Manufacture = drpManufacture.Selected.ColumnName), Filter(Filter(Makers,Manufacture = drpManufacture.Selected.ColumnName),StartsWith(Result,TextInput1.Text)))

and so on.

Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Drrickryp]

@kriggo15 

It sounds like you need  "Cascading dropdowns ".  Shane has a video about that too.  https://powerusers.microsoft.com/t5/Webinars-and-Video-Gallery/PowerApps-Cascading-Dropdown/m-p/9281... 

[v-xida-msft]

Hi @kriggo15 ,

Could you please share a bit more about your scenario?

Do you want to filter your Gallery based on any combination of Text Input box and several Dropdown filters?

Further, could you please share a bit more about your data source? Is it a SP List?

Based on the needs that you mentioned, I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the Gallery to following:

Filter(
       'Your SP List',
       If(
          IsBlank(drpMfr.Selected.Value),
          true,
          Manufacturer.Value = drpMfr.Selected.Value
       ),
       If(
          IsBlank(drpMake.Selected.Value),
          true,
          Make.Value = drpMake.Selected.Value
       ),
       If(
          IsBlank(drpModel.Selected.Value),
          true,
          Model.Value = drpModel.Selected.Value
       ),
       If(
          IsBlank(TextInputBox.Text),
          true,
          TextInputBox.Text in Manufacturer.Value || TextInputBox.Text in Make.Value ||  TextInputBox.Text in Model.Value
       )
)

Note: I assume that your data source is a SP List, and the Manufacturer, Make, and Model are all Choice type columns in your SP List.

If the Manufacturer, Make, and Model are all Text type column in your data source, please modify above formula as below:

Search(
    Filter(
       'Your SP List',
       If(
          IsBlank(drpMfr.Selected.Value),
          true,
          Manufacturer = drpMfr.Selected.Value
       ),
       If(
          IsBlank(drpMake.Selected.Value),
          true,
          Make = drpMake.Selected.Value
       ),
       If(
          IsBlank(drpModel.Selected.Value),
          true,
          Model = drpModel.Selected.Value
       )
    ),
    TextInputBox.Text,
    "Manufacturer",
    "Make",
    "Model"
)

Please take a try with above solution, check if the issue is solved.

Best regards,

[kriggo15]

@v-xida-msft , thanks for the reply.

The dataset is actually SQL and is over 10K records. I'm using the auto data as dummy data to represent my actual dataset. I'm running into issues with delegation as well because I need to get a distinct list of Manufacturers, Makes, etc for each of the dropdowns and it isn't checking all of the records due to the size and I'm not sure the best way to get around that. 

[v-xida-msft · Accepted solution]

Hi @kriggo15 ,

Based on the issue that you mentioned, I think you have faced Delegation issue with your formula.

If you faced Delegation issue with your formula, it means that you could only process 2000 records locally at most from your data source within your app.

Please use Delegable function within your canvas app to process more records from your data source. More details about Delegable functions supported in SQL Server connector, please check the following article:

https://docs.microsoft.com/en-us/connectors/sql/#power-apps-delegable-functions-and-operations-for-s...

Based on the issue that you mentioned, I think a collection could achieve your needs. You could consider bulk-load your SQL Table records into a collection in your app, then use the collection as data source instead of your original SQL Table data source. Then you would get rid of this Delegation warning issue.

Please check and see if the following solution would help in your scenario:

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

Best regards,