Search only selected part of data source

[Anonymous]

Hi,

I am very new to Power Apps.  Trying to create an app that allows users to view thousands of indiviual items of inventory.

PA has created a suggested layout and I have added extra fields to it.  I have entered the below formula to allow for user to search by a particlaur data column.

SortByColumns(Search(Table1, TextSearchBox1.Text, "MAKE"), "MAKE", If(SortDescending1, Descending, Ascending))

This works, however, I have relised that the app is only displaying a small number of the thousands of inventory lines on the data source.  Is this beacuse it can only show an limited amount of lines? Can this be changed?

If not I will have to create separate windows, one for MAKE with specific value selected which can then be filtered down into MODL with specific value selected, which can then be filtered down to TRIM with specific value selected.  

I want the user to be able to drill down to find details specific inventory.

Really appreciate any advise, I have been dreaming about creating this type of App for years and with Power Apps I am very close,

Thx 

[Anonymous · Accepted solution]

I'm pretty sure this might have to do with this:

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

And how PowerApps behaves with more than 500 records.

[Anonymous]

Thx for the reply. Sorted this issue by switching data source to static excel upload rather than cloud. All info now showing.

The current task I am stuck on is adding drop down filters.

I have a text serach box that allows make of car to be selected but I also want the user to be able to filter down using up to 9 drop down filters.  I have added and named all 9.  Where I am stuck is the formula.  

I currently have the following Items formula that is making the text box filter by Make.  Do you know what I need to add to get the drop down filters working?

Filter(Table1_1, TextSearchBox1.Text in Text(MAKE))

Thx 

[Anonymous]

You could nest your Filter queries for the dropdowns. I tried to post a specific example but it keeps disappearing

[Anonymous]

This could be accomplished with nesting Filter values.

Bear with me here. I used a gallery for this example, but you should be able to adapt this to make it work for your uses.

Here is my example with 2 dropdowns and some sample data from my sample data source

Now for the code:

DropDown1:

OnChange value set to:

UpdateContext({variable1: Dropdown1.Selected.Value})

DropDown2:

OnChange value set to:

UpdateContext({variable2: Dropdown2.Selected.Value})

Gallery:

Items value set to: If(IsBlank(variable1), If(IsBlank(variable2), sampleData, Filter(sampleData, Column2=variable2)), If(IsBlank(variable2), Filter(sampleData, Column1=variable1), Filter(Filter(sampleData, Column2=variable2), Column1=variable1)))

This above formula is taking into account that the Dropdown value can be set to a null value (how the dropdowns are blank in the picture). If you would rather have an "All" option just replace IsBlank(variable1) and IsBlank(variable2) with variable1="All" and variable2="All". Keep in mind this only 2 dropdowns (you said you wanted 9) and the code will get lengthy with 9, but this kind of methodology should work.

I am unsure if anyone knows of a short cut for doing this, but I can verify that I have tested this and it does work.

[Anonymous]

Try one more time.

This could be accomplished with nesting Filter values.

Bear with me here. I used a gallery for this example, but you should be able to adapt this to make it work for your uses.

Here is my example with 2 dropdowns and some sample data from my sample data source

Now for the code:

DropDown1:

OnChange value set to:

UpdateContext({variable1: Dropdown1.Selected.Value})

DropDown2:

OnChange value set to:

UpdateContext({variable2: Dropdown2.Selected.Value})

Gallery:

Items value set to: If(IsBlank(variable1), If(IsBlank(variable2), sampleData, Filter(sampleData, Column2=variable2)), If(IsBlank(variable2), Filter(sampleData, Column1=variable1), Filter(Filter(sampleData, Column2=variable2), Column1=variable1)))

This above formula is taking into account that the Dropdown value can be set to a null value (how the dropdowns are blank in the picture). If you would rather have an "All" option just replace IsBlank(variable1) and IsBlank(variable2) with variable1="All" and variable2="All". Keep in mind this only 2 dropdowns (you said you wanted 9) and the code will get lengthy with 9, but this kind of methodology should work.

I am unsure if anyone knows of a short cut for doing this, but I can verify that I have tested this and it does work.

[Anonymous]

Thanks for your help, yesterday was the first time I had ever seen Power Apps so apologies for the silly questions.

I have added all on change values,

In the value section of the gallery I already have the following formula:  

Filter(Table1_1, TextSearchBox1.Text in Text(MAKE))

How do I add incorprate addition formula you provided.

I have actually got 10 drop down fields plus a text search box.  I want the user to be able to filter the make of car using text box then further filter using the 10 drop down options.  So they could type Ford and then use drop down to filter to fiesta and then trim and so on.

my data source is called: Table1_1 

and my drop down box names are within the formulas added to the change value.

UpdateContext({variable1: DropdownMODEL.Selected.Value})

UpdateContext({variable2: DropdownTRIM.Selected.Value})

UpdateContext({variable3: DropdownSERIES.Selected.Value})

UpdateContext({variable4: DropdownTYPE.Selected.Value})

UpdateContext({variable5: DropdownGP.Selected.Value})

UpdateContext({variable6: DropdownYRP.Selected.Value})

UpdateContext({variable7: DropdownGB.Selected.Value})

UpdateContext({variable8: DropdownF.Selected.Value})

UpdateContext({variable9: DropdownEXTC.Selected.Value})

UpdateContext({variable9: DropdownDA.Selected.Value})

Really appreciate your help.  If I can get this to work it will help so many people,

Thx 

[Anonymous]

Just so I understand correctly, are you wanting to base the Model Dropdown off the Make and then all the other dropdowns off the Make and Model? Or are they each based on all the dropdowns that preceed it?

[Anonymous]

Sorry I wasn't that clear.  I would like 10 drop downs to work separately from the text search box for make.  This is so that users could filter down for example just diesels of all makes.  The user then might type ford in the text box to see how many of the diesels are Fords and then use a drop down box to see how many where automatic and so on.  I guess the drop downs need to work like they do in Excel.  Thx