Power Apps Search and Filter Function with SharePoint + Workarounds

[@jonathancopin8093]

The most important comment : "Power apps is always right when it says it's wrong, but it's not always right when it says it's right". I never thought of taking the Data row limit to 1 to test delegation issues it's a really clever solution 👍👍 Thanks Shane

[@ShanesCows]

Very hand tip. Glad it helps.

[@aravindenterprises2379]

Was breaking my head with the formula, then found out the field from sharepoint as mentioned, ur a life saver thank you very much

[@ShanesCows]

Awesome.

[@anapmds7247]

Shaned, I am from Brasil and your video help me a lot ! Obrigada! Thanks!

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@anapmds7247]

@@ShanesCows Do you have any video about "No item to display' when put the search?

[@cduke1963]

Workaround for non text fields. I’m going double check this, but I’ve used a powerautomate to capture the ID number of a row and then added it to a hidden text field in the list. This way you can Search or Filter on ID as text.

[@ShanesCows]

I like it. 😎

[@mappingman]

Shane, what if your organization won’t pay for premium options in Power Apps? Are we just out of luck? Can you pull the SharePoint list into a PA table? Is the table function delegable? From the other video I’m assuming no. So what can we do in PA to get the full list and be able to search against it? I’ve been working on this with Filter and using Left, Mid and Right in combination with regex. After looking at these videos I need to go back and see if I am actually getting all of the records back. 😮

[@ShanesCows]

Yeah, I bet you aren't getting all of the results. 🥲 Sorry. Things you can try - Nesting your functions kzbin.info/www/bejne/n3ibhJ2Nqb1kr68 - Archive to get below the limit.

[@mappingman]

Thanks.

[@golfnutt8]

Hi Shane, thnks for everything you do to help us navigate the Power Apps complexity. I am not using a gallery for and not using SP Integration. I can connect to my list my my form properies does not have "Items" it has "Item". Right the edit form shows "Item" and uses First('Office Supply Products') to show the data. Your gallery shows "Items" so I must be missing something. Can you provide any direction? Thanks again!

[@ShanesCows]

Items is the property for a Gallery. Item is the property for a form. Are you maybe mixing the two up?

[@golfnutt8]

@@ShanesCows No, I created a new screen and form. The data coms from a SharePoint list. I am trying to filter on the form to find records in that list. The new form display mode is in Edit mode. When I look at the properties at the top left I only see “Item” not “Items”, which is why I am not able to utilize your search function. I may be going about this wrong. ☹️

[@wennysoh3624]

Hi Shane, thanks for the guidance. If the data more than 2000, can we still use tgis seach /filter function?

[@ShanesCows]

As long as Filter returns less than the delegation limit.

[@wennysoh3624]

@@ShanesCows thank you very much 😊

[@rochellenicholson6372]

Thanks for the video. Is there a way to only show 5 items of a gallery, regardless of how many items are returned when filtered or sorted?

[@ShanesCows]

There is a function called FirstN that would do that. FirstN(YourTable, 5) should do the trick.

[@frankdeak8563]

Hi Shane, I am relatively new to power apps. Is it possible to maintain an up to date copy of a SharePoint list in data verse to get the best of both worlds?

[@ShanesCows]

Yes, look for virtual tables.

[@ShanesCows]

This videos talks a small about it New Dataverse Table Options + Virtual Tables kzbin.info/www/bejne/gKPRnZx3p5Kil8U

[@TheRealDLamar]

Is there a list of functions compiled somewhere? If I was creating a search bar without this video… how would I learn the functions?

[@ShanesCows]

docs.microsoft.com/en-us/power-apps/maker/canvas-apps/formula-reference My favorite piece of documentation.

[@TheRealDLamar]

@@ShanesCows thank you so much Shane!!

[@glaringmind-rq8xu]

