PowerApps filter gallery by dropdown
Вставка
- Опубліковано 6 сер 2024
- In this video, you will learn how to filter your gallery using one dropdown, two dropdowns, and a search box. Lots of fun to be learned here. We also cover the distinct function and the collect function along the way.
Power Apps training at training.PowerApps911.com - Наука та технологія
Shane, Love your videos. To the point and with some humor. Learned so much from your work. Thanks!
Thanks Martin. 😎
Thank you Shane, for giving people who have no idea what they are doing the opportunity to impress other people that have no idea what they are doing! Making me look like a rock start!
Awesome Peter! 🤩
Random comment but I find it funny that every PowerApps teacher on UA-cam always says: "But first, here's the intro!". Its the only time time I've every seen someone introduce an introduction.
I didn’t know anyone did it besides me? 100+ videos of doing it most of my viewers expect it. 🐶
Thanks Shane for another great tutorial. I'm developing a big app with power apps using your videos.
Awesome
Awesome stuff Shane!!! I couldn't finish this app without all of your help!
Glad it helps Eric!
Thank you for hard work you´re has been doing to the rest of the mortals.
From two week´s ago, you´re my "Master of Powerapps" (like He-Man was of the universe!).
Hugs from Córdoba, Argentine to Cincinnatti.
Thanks Diego. I loved that cartoon🐶
Awesome Shane, thanks so much for sharing. I've just used this to add filtering to a form in Project Oakdale.
Nice Job Alex.
Thank you Shane! This was very helpful!!! I appreciate all you do.
Glad it was helpful!
Thank you for leaving the moments where you have no idea what's going on...I think we spend most of our time in PowerApps in that state! It helps a lot 😊
You’re welcome. Several people have told me that my issues help them connect the dots. 😎
Oh yes... I totally agree!!!
Excellent work as usual, Shane!!
Thanks!
Thank you Shane for sharing this link. You rock and I have learned a lot from your videos.
My pleasure!
Thanks so much! Your channel is my usual go-to for power apps and related. All excellent videos.
Great to hear!
Thanks a lot🤝, much needed video indeed. Keep sharing your wisdom!!
My pleasure 😊
Thank you so much. Had everything I needed :)
Glad it helped!
Thank you, i learned a lot, there maybe some new techniques but i keep on watching your other Videos based on filtering and make my notes. It took me 90min to write down everything and combine that with screenshots. I have a very good docu to your video which i put on my favorite list.
Happy to help!
Searched all afternoon for how to do this. Thanks for the great video!
Glad I could help!
Thank you so much Shane, this just saved me hours of figuring out the filter.
Glad to help. 😀
Thank you Shane! I understand the logic behind all this! Thank you!
Glad it helped you!
I see you went ski/snowboarding recently! Thanks again for this amazing video!
Glad to help. 😀 Need more sunscreen 😀
Seriously Shane, thank you! Also, my favorite line that I now use of yours “Why are you mad?!”
Another great video Shane. I do this all the time but I have a slightly different approach. I do the two collections to add the All, but then for each dropdown I have a hidden label with the dropdown result based on the IF. If(Dropdown?.Selected.Result = "All","",Dropdown?.Selected.Result )The my items filter uses Startswith() based on the Labels. I have one Gallery with over 25000 records that has three dropdowns and three Text Input fields. Two of the Text Input are ID fields. This approach allows for a filter for the Gallery to be based on one or all of the fields. I use an IF to use the ID Fields in isolation.
Thanks for sharing Mark 😎
Thank you for this, I enjoy practicing on your Vedio and I learn a lot. I appreciate how you follow simple approaches and add functionalities gradually, it makes my practice less overwhelming. thank you so much.
Glad it was helpful!
Brilliant as always. This is exactly the solution I needed
Awesome sauce!
@@ShanesCows I ran into one small problem since I added the dropdowns. When a user filters the items and then clicks and goes to the details screen and comes back, the text search is still there but the dropdown's are back to All. Would I need to set a variable and pass it in as the default value or is there a simple tip. Or maybe I set something wrong somewhere when creating?
@@ShanesCows I just seen the solution further down in the comments by Joseph.
This is so awesome. Takes a while to wrap the head around it, but it's beautiful. But lots and lots of dropdown filters is complicated.
I have a better way now 🤩 ua-cam.com/video/Fstk03c9dcg/v-deo.html
Thank you for sharing ... God bless you man!
Glad it helped you. 😎
Wow wow wow.... finally I prepared my own application with the help of your video... Thanks, Much bro.. Had so many difficulties in between, but I was able to resolve though...
Thank You Shane! It was very useful for me :)
Thanks Shane... very nice video and fun to watch
Glad you enjoyed it
I am building out the option for 3 dropdowns and has seemed very straightforward. It was mentioned at about 23:09 of the video about needing 9 combinations. I can only find 8 combinations and so was inquiring what combination I might be missing. I am having trouble with all data returning for the "All", "All", "All" option, but all other scenarios work. What I have is a label that's doing a SUM of the gallery for a distinct column of data, and so the SUM is not gathering all of the data in the gallery of this column which has been my test for each of the scenarios to make sure it's working.
Your videos lead to great learning :)
Glad you like them!
This is gold, really liked this video, really helped me alot. Keep up the great content.
Thanks. You might like this later video that shows and even easier way to do it. :) ua-cam.com/video/Fstk03c9dcg/v-deo.html
I hit on the like and follow button on duration 22:52. You made it is just not a tutorial, it's you know the student reaction is when he can made it by himself.
Thank you, glad it helped. 😎
thx mr. shane it is exactly what i looking for :)
🙏
Awesome video Shane!
Thanks!
Shane this has helped me so much thank you !!
Happy to help. :)
Thanks mate, Great video!
Glad to help. 😀
Thanku so much Shane, for all your powerapps tutorial
My pleasure!
Great video, Shane. I have (almost) exactly this situation in my app, except a toggle instead of the 2nd drop down. I built the app a few months ago by piecing together content from your other videos and (gasp!) Microsoft’s documentation. It works pretty well, but I look forward to getting back into it to clean it up with the nuggets in this video. Thanks,
Glad to help Jim
Hey Shane, great video once again and I was keen to see this one as I am currently working on a gallery that I want to give users the ability to filter in a number of ways. I would love to know the steps of creating a gallery that is filtered with a combination of dropdowns and a combobox and possibly search as well. So essentially you have two dropdowns where you filter by fields when you only want to select a single record, but then use a combobox for a field where you want to see items relating to multiple records. As an example based on this video, imagine you had a company that had multiple work sites. Here you not only want to find out what an employees favourite colour was and which department they worked in, but you also wanted to know which work site they belonged to. So you could quickly see all the employees in the IT department at say work sites A&B (and not sites C&D) and see what their favourite colours were.....Hope that makes sense
It does. I will have to think about a future more complex video. The In function will help you in the meantime
You are a life-saver!
Happy to help
Great as always. Thank you for your job from Ukraine!!!! ))
Happy to help
Just what I was looking for taking my first baby steps in PowerApps
Glad to help. 😀
Awesome and very helpful video!! (You got another sub)
Thanks a million! 🐶
Great video Shane! Would love to see how to create a filter experience for users that allows the user to filter their gallery data using 3+ columns. Right now the approach I would use for this scenario uses a lot of IF statements and the logic gets a bit unwieldy. I'm not sure I want to go there.
One day I will cover it. One day. 😑 So many videos I need to do. 😀
Thank you so much for this! You are my favorite person on UA-cam. I've studied this video repeatedly over the past week, and almost have my app functioning perfectly. One problem I'm having is that the collections I built to populate the drop-down menus are wiped when I publish the app (to be clear, it's a connection copied/pasted for each of the drop-downs, then used to build into the "ALL" connections on the Screen's On Visible). Is this because of the ClearConnect function? And if so, how I can fix it? Thank you so much for all of your super helpful videos!!!
LOVED THIS!!!!!!!!!!!!!!!!!
Awesome 😍
Thank you. This is exactly what i was looking for.
Glad it was helpful!
@@ShanesCows Is there a way to use the text searchbox filter and the dropbox filter but change the text searchbox to allow partial search? for instance i previously used this "Filter('NTF Lookup',TextSearchBox1.Text in Title1,"Title")" when only using my searchbox and it would partial text search, but i have been unable to convert this into the format you used at the end of the video for using a searchbox and some dropboxes. My current search that does not allow partial looks like this "Search(Filter('NTF Lookup',Resolved.Value = dropdown1.Selected.Value),TextSearchBox1.Text,"Title")"
now that i am looking at that i think fiddled with it so much the original working search in script looks like it is no longer the same that i had before.
That was brilliant thank you very much Shane and say hello to your dog Chewy :)
Thanks and will do!
Great video!
Thanks
I just want to comment that I had a very hard time with this but it was also due to my own fault, lol. I'm using a region and location from a different source but I'm using the location as the filter. On the OnVisible Property, instead of using Collect for the static record, I had to use another ClearCollect as "All" is already being used in my filter using Distinct. Surprisingly, I didn't have to modify the if statement (after several tries, mind you, lol). Also surprising, I watched this video a little while ago but it didn't HIT me that maybe I should pay closer attention until I decided to tackle the delegation as there's no reason for the icon to show (got that from your other videos). All in all, I was able to successfully add the functions into my app. Anyways, love your videos, they've helped me immensely, and I've shared with fellow app makers your feats. Thanks again!
Glad you got it and thank you for sharing with others so everyone learns. 🐶
I watched timestamp 13:45 to 14:30 on 0.75 speed and it was hilarious. This video saved my butt today. Thank you!
Glad I could entertain 😂
Love your clear and complex videos! Would it be possible to display the number of persons in each position in the company, next to the combobox label? Example: All (78), IT (2), Executive (1)
You are the best! Thanks!
Thanks 😊
I finally managed to do everything you did and more. I am blown away by the way you explain things. Absolutely brilliant. I had difficulties at the start following the steps as my Dropdowns use Complex Columns, and for a newbie like me, it was so hard to figure out. Have your covered relational database like training? Because that will be interesting. Thank you very much.
Maybe this will help? ua-cam.com/video/jmh8eeQVAIc/v-deo.html
Hi I am using a sharepoint dropdown field in my PowerApps Dropdown but it is not able to pick up this sharepoint field (it is picking up other text fields though). How can I solve this? Thanks
i really love your videos just wow
Thanks Shane, I would like you to make a video in which you explain how to create an alert to the mail or with push notifications in which you warn that there is some time to reach a deadline, that is, an alert that notifies an upcoming date, greetings from Chile.
I would use flow for that. Sounds like a great video.
Hi Shane . Problem solved. I had to 'un pack' and fine tune my code further 'bit by bit' ( as you suggested), and eventually I got it right. Thanks for your support and tips . Kind Regards Martin
Good job Martin. I am proud of you for breaking it down. 🐶
Thanks for this. Useful at this date.
Awesome 😎
Hi! Thank you so much for the video .. Would you happen to know how could I sort the available choices within the drop-down field alphabetically?
hi Shane, this is great tutorial, thank you. However, can you advise if you have a video to do the same for larger set of source data? If not using Distinct - how can I filter my data in the Gallery otherwise
Hi Shane, thanks for this tutorial but need some help as im stuck in the collection. When i try to use the CollectVideo to Dropdown Items it doesn't work and shows no data. Hope you could help. Thanks!
Hi, great video! Unfortunately I'm having difficultly right at the beginning. When I add the dropdown and select my datasource (items=my sharepoint list), it appears that none of my "choice" columns from my sharepoint list appear in the "value" for me to select. For example, I have sharepoint columns = status (new, in progress, closed, on hold), column = priority (critical, high, medium, low), etc...but none of these columns appear for me to select to tie to the dropdown. Any idea what I'm missing?
Thanks you, you're the best.
You're welcome!
Hi Shane, Great video as usual!! I built an app from a SPO list and the default Browse gallery "items" has SortByColumns() with Filter(), the sort and filter are both set to "title". Is there a way to change the StartsWith() inside the Filter() to be 2 other columns from my list? I would like to change title in the filter to both "driver" and "Eval Date" Is this possible? The list can grow to thousands of rows, so I have to worry about the delegation issue...
Shane-Thanks so much for making this video. This is exactly what I was looking for! Implemented and tested and works exactly as you have described.
Great to hear!
Wow! Exactly what I was looking for. Thanks Shane. I'm sure Chewy had a hand (paw) in it though. Although, it doesnt work with the OnVisible ClearCollect since my dropdown is based on a choice field 'Category'. Filtering works fine using the first section because you can pull in fieldname.value. Using the ClearCollect OnVisible doesn't seem to offer the 'value' part of the expression. Surprise!!
I have to use Choices('listname'.choicefield) so this doesnt work in the OnVisible property in order to setup for "All" SO, for anyone interested or those that have the same issue, this little variation works:
[OnVisible]
ClearCollect(colCategory, {Value:"All"});
Collect(colCategory, Choices('Grocery Items'.ITEM_x0020_CATEGORY))
[GALLERY ITEMS}
If(ddCategory.Selected.Value = "All", 'Grocery Items', Filter('Grocery Items',ITEM_x0020_CATEGORY.Value = ddCategory.Selected.Value))
Obviously I now have to figure out the sorting part.
Thanks for sharing!
Hi Shane,
I'm using a shorter solution that I hope you'll find interesting.
Here is an example with 3 Dropdowns :
Filter(
Requests,
If(IsBlank(Dropdownstatus.Selected.Value), true,Dropdownstatus.Selected.Value = Status),
If(IsBlank(Dropdowntype.Selected.Value),true,Dropdowntype.Selected.Value = Type),
If(IsBlank(Dropdowncustomer.Selected.cre86_name),true,Dropdowncustomer.Selected.cre86_name = Customer.Name)
)
Thank you for your videos .
I didn't see Derek Shannon used quite the same solution ^^'
Very nice. Thanks for sharing. 😀
Great video , very helpful
Glad to hear it!
love u sir!. i have been fed up i didn't find any solution. here i fix it.
Glad I could help
you are the best!
Thanks 😊
awesome！Love your vide！
Thanks 😊
Very usefull tutorial. Changed my life
Awesome
Hi Shane - this is amazing....
Thank you for always sharing your knowledge with us. CAn you also put some light on using this with SORT function as well.
Thanks
Put SortByColumns around the filter part of the formula.
@@ShanesCows thank you 👍🏾
@@ShanesCows Could you provide the syntax on how to do it? I'm just starting out with PowerApps, and these tutorials are teaching me a lot.
Hi Shane,
Wondering how you were able to pull the employee profile pictures into the gallery. Is there a video available where you cover this?
Thanks in advance!
Great video Shane. I have a quick question though. Could you guide me to a video that runs a flow, taking input from the dropdown of powerapps and based on the selected choice, queries an sql database of multiple tables and displays the (queried) output to a gallery. Any help is appreciated.
Hi Shane: In trying to create a filter, I can't use a field that is "person or group" type from my SharePoint list as items for my dropdown. Under Properties, when I go to look for that 'value', it's not listed as one of the choices from the list of columns from my SharePoint list. What can I do there?
Thank you for all your videos Shane, I've watched so many over the last few months!
I'm not quite clear on one part of it though - the Delegable part.
Will using you method of Distinct, mean that I have to keep my list below 500/2000, or will this solve this problem as the Distinct is outside of the Gallery and it's only getting the values from the dropdown box
Distinct is not delegable 😕
Hey Shane, thanks for all the videos, really getting me up to speed with PowerApp! Question - I have a two column table that I'm trying to apply 3 dropdown filters on to allow the user to keep refining the filter the more dropdowns they apply. Am I on the right track with filter gallery? I've watched some of your other videos (filter multiple selections - Exact or All options - Try again) and I feel like I'm half in one-half in another :). Really appreciate the Channel, Thanks!
Hey Mike I think you are headed the right way. Good luck. 😀
Oh wow, this answered TONS of questions I had - thank you, Shane! My one dilemma is... what if the Search box is looking for numbers, because when a numeric value is entered there, only employees with that same or greater numeric value should be found? I did hear you say, "Search function only works with text columns" ... do I need to adjust the column in Sharepoint from "Currency" to "Single line of text"?
You might have to in order to make search work
Thank you Shane!
Glad to help.
Hi Shane, I implemented the dropdown and text box that filter a gallery. What I try to achieve is that whenever the content of the gallery changes, I want to select the first item. In other words, I want the OnSelect of the first item to be executed. How can I programmatically "click" the gallery's first item in the onchange event of the controls that influence the items selection of the gallery ?
Hi there! Thank you so much for this video! Is there anyway I can do something similar but using the GroupBy función instead of the Distinct one?
Thank you for your video :)
You are welcome 🙏
Hi Shane. I have a dropdown based on a choice column (status for production).
My dropdown items hence is set to: Choices('Production Calendar'.Status) , this returns 3 values. Not Results.
How should I modify the: 'ClearCollect(collectStatus, {Result: "All"}); so that the dropdown list 'All' as well?
Shane, again a great video! I was wondering if the filtering could also be done on collections? I'm using 2 collections, one with the worknumber and one with articlenumbers. I would like the articlenumber collection to show only articlenumbers which depend on the workorder. I can't seem to getr this working. Thanks
Yes everything I show should work exactly the same on collections or any table from any datasource
Thanks Shane !
Happy to help
eres un gran profesor ¡GRACIAS!
De nada 😀
hi shane,
i could see the clearcollect not triggering propely on onvisible of screen.
Any workaround for this
Hi Shane...
Thank you. This was Very Helpful for me !!!
Could we change those Department and Favorite Color dropdowns into Gallery control and based on selection in department gallery show the all favorite colors and employees list in Gallery items... ?
How can we do that ?
Thanks in Advance.
Hey Shane. Thank you for this video. When I add my dropdown box I enter in State which is the column that I want. It is a choice field in my data source. When I type State in the formula box it says: State=There is an error in this formula. Try revising the formula and running it again. Data type: Enum. What does this mean and how do I fix it? I am very new to Power Apps. Thank you.
Awesome as usual! Is there a way to force a user to a screen where you have pre-selected their filtered view by one of those choices that we created? ie. could we take them to review all Employees whose favourite colour was red because we know they are only interested in employees who like red....?
Great content, Shane. Need help on the one type you omitted. Have to filter based on dropdown and date field together. Not much out there on datepicker filtering much less date and D’s selection.
Depends on the data source. Sadly SharePoint has delegation issues with dates. 😑
Thank's alot!!!!!
I think I've watched this video every month for the last 6 months, and get a little more out of it each time. Thank you. I am at the point where I might be so bold as to critique. That last Filter with the "And"... I believe multiple logical-tests in a filter are AND-ed, so you could put the two logical tests separated by commas, rather than combined with an And. Maybe there are delegation or performance ramifications?
You are correct about the , and the And being the same thing. I don’t believe one way or the other affects anything though. I believe they are the same
Great video Shane. I’m assuming you can do the same with Combo boxes for the filters?? Would also like to see the options for more than 3 filters too.
Never tried for a Combobox but I would assume yes. 😊
@@ShanesCows Very nice video shane, been looking for this for quite some time.Thank you very much . Also i have multiple and multiple for filter i wanna do. You showed exampled for 2 dropdown. I have like more than 10 i wanna do. Is there any other alternative than the If, Not ,and . i can do baby steps for all . but just wanna know if theres other ways i can do it with in Powerapps.
@@AdroitZen Same Here! Did you find out a way?
@@ShanesCows For some reason, combo box is not returning "All" value on screen visible. Your solution only applicable for Dropdown boxes.
Hi @Shane ~ tried to use your example but somehow even though my fields do not have single quotation in the excel file that was later converted to a SharePoint List, whenever I try to use the same formula, it was giving errors due to having single quotation on both side of each column (i.e. Column1 changed into 'Column1') ... whenever I tried to remove the single quotation ... well the red line was all over the forrnula. Thank you!
When I use the first drop down box, and build the function in OnVisible as you have, the collection evaluates with "All" under the "Result" column and nothing in a "Value" column for that row. The is has my choices in the "Value" column of the next few rows, with nothing in the "Result" column.
Great videos, big fan. For this video, I got off-track really early, in the first Filter() call, in fact:
Filter(HouseholdInteractionSurveyData, DoorAnswer = DoorAnswerDD.Selected.Value) ( .Result is not an option)
I am drawing off of a SP list, HouseholdInteractionSurveyData, that has a header, DoorAnswer. I have a Dropdown called DoorAnswerDD. I get a red underscore under the equal sign, "Invalid Argument Type."
DoorAnswer and DoorAnswerDD are both Control data types.
DoorAnswer was originally gathered via a dropdown.
If you can offer any advice, it would be greatly appreciated.
Thank you, Shane.
Here is the working reply I got off of Power Apps Forum:
Filter(HouseholdInteractionSurveyData, DoorAnswer.Value = DoorAnswerDD.Selected.Value)
I needed this because the column data type for my SharePoint list was Choice
Glad you got it.
Hi Shane! Thanks for the video! lots of good info in it. I have a question, though: If the gallery to which I want to add the dropdown filters to is a nested gallery filtered by a previous gallery already (like you showed in the Learn to use the filter function video), how do I keep the filter and add new ones to it? Thanks!
Yikes, not sure i have a quick answer for that one.
Hello Shane! I have 5 filters that need this logic. Can you explain a little more about the "filtering collections upon collections" you mentioned in this video, or a different direction you think might be more effective?
Sorry I am slow Ana. You click a button to filter the data OnSelect I process each control one at a time. Updating a collection with the results until I get to a final collection. If(dropdown 1 = All, ClearCollect(collectedItems, datasourece), ClearCollect(collectedItems, Filter(datasourece, column = dropdown1); Do that 5 times. :)