Power Apps Search and Filter Function with SharePoint + Workarounds 

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

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

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

Great review Shane. My students will benefit from this mightily

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")

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

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

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

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?

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!

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?

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

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

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?

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

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

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?

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?

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.

Thank You from India

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?

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!

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

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!

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

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?

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)

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

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.

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.

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)

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?

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?

How do I do this for a Sharepoint lookup column?

Hello, Nice tutorial But i have an question If i search on multiple columns i don't get what i search for For example i search on a lastname i get 1 or more result but when i continue writing also the firstname i don't get any results anymore What do i wrong? SortByColumns(Search(colAllData; Trim(TextInput1_8.Text) ;"Title";"field_1";"LASTNAME";"FIRSTNAME";"field_7";"DateText";"MonthValue";"field_12");"Title";If(SortOrder.Ascending;SortOrder.Descending)) Thank you

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

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

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.

How to do the search with date?

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

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

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