Power Apps Search and Filter Functions on Large SharePoint Lists
Vložit
- čas přidán 29. 07. 2024
- In this video, you will learn how to use the PowerApps Search Function, Filter Function & combine both. These functions are useful to allow users to search through all items in your data source and/or filter your data. We will build a search box, look at delegation (working with large data sources e.g. SharePoint Lists) & understand why Search function may not return all items with SharePoint as a data source. We will explore delegable functions like StartsWith & look at a technique of performing search against a filtered (delegable) result set.
Video covers the following:
✅ What is Delegation? Key concept to understand
✅ Search Function
✅ Filter Function
✅ Search and Filter functions combined
✅ Search on multiple columns
✅ StartsWith & IN Functions
✅ Delegable Filter Query & Search (Non-Delegable) on filtered result set
My PowerApps Delegation Playlist:
• Power Apps Delegation ...
Power Apps Filter Gallery:
• PowerApps Multiple Fil...
• Power Apps Sort and Fi...
#PowerApps #Search #Filter #Delegation #SharePoint
Table of Contents:
00:00 - Intro
00:31 - Power Apps connected to SharePoint List
00:54 - PowerApps Delegation - Working with Large Data Sources
03:55 - Search box in Power Apps (Search function delegation challenge)
07:28 - Search function limitations with Large SharePoint Lists
07:51 - StartsWith function supports delegation
09:32 - StartsWith Search on multiple columns
10:24 - Search & filter on Power Apps Galleries
12:15 - Filter & Search SharePoint List in Power Apps with delegation
19:34 - Subscribe to Reza Dorrani channel
The varDataCount with the slider control was my personal favorite. Well done Reza.
Thanks Dan 😊
@@RezaDorrani Yes me too. Totally agree with Daniel here :)
What minute can I see the slider?
@@danielgil9651 13:44, but u should watch the video from the beginning, to understand why he put the slider in
Wow! This helps a lot! I never knew I could combine multiple filters in on text input. Also, I always set the records from 500 to 2000. It looks like I have some fine tuning to do that will improve performance! Thank you!
Whatever I need, find a solution from you.
Again thank you, amazing contribution!
Great to hear!
This is in all ways superb. Thank you ❣For the search reset button I had to use '+' instad of ',' - only then I was able to combine the reset functions. Now its working 😁
Glad it helped!
That's interesting! I never thought about getting data with inner queries and then applying additional non delegable queries to get the results. Thanks Reza for such unique information.
You're so welcome!
It's exactly what I needed, had a problem with delegation on a large list and search with cascading filtering components. Thank you for the great lesson!!!!
You're most welcome!
Wow. This is just excellent. This resolves so many 'issues' with apps that work with large Sharepoint lists. Thanks for your patient, methodical teaching.
You are most welcome!
Hi Reza, this is absolutely fantastic. I had been banging my head against a brick wall trying to achieve this but your tutorial was explained and covered in depth and has solved my issue. Keep up the good work.
Glad it helped!
Thanks a lot, Reza. Been working on my project through the help of your videos but got stuck in the search part. It seems like the universe is telling to continue it now after seeing this video. Amazing work! Looking forward to your next uploads
Thank You so much. Keep going with your project.
Excellent - managed to adapt this to use one field to search one of 3 columns (rather than 2 drop downs and a text field) UPRN, PostCode and Address to narrow down and get below the power apps limits with a 139K row share point table. Surprisingly fast. Also put in a default value in the text field - so it started with only one record to pull.
Awesome!
Ingenious solution to a common and annoying share point problem. Thank you again Reza.
Thanks Simon
Not sure I got to watch this when you released this a year ago. I'm about to embark on a project which will require that I provide filtering/Search capabilities to I decided to refresh from my no 1 go-to source and I certainly wasn't disappointed. Great insight and thanks for that. 🤣
Most welcome
Love how you explain in such detail. This is one of the most helpful tutorials I've found.
Thanks Andrea
Works great with combo boxes as well. Thanks Reza, you saved us again!
Great to hear!
Reza ur the best bro, understanding the pulse of the audience and knowing their needs is the next level u pushed to.
Thank You 🙏
You are a true gem for our community. Many thanks for this great video!
Wow, thank you!
@@RezaDorrani at the contrary, we should all be saying thank you
Have been banging my head on this one for 3 days, THANK YOU
No more head banging please :)
Great work Reza as usual. I didn't know the delegable part of your overall query will still work even if the overall query is not delegable. That is very helpful.
Glad it was helpful!
Reza, you never fail to produce informative and beautifully explained concepts!
Thanks
Best explainers of delegation around. Excellent work Reza.
Much appreciated!
Thank you so much Reza. You and Shane Young are my heroes for complicated Power Apps issues! :)
Thanks
Dear Reza, Your chanel as well as Daniel's are so incredible in terms of knowledge. I'm so impressed and sooooo thankful for your work. Thanks a million times !🤗
Wow, thank you so much. We try our best to share what we learn.
Amazing video !! You help the entire comunity bring this videos for us Reza, i just only to say THANK YOU !
My pleasure. Thank You so much for watching my videos.
Great stuff Reza - Some amazing tips!
Loving the conditional use of In. I would say if there were metrics on what functions get used the most in apps, the In function is now trending sharply upwards! 😀
Thank You. I hope the trend is UP :)
Thanks Reza! This was EXACTLY what I needed. I appreciate your ability to explain in detail very quickly! I’m very new but expected to perform at the highest level. I hope you can help me do that. I will subscribe to your channel.
Thank You
Reza you done it again you're the best PowerApps guru I ever seen bottom of my heart as you make impossible possible thank you for you help and teaching us ♥, I hope one day you will show us a loan app calculation or sales ordering app please put it in dairy for future thanks
Wow, thank you so much.
I will add your requests to my backlog.
Dear Reza, You are exceptional. You make very interesting videos on power apps and associated tools. Your explanations are extremely clear and well structured, I have never seen so good videos on the net than yours. You exactly explain what I am looking for without useless comments or parasites like most others do. You do a very good job and you are a very pleasant person. Congratulations, best regards. Greatings from Belgium ! All the best for the new year.
Thank you very much!
My focus has always been to cut out any fluff & get straight to the point :)
@@RezaDorrani You do it perfectly, congrats !
Assalamu alikum.
You are the power man of the PowerApp and going this platform to next level 👍
Thank you ❤️
Wow, thank you for your kind words.
It is extremely good session for filters. Thanks Reza Dorrani❤
Most welcome!
Thank Reza for sharing videos with great tips. I found them brilliant, easy to understand and exactly what I'm after for my work. Well done 👍👏
Glad it was helpful!
Best is entrance of every film ;) . But to the point. Thank You Reza for all movies. Very helpful
Glad you think so!
I got it again amazing lesson and enjoyed it during learning. Thank you so much Reza.
My pleasure!
awesome video explaining a challenging concept. Good work Reza!! Appreciate the clarity and insight.
Glad you liked it!
Exactly what I was looking for..thanks !
Glad I could help!
Thanks Rezza, always something worthwhile learnt.
Glad to hear that. Thanks for watching.
Excellent content as always. You are my go to when i need help with PowerApps.
Thank You 😊
What a clean explanation! Top notch
Thank You
Superb explanation! Thank you Rezza!!🍻
My pleasure!
Reza, very well explained and got what we exactly want.
Glad to hear that! Thanks for watching.
Thank you so much Reza. I have always wondered on how to work on large dataset from SQL. Thanks for the insight
Glad to hear that!
Thanks Reza. Really helpful video. Just what I was looking for.
Glad it was helpful!
From Argentina, thanks!
You are most welcome Tomas
Brilliant, thank you Rezza!
You are most welcome!
Yes , good idea in powerapps filter and search with sharepoint list , now I will think about how to modify my code to meet my boss demand !!!
Great 👍
Thank you very much! Helpful as always! My project is based on your videos😂
That's great!
So well explained. Thanks Reza 💥👍
My pleasure!
This is awsm ... following your videos bring 'VALUE ADD' to projects
Thanks a ton
Thank you so much for this Reza. I learned a lot and was able to implement a similar functionality using dataverse table.
Awesome!
Brother you are teaching like you made this Power Apps. Superb
Wow that’s a big compliment. Thanks Niraj
Awesome, I like the way you describe it. Thank you.
Thanks for watching Mohit.
Awesome delegation handling video Reza👌👌👍
Thanks 👍
Smart way ! Thanks reza !
An alteenative if they want to use search only is to ask them to use sharepoint :) the search support billion of entries as it is indexed
You are most welcome.
Thanks a lot for the Reza, recently I was looking for this :) :)
Glad I could help!
Awesome Never gave a thought on combining it. 🙃
Me neither until I came across a requirement to get this to work :)
now this is great thank you so much for the work and effort
You're very welcome!
Simply amazing.. thanks a ton.😀
Most welcome 😊
Very nice innovation Reza (up to your usual standards of course). I have bookmarked this and you should get some more views from forum responses on this subject. Warren Belz
Thank You Warren. Much appreciated!
feel hard to learn this logical operation but thanks a lot for your solution, so much helpful
You are most welcome
Excellent! Thank you!
Glad you enjoyed it!
Bro your video is amazing and very helpful to me. Hope to see a lot of upcoming videos.
I will try my best
Awesome video! Thank you!
Glad you liked it!
You are the best, this video is useful!!!
Thank you! 😃
excellent video, a sincere thanks Reza!
My pleasure!
Thank you so much Teacher you are the best ❤
You are most welcome!
Hello Reza, thanks for sharing. I'll try to implement this solutions.
Awesome
Excellent Coverage!
Thank You
This helped me big time. Thanks
You're welcome!
This is so helpful. Thank you Reza - the like button just isn't enough. I used the Thanks button for the first time ever. Hope you enjoy a nice coffee on me.
Awesome, thank you!
Great Reza ! Thanks a lot !
Welcome!
Excellent Video as always...
Thanks
thank you very much!
You're welcome!
Another superb video
Thanks
Thanks for sharing this Reza.
My pleasure!
This one rocks amazing video
Thank You
your Good Man thank you Reza
Thanks Osaid
Thanks for your video... amazing
Most welcome
Very helpful.. thank you
Glad it was helpful!
Awesome !!!!
Thanks
Thanks!
Thanks
Great content, thanks for sharing your knowledge. I would give it 2 thumbs up if I could.
Thank You so much 😊
This is very valuable info
👍
@Reza Dorrani why isit that when user press save changes or refresh button, all the value is reset to blank (visually). The database does save their changes made, but to see the records again... they will have to either refresh the entire app, or type some something in the text filter, then cancel it and itll show the right data. What is going on?
This is not the case in my scenario.
I have not come across the mentioned issue and hence not sure what the reason for your issue is. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Very useful info thanks for sharing.
My pleasure
Amazing channel better than Microsoft Tutorials.
Thanks :)
Top Reza, the combi of filter and search; did not think of this before. Still Microsoft should give us the delegable 'in' operator for SharePoint 😊 (missing that so much on >8K lists) ; must be easy as the SharePoint underlying database is SQL Server. Probably Microsoft wants to keep a distance to the SQL connector ... but that is under premium license 😒. Gr. Lex
Thanks for watching
Amazing! Thank you for this. Will definitely use this on my project.
I have one question, regarding a problem I’m facing. I have a 10k items sharepoint list. Each row is assigned for a particular individual (about 50 distinct individuals). When I put a dropdown control so I can filter the person, the dropdown itself dont load all the 50 people. Only a few, from the first 500 rows from the limit that was set. How can I overcome this? It’s an important filter so I can check each one’s work.
You can try the person type technique I showcased in this video. Distinct function is not delegable with SharePoint.
I do not have a video on your use case. I will recommend you check on the forums at powerusers.microsoft.com/
@@RezaDorrani thanks!
Hi Reza, as always your videos are great content, easy to understand and very imporant! Thank you so much for your work! I currently try to do a full text search an a sharepoint document library. Do you have a way to search & filter in .txt .pdf .doc files etc.?
Thank You.
Not sure about searching in files. Something for me to investigate on.
Another great video. Is it possible to filter a gallery but only have the power app show 5 items.
Why would you want that? :)
You can use FirstN(filter( your condition), 5)
Hello Mr Dorrani,
Your videos are amazing, thank you to help the Power Apps community !
I'm new in Power Apps, and I want to create a research bar which can accept all values of my sharepoint columns (column 1 = material (steel, wood, stone, etc), column 2 = quantity (50, 600, 5, etc.) etc).
Writing steel, 750 or other value, I want to display the results.
I have big difficulties to do it, I tried by many ways, but I know I'm close...
Thanks for liking and watching my videos.
I do not have a video on your scenario and it is something I have not tried.
I would recommend to post your query on the forums at powerusers.microsoft.com in case someone has done something similar.
@@RezaDorrani Thank you for your quick answer, have a nice day !
Superb!
Thank you! Cheers!
Thank you Reza, very helpful. Do you have any tutorials for the count you have on this video which changes depending on the selected records?
At what time in the video do I show the count?
Thank you! clearly, beautifully, visually.
I get ideas from you on application design)
1.If I set a limit of 2000 and know that I will not have more than 2000 lines, can I not do these manipulations with delegation, hiding the search string?
2. And the question is why is it not desirable to make a limit of 2000 lines?
3. do I understand correctly that if there is less than the entered limit, for example 2000, the filtering function will show all the values searched for through the search bar
1 - if less than 2000 items, then you can directly use Search function.
2 - Since all those 2000 records will be loaded in memory in your Power App.
3 - same as point 1. If you have less than 2000 records, you can avoid delegation.
Thank you
Thanks for watching.
Nice video Reza 👍👍
Glad you enjoyed it
Works well as long as you are okay with the default sorting which is oldest to newest. I want to display newest to oldest, so when I put in a sort function, the gallery will only show the oldest 500 rows.
That should not be the case. I have not come across the mentioned issue and hence not sure what the reason for your issue is. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
This video was very helpful, One Question: Just like the way we filtered text columns, is there a way we can filter choice type columns using Starts with function? Meaning I don't want to create additional dropdown filters, I want the filtering to happen on the search box, and it has to filter text as well as value(dropdown values).
May be possible but not something I have tried or have a video reference for.
Hello,
I am using SQL server as the data source and I used a drop-down list to filter data where in items property, I have given all the distinct values of that particular data. But since power apps only stores 2000 records in its memory at max, the list shows only the distinct value from those first 2000 records. What work around can I do to get all the distinct values from the data.
Distinct is a non delegable query and Im not aware of any workaround for it.
Another excellent video. I learned more from you than the course I paid online. OK, I have a simple question, if I wanted the search box just to search for the ID and nothing else. Do I get rid of the rest and just leave the txtSearch.Text in ID? Thanks.
Thanks.
Yes, you could do that. But ID is not a delegable column. So it would only search based on the delegation limit.
Awesome
Thanks
Amazing video! It really helped me solve an issue I ran into with delegation :) I would like to implement the Search function when the dataset has been filtered to within the delegable limits but I am working with the Data Table control rather than the Gallery. I can't set up the varDataCount variable because the CountRows function does not seem to work on a table control. Is there a way to perform CountRows when using a Data Table control? Thanks!
I have not tried with data table hence not sure. I will recommend checking on forums at powerusers.microsoft.com/