Answered

Search only selected part of data source

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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.

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

Like
a
(0)

Report
Copy link

Link copied!

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

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

Like
a
(0)

Report
Copy link

Link copied!

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

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

Like
a
(0)

Report
Copy link

Link copied!

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.

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

Like
a
(0)

Report
Copy link

Link copied!

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.

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

Like
a
(0)

Report
Copy link

Link copied!

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

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

Like
a
(0)

Report
Copy link

Link copied!

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?

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

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No