PowerApps Delegation with SharePoint as a Data Source (Part 1) 

You are welcome!

Thanks for watching

The delegation series was one of the earliest videos I made on Power Apps. Hope you enjoy them.

Thank you Daniel.

Thanks so much Sean

Thanks for watching

Delegation is a tough topic to explain in a simple way. Glad you liked it.

Thanks for watching

Thank you

Hope my future videos are good :)

Glad to hear that! Thanks for watching.

Thank you Mikhail for the fantastic feedback. I hope to keep providing relevant content.

Thank you Mikhail

Thank you Kim

You are most welcome

Glad it was helpful!

Most welcome

Thank you!

You are most welcome

Glad you liked it

Most welcome

Thank you! Cheers!

You are most welcome

You are so welcome!

Thank You

👍

You're welcome

Thanks for watching

Indexing ensures that you can query the list/library if the data in the list/library is greater than 5000 records. In SharePoint online (modern) - indexing is performed automatically as data grows. Note: You can only have a max of 20 indexes per SharePoint list/library. To know more about SharePoint limits - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-AkyW8cWDVoc.html

StartsWith is a delegable function. I just tested with a space in the text example: "Power Automate" and it did return the results for me. I would recommend sharing screenshots of your issue with details on the Power Apps community forum - powerusers.microsoft.com/t5/General-Discussion/bd-p/PowerAppsForum1

Excel is not a delegable data source in Power Apps. You could load data into collections but will run into performance issues for large data sets. ♥ to Colombia

Thanks so much! All great questions. The problem - I have done very little work with sql and power apps. Not sure how it handles or if it handles clustered indexes. You should be able to sort data from table based on your columns as long as that sort function column type is delegable. I will recommend checking on forums at powerusers.microsoft.com. I’m sure there are experts who have done work with SQL and Power Apps delegation.

@@RezaDorrani , thanks for the quick reply. I'll definitely check out the powerusers forum you provided a link to. I was able to resolve my issue by adding a SORT function to my filter statement. But I'm still curious to found out how and if powerapps uses SQL indexes. Thanks again.

You are most welcome!

Most welcome

Thank you!

Search is not a delegable function. Filter and StartsWith are with respect to SharePoint as a data source. Each data source is different

You need to ensure that you set that visible property for each and every control within the gallery.

Check if you are still getting delegation warnings in your App. Next, check if there is any data that matches your criteria.

@@RezaDorrani Thank you, it now works.

Excel data is stored locally in App so does not run into delegation issues but is not ideal for large data sources as all data is loaded into memory in App. Total count for delegable data sources would return max 2000 since CountRows is not a delegable function. I did a video showcasing a workaround for CountRows with SharePoint - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qsIn0mytC_8.html

@@RezaDorrani I said that badly, my data source is Sharepoint, I have download sharepoint list into Excel to analyze data. I'll see your suggested video. Thanks a lot

Welcome!

From what I remember, multi-line is not supported.

Thank you for watching.

Excel files with power apps do not support delegation.

Hi Leonel, My latest delegation video shows the Count items workaround ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-yXvDj60-3YQ.html My upcoming video will showcase SUM, Average etc.

Possible but grouped by itself is not delegable.

Reports in Power Apps? You should be using power bi for that.

I am assuming you mean number of data sources connected. I don’t think there is any limit but performance starts getting affected.

I prefer not to use excel for this reason. You would have to query and load data in batches and hold them in collections. Possible but has it challenges in terms of performance as well.

@@RezaDorrani Thank you

This looks to be an issue with your use case. It would not be possible to solve it here on chat. Best place to seek assistance is the community forums where you can post your screenshot of your formulas and explain the issue - powerusers.microsoft.com/t5/Building-Power-Apps/bd-p/PowerAppsForum1

Drodpown lists I believe have a 500 item limit. Combox Boxes have a 2000 items limit. If you need to go beyond them then considering redirecting user to a seperate screen where they can make selections from a gallery control.

Gallery always loads data in batches of 100. Once the user scrolls down, it will keep loading the data (as much as you want). This is optimized loading of data which is delegation which is best practice.

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

CountRows is not a delegable query. Check my delegation playlist. I have covered a lot of scenarios including CountRows ru-vid.com/group/PLTyFh-qDKAiE6ia-D94Qk5-AzpN4dqOmD

is not delegable as far as I know. Im not aware of workarounds. I have done 7-8 delegation videos - check and see if any of those help. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.

@@RezaDorrani Thank you so much. You are really doing nice job. Great work.

Hi, I think this video could help you ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

Thanks for sharing

@@RezaDorrani but im using a collection rather than combo box as it reads the collection when the app runs, example user A has access to regions USA and UK, so User A should see all data related to USA and UK, the country columns is a choice type column