I am trying to achieve similar things except my selected employee from the lookup will return a pinned location on a site map( showing office location ,floorplan, cubicle#. The requirement is after searching the employee in the drop down will show the employee details ( title, location, phone number etc) also it would show on the map as a pinned location pointing out the employee’s office location and cubicle#. Is there anything like this that you have worked on. If not would you be interested to show how this can be done. I am continuously learning from your videos and trainings. It would be awesome. Thanks in advance!

[@kathrynspader9362]

Have you ever used Text color as a variable to do searching or sorting?

[@andyhernandez5108]

Please let me know if you can create Folder in Windows desktop using PowerApps or Power Automate? I’m also interested in creating Folder Shortcuts. Can this be done at all?

[@ShanesCows]

I don't think so but I have never tried so not positive.

[@geralddahl9159]

22:06 re CountRows(DataverseSource) - Power Automate tells me I have 8046 records in Accounts, but when I try in Power Apps CountRows(Accounts) = 0 and when I adjust to CountRows(Filter(Accounts,'Accounts (Views)'.'Active Accounts')) it only returns 2000 and denotes delegation error. Any thoughts?

[@tonykuiper9127]

Hi Shane... Our IT support chap said yesterday (after setting some stuff up in our workshop) that sharepoint was "moving over to onedrive" in the sense that suggested that sharepoint was somehow changing and that we would need to "migrate" to onedrive? Is this a thing? I mean they have some similar functions like storing files etc, but onedrive cannot really be called a "mini database" like sharepoint could. (sorry for the random question)

[@matthewv4170]

OneDrive is built on SharePoint. I think your IT guy needs a bit of training. Also SharePoint isn't a mini database, you can use it as a database buts its essentially just a list

[@tonykuiper9127]

@@matthewv4170 cheers.. I guess I think about SharePoint in that way because of how I use it. Multiple lists being combined to house data and create output. I'm an old Access tragic. We don't have dataverse here. I think the way the company uses OneDrive is more a symptom of not being interested in the ability of the O365 platform. Just looking for cheap rather than exploiting the capabilities. Well that's my 2 cents anyway. Personally I love the whole environment.

[@ShanesCows]

Matthew is correct. Not sure what your IT is thinking 🥴

[@CarlosSolisNow]

Great review Shane. My students will benefit from this mightily

[@edbeardsley5558]

Great Video, thanks for the pointers. I have used your process successfully before, but form some reason I am not getting just the fields to populate. It is giving me the "fieldname"_datacard. Any suggestions?

[@ShanesCows]

That is weird Ed. Some days it get grumpy but showing you the data cards is a new one to me.

[@Nola_Travel]

10:00 finally someone solved this for me. Thank you!!!

[@ShanesCows]

Happy to help. :)

[@jasongregory9872]

Here is the question I have... How did you make the contact list. I have tried several ways to create a contact list to connect to the app? All I have now is a contact "List" that is sorted by my building location and people assigned to work in that building. I cannot seem to get that to import to the app correctly.

[@julianschmittgall3755]

Thanks so much for the great video! I have a list with a column that allows multiple values with checkboxes (characteristics like SEO, SEA, FBAds, ...). I tried to use your search logic with adding a column for those values but can't get it to work. I suppose it only works for columns with one value? Is there any way I can filter or search for one or more matching characteristics that are in this column (eg. SEO or FBAds)? Thanks so much again! - Julian

[@matthewv4170]

You could put a department drop-down on the filter using a distinct or group by department type for items in the dropdown

[@ShanesCows]

I like it. :)

[@matthewv4170]

@@ShanesCows it appears GroupBy is non delegable. Grrrrr.......why does PowerApps not warn you....

[@williamdyette1262]

Thanks so much Shane. With regard to delegation, PowerApps looks at the first 500 (default) items in a SharePoint list. Is there a way to start at the last entry and go backwards to the 500 previous entries? For example, your SP list has 10,000 rows. Can you filter to look at rows 10,000 to 9,500?

[@williamdyette1262]

I just figured it out. My original formula for my gallery: Sort(Filter(DocumentTracker,StartsWith(Creator,TextInput3.Text)),ID,varSort). To change it to look at the last 500, I added a second sort command: Sort(Filter(Sort(DocumentTracker,ID,Descending),StartsWith(Creator,TextInput3.Text)),ID,varSort). For some reason, I assumed you could only have one sort command at a time in a formula.

