Power Apps Sort and Filter on Multiple Columns
Vložit
- čas přidán 25. 07. 2024
- This beginners video tutorial on Power Apps Sort and Filter on Multiple Columns in a gallery covers all the basics of using the Sort, SortByColumns and the Filter function together. We will perform dynamic sorting and filtering of gallery data all while keeping delegation (working with large lists) in mind. We will sort and filter on multiple columns and perform search operations on data using StartsWith function with SharePoint List as a data source. These basics on Sorting and Filtering of PowerApps data in a gallery or a datatable are important for every Power Apps maker to learn.
#PowerApps #Sort #SharePoint #Filter
Delegation:
docs.microsoft.com/en-us/powe...
SharePoint Sort and Filter function delegation:
docs.microsoft.com/en-us/conn...
My playlist on delegation and data filtering of gallery:
• Power Apps Delegation ...
Sort and Filter formula showcased in video:
github.com/rdorrani/PowerApps...
Table of Contents:
00:00 - Intro
00:20 - SortByColumns in Power Apps Gallery on Text, Number and Date columns
03:47 - Sort function in Power Apps on Choice / Dropdown & expressions
09:14 - Combining Sorting with Filtering on Multiple Columns
13:35 - Add sort button and sort direction Icon
21:33 - Subscribe - Věda a technologie
Thanks a lot for sharing this, it solved an actual scenario when you use search as well as want user specific dynamic sorting :-)
Glad to hear that!
Thanks very much for the video. I'm from the Brazil, and my english is very basic, but you bothered to speak clearly and slowly, so that everyone understands. Thanks!
Thanks Michel. Glad to hear this feedback. I have worked on my speech to ensure I speak slower and clear. Very happy to hear that folks are seeing improvements.
Above and beyond, even the last part about delegation. Perfect video!
Thank You
Brilliant content. I've seen other videos on these topics but none that combine all of the features together in one clear and working example. I am learning PowerApps for use at work and I have now subscribed to your channel based on this video alone. Great work!
Thank you for the amazing feedback and welcome to my channel.
This method is by far the easiest I have seen to achieve the end result. Thank you Reza!
Thanks so much Dan
Awesome video. Thank you for covering the delegation part. It is very useful.
You are most welcome
Thanks a lot Reza for your awesome videos. I can now avoid the delegation issue while multiple column sorting
Glad to hear that
Another amazing tutorial. Always in anticipation of something new in your videos. Keep it up.
Glad you like them! Goal is to show something different each time.
'Brilliant' describes the quality of this session. I face the problem, and there is Reza's video that exactly solves it. Not only solving it, but I learn so much from it.
Thanks so much for the awesome feedback.
The King of Power Apps. Thank You!
Content is 👑
Thank You
I was wrestling with multiple filters for my gallery. This is a great help, thanks!
Great to hear!
Super helpful video! Was able to troubleshoot and role out my app for user testing same day. Much appreciated!
Great to hear! Thanks for sharing the feedback
This is exactly what I was looking for. Thank you very much for this great video.
You are most welcome
That is cool. Your explaination in Each and every step is very clear in all the videos. You're are a Master of powerapps.
Wow, thanks!
This is an excellent tutorial, shortened my formula by at least 40 lines and improved app speed
Awesome 👍
Brilliant. Please keep these videos coming.
More to come!
Hi Reza!
This video has been pretty useful to me. Just one observation. Please go to minute 21:16. There is a bug on the formula that checks sort direction. You wrote: "If ( varSortColumn = Ascending, Ascending, Descending)". It should have been: "If ( varSortDirection = Ascending, Ascending, Descending)". That´s why the sort icons seem to work unproperly as you might see between minutes 21:23 to 21:30.
Thats correct! Many have reported this in comments :)
you are the best Mr Reza, thank you for everything
Thanks Salim
Hi Reza, you are amazing and helped so much with my project. Just wanted to let you know! Keep it up!
Glad I could help! Thanks for watching my videos.
As usual, great video, thanks for helping us accomplish better apps!!
My pleasure!
Thank you!!! This is what I need for my app.
Glad to hear that. Thanks for watching
ANOTHER Life-saver... THANK YOU REZA!!!
Most welcome
Great job Reza. Thanks for sharing
Thank you
Very helpful thank you Reza for your effort !!
My pleasure!
Thanks for this video, It solved the most demanded features from my app users.
Glad to hear that. Thanks for watching.
PERFECT!!! Amazing work brother!!!
Thank you! Cheers!
Great job man ... lots of vids out there on this subject and you're approach is one of the better ones... you should make a video on brainstorming methods when coming up with logic flows for answering probs like this. 🦅
Thanks so much.
Reza this is Amazing as usual with all your content. In terms of new videos, I'd love to see you re-do the repeating Grid video but with Sharepoint as a data source and not CDC. Thank you.
Thanks for watching.
I have done the grid video 2 times with Power Apps and Dataverse for Teams.
If I ever do a 3rd version, I will choose SharePoint.
Thank You Reza Duranni For This Incredible Solution 😊
You are welcome
Perfect as usual!
Thank you! Cheers!
Ok, i would not like the first minute but at the end this tutorial helped me a lot and i should appreciate it, so i do!!!, Thanks Reza! Regards!
You are most welcome. As long as the content is providing value, that’s a win for me.
thanks a lot, your video helped me to set the correct filter and sort my list :D
Glad to hear that! Thanks for watching.
You saved me!! Thanks a lot!
Glad it helped!
Excellent content Reza! Very clear explanatiion step-by-step. Once minor comment: I understand that Task column is bound to Title field in the gallery. I was scratching my head for a few mins when you say Title, but you were actually referring to Task field. It wasn't obvious in your video I guess. I finally got it after pausing and going through your screen and code.
Thanks so much!
Apologies for the naming confusion. SharePoint has concept of internal name and display name.
Internal name was Title and Display Name was Task
Very rare content and useful example
Thanks for watching
Muito bom, obrigado pela aula,ja irei seguir voce!!!
Thanks
Thank you you saved me a lot of time with this video 😎😎😎😎
Glad it helped!
awesome again !
Thanks again!
Thanks again for sharing this wonderful video Reza!! I do have a similar implementation of sorting from before. Then I encountered delegation issues.
I found your pagination video last week and it worked well but was stuck in incorporating my old sort solution. Alas I stumbled on this video and confirmed all the things in my situation.... that complex types won't work, the dynamic variables are not working either, and I have to repeat the same code (filter of 5+columns) for each column I want to sort....
keep up the good work!! :)
Sometimes we just have to write long formulas to get around delegation.
Very nice!
Thanks
fantastic tutorial :)
Thank you! Cheers!
Boom!!!! Love eeeet!!!!
Boom!!! Thank You!
great job thanks
Thanks for watching!
Nice Reza, thanks!
Welcome!
Another good one. Thanks.
Most welcome
@@RezaDorrani will this logic work with Search too? Or do i have to do Filter? I am having a hard time getting it to work with the following. SortByColumns(Search(Job0013_Holiday_Gift_List,
TextInput_Search.Text,"EMail","First_Name","Last_Name", "Company"),
"Company",SortOrder.Ascending,"Last_Name")
@@scottmatson3313 Search is not a delegable function with SharePoint.
I cannot guess if formula will work by looking at it. Will have to try it out at my end. I would recommend posting your query on forums powerusers.microsoft.com
excellent presentation. it helps me lot.
Glad it was helpful!
Awesome 👍
Thanks Sourav
Really Nice. I always learn something.
Glad to hear it!
Amazing Video
Thanks!
Well explained .Thanks Reza
Thanks
Thanks!
Thanks David
Amazing! I am watching this video for the 1st time on 3rd of April 1 year later 😄
Awesome! 😊
More great stuff Reza.
Thanks
This is a great stuff with a combination of SharePoint Online and Powerapps. That would be great if you can post a session how can we create a SharePoint list Grid view in the gallery.
I have done 2 videos on grid views on my channel. If I do another version then I will choose SharePoint.
Thanks Reza !
👍
Hi Reza, another amazing video, Thanks for sharing!!!... Would it be possible to include the Switch for varSortColumn inside the Filter functions just the same way you did with the If for varSortOrder (Ascending/Descending)?
You can but it will result in delegation warning and will not work with large data sets.
Thanks Reza..👍
👍
Thanks for these videos Reza! Extremely helpful for new power apps users. I did find that the varsortcolumn function inside your switch function was not working. I noticed you said "sort direction" but the syntax shows sort column. Hope this helps anyone else who's sort icons weren't working.
Thanks. I made a mistake on that part of the video :)
Hi @@RezaDorrani Your Videos Really help us a lot !! one thing we can do is change "Sort Direction " in github
@@qayyumkadri2138 Thanks!
Sort direction in GitHub?
@@RezaDorrani yes , you can paste corrected code there with single line note on top.
@@qayyumkadri2138 There are 5-6 such videos where code need correction. I would do so in my free time which is very limited these days.
I have a lot of plans but limited time.
Hi Reza, Thank you again and again for the great video's you make. I followed the switch formula you provided, i am sorting on ID column and 3 other Date fields, everything works great except 3 date fields, when sorting on date fields , any empty fields are not included and some rows go missing, any idea why? your help is appreciated. Thanks in advance!
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Thank you very much for this wonderful post. I have a question how to sort status where the status is 'Not started' ?
is Not a delegable operator. Sometimes you have to write long formulas to get around it.
Lets assume you have 3 status values
Not Started, IN Progress and Completed
Formula would be
Filter(datasource, Status = "IN Progress" || Status ="Completed")
Thank you for the detailed explanation. I'm having trouble with dropdown + OR function.
I am trying to filter my gallery by two dropdowns, either dropdown1 or dropdown2, or a combination of both. Both of these dropdowns are linkd to tables stored on excel. These are "month" and "task".
My main data is in a table called Main. It has a month and task column.
What I want to achieve is by selecting dropdown month, the gallery returns all the items with the month selected or If i click dropdown task, it returns all tasks. If i click both, it filters both the month and the task. Same as your video.
I'm using Filter(Main, 'month' = dropdown1.selected.month, OR(task = dropdown2.slected.task)).
However, this only then filters both combo boxes e.g., when i click the month dropdown, it will not show any results until I click on the task dropdown. It then applies the filter for both, but wont show just the months with all tasks.
There is a blank option in the dropdown which is the first option to prevent accidental filtering.
You need to check if your combo boxes are empty - Check czcams.com/video/5dSk5iOgT68/video.html
Also, your expression is more of an AND than an OR.
You need something like this + add logic for empty check
Filter(Main, 'month' = dropdown1.selected.month || task = dropdown2.slected.task)
Great video! I used this in a few apps already. Quick question for a different use case I'm working on. Is there any way to integrate this with the Search( function? I'm working on a FAQ app and for that, the startswith function isn't convenient for searching, but it is still beneficial to be able to do some basic filters on the content.
czcams.com/video/zxZGaXQlZqo/video.html
Hi Reza! Thank you so much for your video. It was very helpful. If you wanted to add the Sort Up/Down icon to your choice field called Priority (to make the sorting of that field dynamic also), how would you incorporate that into the formula you have in the Items property of your Gallery? Because in the Items property of your Gallery, you use SortByColumns. But if you wanted to sort your choice field called Priority, you would need the Sort formula instead of the SortByColumns formula. (And I'm not worried about Delegation issues because my list only has about 60 items)
Set a variable on icon selection - up or down value. And use that to define sort direction. I do not have a specific video on this scenario hence giving an idea.
You can also try posting your query on forums in case someone has done something similar
powerusers.microsoft.com
Hi Reza, this is a great tutorial! I have a question - my Datagrid has many column so it shows a scrollbar. When I scroll to the right, the sort arrow does not move with the relevant column header and gets misaligned. How do you fix that?
In the video I have shown a gallery control.
If you need the columns aligned to your datagrid (not sure which control are you using), you will need to set the x and width coordinates of the sort arrows based on position of the column headers.
Thank you Reza
You are most welcome Sundra
thank you. Is there any way to accomodate complex column within this dynamic sort?. i.e. sort of priority column from your demo?
You would need to table shaping formulas like addcolumns to get the text values from complex columns and then sort on it however those formulas are NOT delegable
Thanks
Thank You
Thanks Reza....
Thanks for watching
Thanks, Reza
Thanks for watching
how did you have table format after getting data from sharepoint? I am trying to use excel sheet but I am not getting the tabular layout and second... what did you code for text box and drop down menu?
I customized the gallery design.
Check czcams.com/video/bnC8u3gdWss/video.html
These are all brilliant insights. Could you make a video using a people picker as part of the filter for the gallery. This seems to be a gap in CZcams university. Thanks.
I have shown that in some of my videos. Title may not be that specific. You will need to look through.
Thank you! Does this work on the Data table too?
Yes. Same concept will work.
Hi Reza. I'm trying to combine SortByColumns with a Filter not based on a text input. This uses a context variable on button OnSelect: UpdateContext({SortDescending1: !SortDescending1})
- My gallery filter function works like this: Filter('testRaw Materials List', Customer.Value = "Spring Sheep")
- My SortByColumns works like this: SortByColumns( 'testRaw Materials List', "Title",If(SortDescending1, Descending, Ascending))
How do I combine these two statements? When I use syntax similar to what you've done in 10:37 I get an error. Using SharePoint List as a datasource
I will have to look at your App in action to provide any guidance. I would recommend you post your issue/query with screenshots on the forums at powerusers.microsoft.com
Very nice and informative video. I have one query regarding my one of the issue, I wanted to show data from sharepoint list Title field in PowerApps ComboBox and my Sharepoint list is having items more than 5000. Could you please suggest the best approach to achieve this.
I do not believe a combo box supports more than 2k items (or may be limit is 500).
Not sure why would you want the user to have a list of titles. What not just give a search box as shown in video?
Thanks Reza for yet another important topic. I am facing some delegation issue related to this. My use case is very simple, My data is in sharepoint list and I have a workdate column defined in that as date variable. I want to show only the entries from current month and that in descending order. For this I have wriiten a simple code as
SortByColumns(
Filter(
Timesheet,
Month(Now()) = Month(WorkDate)
),
"WorkDate",
Descending
)
Month(WorkDate) is the one creating delegation issue. Do you think I need to define another variable and use switch function or there is a simple way to tackle this?
Your formula posted here is not complete. DateTime columns in SharePoint are not delegable. Date type columns are.
Check my delegation playlist - czcams.com/play/PLTyFh-qDKAiE6ia-D94Qk5-AzpN4dqOmD.html
I would recommend you post your issue/query with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani Thanks for your reply. I will post this in forum with screenshot.
hi reza, does the video go over sorting by a choice column? im getting unable to sort by this column type
I did this video a long while ago so I do not remember myself.
This is an amazing video, thank you very much.
Is it possible to sort record type columns such as Choice, People or lookup?
Possible. I do show Choice column sorting in video. However, it will run into delegation issues.
@@RezaDorrani Thank you for the reply. To avoid delegation could the switch technique you used @19:49 in the video be used on record columns?
Also, just a heads up I believe lines 14 and 45 in the github syntax should be VarSortDirection and not VarSortColumn. took me awhile to figure out why mine was not working.
@@jbo1253 It would not help with delegation.
You are correct on that mistake :)
Hey, thanks for the video! Everything seems to work great, but the sorting only goes in one order. Your arrow changed but the numbers didn't (at the very end). I'm having trouble figuring out how to fix this, any ideas?
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi Reza, Thank you very much, Your videos are amazing! I am following your directions for sorting functionality and got an issue, I wonder if you could give some guidence. I have a horizantal container and inside i have a data table(similar to your service desk app with responsive design), In the data table i inserted sort icons on column headers. for some reasons the icons are not getting aligned properly with data table headers, are there any known issues in using table and icons in side a container?
I have not come experienced this and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Thanks once again for your comprehensive tutorial!! @ quesrtions:
1)What about those filter and reset icons. I figure the filtering would set one of two filter icons (filled, empty) visible and one hidden and the reset would reset filtering. HOWEVER, how do we know when the "filtering" is engaged?
2)Is that fancy header a component and how did you construct it? I cant get the quarter circle icon to rotate.
Thanks
1 - To check if filtering is engaged, I check to see if the filter controls have values in them.
2 - Fancy header is a string of label controls tied together :)
Check czcams.com/video/bnC8u3gdWss/video.html
@@RezaDorrani Thanks Reza. It didn't work that way for me. I had to put a hidden label above the search boxes which pulled the value then the code checked if those labels had values. I had to put a circle on top of the filter icons as I found it was only working if you clicked the edge of the icon/text. The transparent circle on top seems to work.
Thanks. LOVE YOUR VIDEOS!!
Thank you Reza. For about Reset button of after all search /filter/sort , please advise me for your VDO? thanks a lot
Welcome!
I did not understand your question.
@@RezaDorrani sorry I got it after reviewed in this content again and understood about reset button.
Hi Reza,
In the last part of the video, where you talk about delegation due to dynamically defining sort column, could you explain scenarios where this delegation warning would actually return wrong output? Or will we practically never face any issues, even though there's a warning (provided that our dynamic column name assignment is valid).
I have experienced that not all warnings are actually problematic e.g. countrows with firstn etc. delegation issue.
Could you please address this? thanks :)
Give me all items from my SharePoint list sorted in asc order. If sort column is dynamically defined will result in delegation and Power Apps will load only the first 2000 records (assuming thats the delegation limit) in memory and then sort them. All other records in data source would be ignored.
Delegation warning will never be a problem in case of data sources less than 2k records.
@@RezaDorrani Ok, then how do we solve for dynamically defined sort columns with delegation in mind?
@@kx2793 That is a limitation.
Hi Reza!! Nice and informative video. Can you tell me how to reset the sort icons back to original? so that no direction is selected?
Its been a long while since I did this video. I believe the variable used for defining sorting, you can try setting that to Blank()
Please note I am only guessing as I do not remember this video step by step
Hey Reza, Thanks for your videos. I am learning alot from your channel. I am working on a vehicle inspection application. Can you just make a one tutorial to how to create a tabbed form and give the user an option to take an image and save it in a particular record. I mean with the information and the image .It ll be helpful. Thanks.
Thank You.
I do have a video planned for working with the camera control (take an image) and upload to a particular record.
@@RezaDorrani Thanks for the reply. I ll appreciate this. Please do it. It ll be very usefull.
@@lohitdevgan6799 It's in my backlog
Can you please show how sorting can be done in data table ? It will be really helpful ! :)
Logic should be similar.
I do not have a video on that.
Will add it to my backlog but this is the first request on this topic. I will need a lot more requests on this topic to give this a higher priority.
Thank you Reza for your video. But I am unable to show all records in a gallery as you have done in your video. Could you help me in guiding how to do that?
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hello Rezha, thanks for such a nice explanation , but I still have one doubt, can we sort on complex column types along with the other columns
As shown in the video, complex columns have delegation challenges. You will have to mix both the sort and sortbycolumns functions to perform multiple sorts.
Thanks Reza
Hi Reza: If I were to mimic exactly what you have in this Video, I need create SharePoint Lists with appropriate data SLT/Choice/Date/Person/Lookup columns. "SHOW MORE" does not list such screen captures. I can do this and populate some real looking data by watching the Video. But, have you provided data used in this Video somewhere?
The data in my App is in a SharePoint list. I have not loaded or provided the data anywhere. It is just sample data I added for demo purposes.
"SHOW MORE" does not list such screen captures - I did not understand this.
Great Video - really helped me with clean my app. Having an issue though - when I go to choose which column I want to sort, not all of the columns in my SharePoint list appear, but a number of unnamed columns (just names "field_**" appear. I've refreshed the data and data source, but don't understand why all of my column names aren't appearing to be used in the sort.
Thanks.
I will have to look at your App in action to know the reason for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
I have a sharepoint list with repeated values for the name field value and I need to display them in a gallery with the unique values and grouping values according to the match. How do I do it?
thanks
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
This is an amazing tutorial. thanks a lot sir. but can you please tell me how can i reset all the sorting fields?
Add a button to reset sorting. On click of button, set sorting to default column of your choice.
how does your filter icon change color when user choose from filter? thank you
I think I checked it any of the filter controls have a value and accordingly changed the Icon
Hi Reza,
This is great and I have an issue.
App works fine on start and when I sort again, sort is not working for more than 100 rows and I see “loading data” notification.
FYI- I’m using SharePoint as source and data table to display items.
Any help is much appreciated.
Thanks.
I will have to look at your App in action to know the cause of your issue. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Great content again. Thank you!
Is there a way to search with "Contains", not "StartsWith"?
I've tried combining the knowledge from your other video: czcams.com/video/5dSk5iOgT68/video.html but couldn't make the search function work :(
Here is the video that explains "Contains" czcams.com/video/zxZGaXQlZqo/video.html
@@RezaDorrani Thank you! Helpful as always. :)
Thank you for a great video. Do you have an example of how to reset the filters. I cant quite work this out
Resetting filters basically implies resetting the controls.
Reset(controlname)
@@RezaDorrani what’s the format for multiple controls?
@@utilisemedia Reset(control1);Reset(control2); etc.
Looks good. However would be great if you could provide more context to the gallery that you are using at the very start. I have no idea how to get started and create the gallery that you are using to sort and filter. Thanks
I have videos on gallery designs and styles.
Check - czcams.com/video/bnC8u3gdWss/video.html
Hi Reza, Nice Video. But one thing I notice in the last part that the Descending in Sort Icon didn't work. I also follow your command and also repeating to watch the last minute which you are clicking the sort Icon. It didn't sort to Descending. Do you know what causes it? It was similar to what I'm facing right now.
Check most recent comments. Someone posted about it.
If I reset filter controls, I get blank gallery. I did reset on search txt box, Drp control and gallery also...
Check your formula again. I have the formula in the video description. No need to reset gallery.
This was great but for some reason, only the last of three sort columns I'm using is working for me. Selecting the other columns returns no data to the gallery when I run the app.
Not sure what column types or data source you are using. Tough to provide direction without looking at screenshots.
I would recommend you post your issue with screenshots on forums at powerusers.microsoft.com/
This is indeed a great video. But why i'm getting an error "specified Column Does not exist or invalid Sort Column Type" when i click the icon sort? what did I missed out?
Not sure why that would be the case