@@marounsader318 Check my new videos on role based apps

Once indexed and formula supports delegation, there will be no issues. Would recommend checking this video out as well - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-AkyW8cWDVoc.html

@@RezaDorrani thank you for the speedy reply!

Check ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

The Database has over 10k records with related lists. Not sure how to share that.

It is not

@@RezaDorrani what is the workaround for this?

@@sonalshinde5248 Im not aware of any

Yes. The workaround is not that neat though. I am currently working on a video for delegation workarounds.Blan Since or Not or ! is not delegable with SharePoint Text columns, you would have to frame your query in a different way. 1 example is as below: Filtering a sharepoint list called students where the Description column is not blank. So the query looks like below. It works as long as my text column starts with any of the alphabets. Can add numbers and other characters also in there. Filter(Students,StartsWith(Description,"a")||StartsWith(Description,"b")||StartsWith(Description,"c")||StartsWith(Description,"d")||StartsWith(Description,"e")||StartsWith(Description,"f")||StartsWith(Description,"g")||StartsWith(Description,"h")||StartsWith(Description,"i")||StartsWith(Description,"j")||StartsWith(Description,"k")||StartsWith(Description,"l")||StartsWith(Description,"m")||StartsWith(Description,"n")||StartsWith(Description,"o")||StartsWith(Description,"p")||StartsWith(Description,"q")||StartsWith(Description,"r")||StartsWith(Description,"s")||StartsWith(Description,"t")||StartsWith(Description,"u")||StartsWith(Description,"v")||StartsWith(Description,"w")||StartsWith(Description,"x")||StartsWith(Description,"y")||StartsWith(Description,"z"))

Hello, check if your case is covered here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-yXvDj60-3YQ.html

Yes, but give me 1 user in the world who would do that :) It is important therefore to define good filters so that users can refine the results. Check - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

@@RezaDorrani yes definately no one will do that, just doubt so asked. One more doubt, in filter 20kTh record will be shown after we enter text in seach box? Is this my understanding right?

@@rthoughts7232 Search is not delegable with SharePoint as a data source. Check my playlist on delegation.

Why do you need 6000 items in a collection? That is not best practice. Just query data source and ensure you are not getting a delegation warning. Power Apps can work with millions of records if delegation is in check. Loding data in collections will load data on the users device. Imagine that much info on a mobile App.

@@RezaDorrani I am planning a dashboard of Migration summary. My query mostly includes Filter and CountRows. If I am using List directly I am getting delegation warnings. My filter query is on Choice, single line of text columns and then get count of those items. I have seen in ur video coutrows is not a delegable function and we should go for flows. But how far using flows in my scenario is efficient I have to check.

@@saiabhinav3554 You will have to use flows. What if your list keeps growing. You can’t just keep loading data.

Depends how you are stoing those links and what type of column it is. There are certain bugs in Power Apps related to links. powerusers.microsoft.com/t5/Building-Power-Apps/Links-to-SharePoint-from-PowerApp-not-working/td-p/412439

As long as delegation is handled, Power Apps can work with large data

@@RezaDorrani but is it necessary to load all the data into PowerApps? In order to LookUp?? From it?

@@mustafahussainmohsin320 Whole idea of delegation is bringing data in batches.

@@RezaDorrani I understand that but What I will do is connect that SP List in PowerApps and I will lookup in that SP on an indexed column as you said using filter and Lookup will it work ? And find that 289,899th record?

@@mustafahussainmohsin320 Yes as long as that query is delegable - it will search entire data set but only return data in batches. Plz watch entire delegation playlist to understand how delegation truly works. No user will put a query to say get me 289899th record. The query would be more on lines of get items with status X, due date today, priority Y etc.

Important concept

Also how could you search beyond the limit of 50 records when the count is fixed at 50. I have using same expression in the gallery items property like starts with

Delegation warning will not go away is formula is not delegable. Changing limit from 500 to 2000 will only tell power apps to fetch more rows.

If query is delegable it will perform the operation on large data sets. Im not sure what you are trying though. Il recommend posting your query with screenshots on the forums at powerusers.microsoft.com

Thanks for the quick response Reza. I have a gallery connected to Share point list which has around 450 rows for now. But it can grow in future suppose beyond 2k records. I want to have a text input box as a search input for name of person from the data available in SharePoint list. Just want to make sure even if records exceed the 2000 mark , my search input box should work. Currently it is working fine using sort and filter functions just as the example in the video.

@@ahujakran As long as there is no delegation warning, you will be fine

Please post your issue with screenshots on the forums at powerusers.microsoft.com

I did not understand your question. I recommend posting your query/issue with screenshots on forums at powerusers.microsoft.com/