[@eroticrobot1234]

Thanks for this Shane - I am trying to implement the Search-Filter function displayed at around 16:00, however I am getting an error "this rule creates a circular reference between properties which is not allowed." Any insight on how to address it? I am trying to return only rows that match a specific department, and then using the text input search to search the combined first name/last name: Search(Filter(Active_suspended, 'OrgName' = "Department Name"), searchbox.Text, "nameField")

[@zurahk85]

Is there a way to search a choice list using excel (dataverse)? I was able to figure out your sharepoint version but thats it. (Also how can I prevent duplicate entries for dataverse? The duplicate rules in the settings dont seem to work.. ive added for example project number 24001 like 80 times without it stopping me. Thank you

[@ReiBalboa]

hi shane, my gallery is files and folder and i want to put an all the folders in the sub folder using this command Filter(DOCS,'Folder path'="DOCS/"). but at the same time i could search the files Filter(Documents, StartsWith('File name with extension', searchbox.Text)), can i combine this codes into 1?

[@vasylsalyk165]

I'm using another workaround - 2 galleries. Gallery1 - pure data source w/o filters or search. Gallery2 uses Gallery1.AllItems as data source. And here I can apply all the necessary filtering. Furthermore, if pagination is required, then I use Gallery3 (data source Gallery2) in combination with FirstN and LastN.

[@wongkaren2048]

Seems work for me. Many thxx!!

[@oluwatobiyusuf]

Thanks for this video. I learnt more on the search function

[@honganhuynh6003]

Hey there Shane, this was so helpful. But how would we filter by drop down + a date Picker? For example, I have a list for our transportation drivers who drive customers around. First drop down filters by Driver (which was explained by this video) but I would also like to let the user select the date to match up with the date field of the trip

[@ShanesCows]

Filter(DataSource, Driver = dropdown.selected.driver And DateField = DatePicker1.SelectedDate)

[@honganhuynh6003]

@@ShanesCows thank you!

[@finnpower92]

Is Power app fast with Dataverse as datasource, when using search? I would imagine that having a gallery of 25000 rows would load very slowly to end-user. I am not willing to change my first baby app's datasource anymore, but willing to consider it for the second child :D

[@thelegand1]

I found this video super helpful but it didn't help me fix my problem, I have a Table with 6 columns and a considerable amount of rows, I have three drop downs, first one to filter category, second to filter sub category from that category, then 3rd to filter the item description from the sub category, the other 3 remaining rows are price, lead time and item code. my problem is with distinct, it removes duplicates which is great, but some of the entries have duplicated item descriptions, blocking out some of my items, the only way I can figure out having them all show is by doing this distinct(all the junk inbetween),description & price) but then the problem with this is that it breaks my price and code box which references another drop down but just to pick up those figures to add into the gallery, sorry if this makes zero sense but it's 4am and i've been messing with this for 6 hours lol. help!

[@erinann7833]

Thank you for all you do Shane...I do have a question. Can you search on a gallery with 2 different tables? I have 2 sql tables that join by primary and foriegn keys. I have a gallery with 2 fields from table 1 and the 3rd value is from table 2 (used a LookUp to display it. But I want to be able to search by the fields from table 1 and 2. Is that possible?

[@vickiturner1658]

I think you would be best to build a view in your SQL database first, and then point to that as the datasource?

[@toddfraser7526]

So frustrating. I'm being asked to built app for an existing SharePoint list that's too large for the sharepoint interface (larger than 20,000 row view limit threshold) and it's a nightmare to work with. I want to go back to my SQL word, ha! I can't for the life of me figure out a way to pass anything delegable to Sharepoint to filter things down enough for search to work.

[@ShanesCows]

Todd did you watch this video to start at the basics? kzbin.info/www/bejne/i5KlnomLht2SfM0

[@toddfraser7526]

@@ShanesCows I have and it was helpful, a ton of your videos have been helpful! I'll skim again, I think the issue is Sharepoint is limited. I've dealt with delegation limits in a couple other apps but it's looking like I can't quite fit this need into PowerApps. It's a 50K row list of invoices that grows at 500 invoices a day. I thought I could filter to just items assigned to one user, and then let them search in that, but filtering to their items filters the first 500 (or 2000) of their items from all time in the list, I can't limit by status AND user, at least I haven't made it work. User is a sub table, person column. Status is a record. I can't get more than the delegation limit to ever collect.

[@ShanesCows]

@@toddfraser7526 Collect is not delegable and will never pull back more than the delegation limit. 😖

[@toddfraser7526]

@@ShanesCows good point. My goal was to apply enough filters that I’d be well under the limit, collecting was a means to counting, trying to make sure I was under the limit. I’ll tinker a few more minutes. Thanks for the replies!

[@toddfraser7526]

My issue is I can't seem to find a way to filter by user without using AddColumns and populating a text colum. with 'Assigned To'.DisplayName. I can't get the number of items under 2000 without filtering by user.

[@rajad7261]

I have one doubt can you please it ,it's urgent for me ,I watched so many videos&visited numbers sites .they aren't help to me . I have 8 number sql servers and different names databases in all servers data is different but columns names are same. I made crud operations for all servers. It's working. Because I'm developer that's why it's running. Now the question is if when user has been using this app how to the change servers dynamically, we don't need add filter. 😤 how it's possible can you please explain

[@josuecontreras7931]

I didn't know the importance of delegations until you show me the dataverse. From now on I will recommend to not work with SharePoint if you have a lot of data.

[@ShanesCows]

Glad to help 😎

[@eurekasheng542]

Hi Shane, first of all, thanks for your sharing~ recently I'm working on a project using UiPath to update sharepoint list, I met this problems that I'm not sure how to config the OLDB connection of sharepoint List, is there any way to update sharepoint list data from a local file or any work around?(I tired through Access, but I worry about the data sync problem)

[@finnpower92]

Would Power Automate work for your solution? It has very easy and reliable action to update sharepoint list, and also easily perform another action once a new sharepoint item is created.

[@eurekasheng542]

@@finnpower92 right…..automate may actually works, thanks!

[@marcmcrystal3427]

How do I do this for a Sharepoint lookup column?

[@SreeRanganath9]

Thank You from India

[@ShanesCows]

Happy to help

[@kishorjabegu4668]

Hi Shane, been looking at your delegation videos and its very useful for starter like me. One thing is bugging me though suppose I have a sharepoint list with a column called Name and I have 6000 records. Its data starts with (dummy name 1, dummy name 2, .... to dummy name 6000) and now if I wanna search the records with search text (name 6000) obviously that would not work as search is not delegable (even for max data row limit -2000). Also if I used Filter/StartsWith it would not work as Search is from middle part of text not start. How to tackle these sort of condition when I want to search from any part of text saved in sharepoint list column. Any ideas how you deal in those condition?

[@finnpower92]

I would consider creating a dropdown selection to choose 1000+, 2000+... Etc. Or really think that do i need to be able to search 6000 records at same time, could you use some 'active' column to limit the search to only one active names? If not possible, then i guess Dataverse is your solution.

[@franknielsen3219]

Nice video. I realy need my company to buy into use dataverse

[@ShanesCows]

Yeah, it is pretty powerful.

[@akhil8177]

How to do the search with date?

[@ShanesCows]

Filter for date not search. 😎

[@matthewv4170]

Not too sure why but this deleted my comment regarding search and group by

[@ShanesCows]

Boo. Sorry about that. I promise I didn't delete any of it.

[@matthewv4170]

@@ShanesCows it's okay. May be worth a video though Reza has one. It's a pretty cool workaround

[@ericzheng4838]

Delegation - what a pain for Powerapps beginners, especially for those non-programmer beginners!

[@andytuke8986]

Dude, you seriously need to get some sun cream on your face when you're playing golf