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

[@arulanand12]

Many thanks. Crystal clear explanation.

[@RezaDorrani]

You are welcome!

[@RezaDorrani]

Thanks for watching

[@kausarjahan1868]

This is by far the clearest and easiest explanation of delegation I’ve come across! Even though I already knew what delegation is, I’ve been struggling with some delegation warnings in my project, so I started digging into your videos. This 5-video series is so easy to understand and troubleshoot, though I’m still working on finding a solution to my warnings. Thanks for sharing such a great resource-definitely still valuable 5 years later!

[@RezaDorrani]

Glad it was helpful! I also did a new version of delegation in general - kzbin.info/www/bejne/aJSulKyiqKyfhsk

[@kausarjahan1868]

Thanks Reza, I actually just watched the new version, and it’s super informative as well. I really appreciate how you respond to everyone's comments and continually update your content... it’s a huge help for all of us

[@chrisproctor2854]

Can't wait to watch the rest of the delegation videos! This is definitely going to help in the future when I start building sharepoint lists with a ton of data!

[@RezaDorrani]

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

[@danielrumbak2530]

Hi Reza, thank you for demystifying Powerapps and Sharepoint. A real pleasure to watch your videos!

[@RezaDorrani]

Thank you Daniel.

[@FernandodeGuzmanII]

This is a big help. I am figuring out how large dataset works in power apps and this video enlightens me. Thank you very much.

[@RezaDorrani]

Thanks for watching

[@seanparker259]

Thank you Reza! Some of the best and clearest explanations of SP that I have seen. Appreciate your content!

[@RezaDorrani]

Thanks so much Sean

[@ZePowerDiver]

Very good explanation! Useful for someone like me who comes from the CDS side of the world. Thanks Reza!

[@RezaDorrani]

Thank you

[@MichaelKubala]

Good video. I was only somewhat familiar with delegation, but now I have a much better understanding of this concept!

[@RezaDorrani]

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

[@kranthitupakula9912]

Good Stuff. Looking forward for the future videos.

[@RezaDorrani]

Hope my future videos are good :)

[@tayebalassmahassansunhouri7864]

Great introduction to delegations, thanks a lot

[@RezaDorrani]

You are most welcome

[@valentineelias7299]

Great video Reza. Keep up the good work

[@RezaDorrani]

Thanks for watching

[@mikhailk5690]

Thank you Reza for the great and useful video. I have spent a lot of time figuring out why my formula is throwing a delegation warning and by looking at your video I realized that ID column only delegates '=' operation. I wish I knew about your channel earlier. Subscribed in order not to miss new videos!

[@RezaDorrani]

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

[@RezaDorrani]

Thank you Mikhail

[@AmritaOSullivan]

brilliant video, thank you so much for explaining this concept so clearly and in an easy to understand manner for a beginner!

[@RezaDorrani]

Glad to hear that! Thanks for watching.

[@klgraf]

Great! Really clear explanation of something that I was struggling to understand

[@RezaDorrani]

Thank you Kim

[@pandukataru7237]

very good explanation for beginners

[@RezaDorrani]

Thank You

[@AnuragKumar-sz6yw]

Very valuable and knowledgeable video this is.😊

[@RezaDorrani]

Glad it was helpful!

[@Akshita9614]

Sir, You are awesome. You just fixed my issue

[@RezaDorrani]

You are most welcome

[@sunilsahu1793]

Awesome...

[@RezaDorrani]

Thank you! Cheers!

[@raslaankhan5146]

Nice Explanation. Subscribed!!!

[@RezaDorrani]

Thank you!

[@austinbrown8326]

This is a huge help. Thank you for making this video!

[@RezaDorrani]

Glad it was helpful!

[@RezaDorrani]

Most welcome

[@muru4541]

Thank you sir I love the way you explain 👍👍👍

[@RezaDorrani]

You are most welcome

[@soeaung1984]

Thanks a lot, Mr. Reza.

[@RezaDorrani]

Most welcome

[@FarhanKhan-lr4tv]

it was amazing tutorial thank you

[@RezaDorrani]

Thanks for watching

[@aquabestindian]

Great video explaining delegation. One query though, if SharePoint has a limitation of 5000 rows/records, why does adding an index work? What is the index doing exactly?

[@RezaDorrani]

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 - kzbin.info/www/bejne/d5zciGuZjKmJpcU

[@devendrasariya2660]

Hi Reza, Thanks you very much for sharing.

[@RezaDorrani]

You are so welcome!

[@upendra798]

Great video and great Explanation. What if I use Excel Online as my data source, consists of more than 2000 records? As Excel Online is not a delegable data source, Is powerapps able to retrive the records more than 2000?

[@RezaDorrani]

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.

[@upendra798]

@@RezaDorrani Thank you

[@JAVIERANDRESAVILA]

Thank you Reza, great video!.. quick question for you, how filter is not delegable for excel data source, do you have any idea how to handle it? I have to use excel in this case and not move to share point!! My big issue is filtering information using excel data source!! Best regards form Colombia man! 👍

[@RezaDorrani]

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

[@pkp044]

Hi Reza, Thank you for the video! I have tried with visible property and I am getting long blank spaces when I try to filter items above first 500 items. Please advise

[@RezaDorrani]

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

[@tkroljic]

Reza, thank you for all the videos you create. They are extremely informative and very helpful. I recently ran into a delegation issue on a SQL table. I tried to resolve the issue by creating a clustered index to ensure that the newest records would appear first. But when I used this table in a Gallery, it doesn't use or display the data in the order of the clustered index. So my question is...when you create a data connection to a specific SQL table and use that table in the ITEM property of a Gallery, what is the default order of the data returned/displayed in the Gallery? Does it come back to the Gallery in order by a primary key? Is powerapp connections aware of clustered indexes?

[@RezaDorrani]

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.

[@tkroljic]

@@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.

[@Meerasree4251]

Great.

[@RezaDorrani]

👍

[@DavideGalloni]

Thanks Reza, just a question: I have a list with more than 2000 records, and I have a label that I use to show number of total records filtered by some data: The code of the label is: Len( Concat( Filter( DataSourceRS; SystemUpdated = "YES" ); "X" ) ) I found that formula into a Powerapp forum!! To check, I have exported the sharepoint list in excel and I have applied the same filters in excel and the total records filtered was 2034 but the label contains 2000 as totale record counted. What's the correct formula should be used to count a lot of filtered records??

[@RezaDorrani]

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 - kzbin.info/www/bejne/p6Ssn2Ojrtl2lZo

[@DavideGalloni]

@@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

[@pisitboonsong]

Thank you for explained I'd like to ask more in case of Power App link with Excel Data on sharepoint. How to set delegation?

[@RezaDorrani]

Excel files with power apps do not support delegation.

[@shaikzuber7945]

Thanks, best explaination

[@RezaDorrani]

You are most welcome!

[@RezaDorrani]

Most welcome

[@annamercer8796]

Does indexing the columns completely get rid of any limit or is there still one in place that might be encountered further down the line?

[@RezaDorrani]

Once indexed and formula supports delegation, there will be no issues. Would recommend checking this video out as well - kzbin.info/www/bejne/d5zciGuZjKmJpcU

[@annamercer8796]

@@RezaDorrani thank you for the speedy reply!

[@leonelbraga5427]

How can we solve the problem in counting itens from a shrepoint list? The COUNT function is not delegable. We can not even use the SUM. How can we count a huge sharepoint list?

[@RezaDorrani]

Hi Leonel, My latest delegation video shows the Count items workaround kzbin.info/www/bejne/r4nZdZ1sZZJmj7M My upcoming video will showcase SUM, Average etc.

[@ResponsibleXI]

Hello Reza, You have been wonderful all though this series of Delegation videos. One question though. I am trying to search based on Text Column and search text need not be the beginning Text of my Text Column Values. If my text value is "Power Automate", using StartsWith can only get results for "Power" query but not for "Automate" query. How can I bring the consistent search results for any text that I enter while handling the delegation issues.

[@RezaDorrani]

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

[@mkhalidumer]

Thanks Reza, Just a little confusion, as per your example, if a student record is after 2,000 records, we can't use search on it ? instead we have to use filter and startswith ?

[@RezaDorrani]

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

[@gerfer6261]

bloody good

[@RezaDorrani]

Thank you!

[@gabrielxandri646]

Gracias Gracias!!!!!

[@RezaDorrani]

Thank you for watching.

[@deepakrajana]

Hello Reza, great video. I am facing delegation issue with Filter-StartsWith on Multiline Text field. Is MultiLine not supported for delegation?

[@RezaDorrani]

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

[@tejaswadkar4651]

Hello Reza, I am working on large data set where I want to use filter function like Filter('mySharepointList', 'Proffession'.value "Engineer"). So Proffession is my sharepoint choice column where I had other 2 choices also ("Doctor","Lawyer"). So I only want to show all items in gallery except Engineer. And Proffession column is not required column so some items has blank that items also I want to show in same gallery. And I am getting delegation warning. So without delegation how we can achieve this??

[@RezaDorrani]

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.

[@tejaswadkar4651]

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

[@sarcastiXperimental]

Thank you

[@RezaDorrani]

Welcome!

[@Saadssk94]

Hello Reza, i wanted to know if there's a limit in canvas app for number of tables used? i was just curious, how many tables can i add to one app?

[@RezaDorrani]

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

[@syedabidaliabdi1273]

Hi, I am new in PowerApps. I developed canvas app. On visible it takes user location from user table via lookup formula and add into combination of ID column in PowerApps form. But suddenly it's stop working. While list containing only 4 records. please look into this.

[@RezaDorrani]

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

[@AbdulQadir-bv8ll]

Thank you for uploading.

[@RezaDorrani]

You're welcome

[@mohammadbilalali2603]

Hello sir, could you please tell me that the gallery has retrieved 500 items/records by default from the data source. but I saw in your video there are only 100 records retrieved by default? could you please tell me the background of that retrieval?

[@RezaDorrani]

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.

[@limychelseafc]

Hi Reza, is it possible to implement scrolling pagination with grouped by lists?

[@RezaDorrani]

Possible but grouped by itself is not delegable.

[@marounsader318]

hi reza, do you have a video related to filtering a dataverse choice column using IN operator i can go through? seems the filter syntax a bit different when we are using dataverse the (=) works but dont know how to make the filter work using IN operator, can you guide me please?

[@miguelsierra7379]

Hi, I think this video could help you kzbin.info/www/bejne/a5W2nGifhMyHbJo

[@RezaDorrani]

kzbin.info/www/bejne/a5W2nGifhMyHbJo

[@RezaDorrani]

Thanks for sharing

[@marounsader318]

@@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

[@RezaDorrani]

@@marounsader318 Check my new videos on role based apps

[@dineshvasamshetty8031]

Real scenario comes at 16:30 5k limit of SharePoint

[@RezaDorrani]

Important concept

[@shwethaagarwal9396]

Trying to create a cascading dropdown on my form in SharePoint based on a large dataset. Can you advise?

[@RezaDorrani]

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.

[@AllThingsRomez]

Hi Reza. Thank you for all the excellent tutorials, they are awesome and really appreciated. I used the same SortByColumns and Starts StartsWith from your example, as soon as I type into the search box all content dissapears, even if I type in one letter. What could be the issue?

[@RezaDorrani]

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

[@AllThingsRomez]

@@RezaDorrani Thank you, it now works.

[@rajatsabharwal8642]

Hi @Reza i have data more than 4000 can u please suggest how i can take all data to make reports. I had used concurrent function but that is not working.

[@RezaDorrani]

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

[@bcharishma3833]

Hi, I had a SharePoint list with morethan 12000 records,we have made a power app with that list as data source, delegation count 2000.has given count rows(gallery.All items).now when I'm trying to search for 11000 th record,it's not displaying any results in app.can you help me in this .?

[@RezaDorrani]

CountRows is not a delegable query. Check my delegation playlist. I have covered a lot of scenarios including CountRows kzbin.info/aero/PLTyFh-qDKAiE6ia-D94Qk5-AzpN4dqOmD

[@raj7sam]

Nice video but I have one doubt... Do gallery by default will load 20k items in chunk of 100 ( do we need to scroll , to get next 100 items, till we reach 20k th item)??

[@RezaDorrani]

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 - kzbin.info/www/bejne/a5W2nGifhMyHbJo

[@raj7sam]

@@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?

[@RezaDorrani]

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

[@sonalshinde5248]

Hi Reza, in SharePoint, is multiline text field delegable with startswith function? I am getting delegation warning for multiline text field.

[@RezaDorrani]

It is not

[@sonalshinde5248]

@@RezaDorrani what is the workaround for this?

[@RezaDorrani]

@@sonalshinde5248 Im not aware of any

[@ConfidencePoint1]

Great video Reza. Can you provide the SharePoint database you used in this video?

[@RezaDorrani]

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

[@miraclesurvivor]

Hi Reza, Can the Filter do any more one filter items, like this: SortByColumns(Filter('Job List', StartsWith(RO.DisplayName, XXXX, YYYYYYY, ZZZZZZZZZZZ, TextSearchBox1.Text)), "ID", If(SortDescending1, Descending, Ascending))

[@RezaDorrani]

Check kzbin.info/www/bejne/a5W2nGifhMyHbJo

[@xxfodxelementxx]

Do you have any workarounds for or !IsBlank? As a text column.

[@RezaDorrani]

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

[@RezaDorrani]

Hello, check if your case is covered here kzbin.info/www/bejne/r4nZdZ1sZZJmj7M

[@ravindrasinghkushwah7387]

I am using Filter(list, created > Date(2000,1,1)). It is delegable function but not getting expected results

[@ravindrasinghkushwah7387]

I only get 500 records but I should be getting 4000 records

[@RezaDorrani]

how are you checking the 500 number?

[@HITman0P-264]

"Text(POCurrentStatus) = lclPOFilterStatus " this line of coding is creating a delegation warning in my powerapps - can someone please help me because some records are not getting fetched from the database causing issue at the client end - PLEASE HELP !!! The delegation underline is below Text(POCurrentSatus) and "=" sign.

[@RezaDorrani]

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

[@mustafahussainmohsin320]

HI, Can we have a SharePoint list of 300,000 rows? or more than that and then looKup that list for further process in App??? like if I want to lookUp 289,899th row.. will it be performant and fast enough? to process when the Powerapp loads?

[@RezaDorrani]

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

[@mustafahussainmohsin320]

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

[@RezaDorrani]

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

[@mustafahussainmohsin320]

@@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?

[@RezaDorrani]

@@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.

[@saiabhinav3554]

Reza can u explain me how to collect 6000items of a sp list into powerapps clearcollect.

[@RezaDorrani]

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.

[@saiabhinav3554]

@@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.

[@RezaDorrani]

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

[@dimplekapoor8600]

Hi Reza Could you please help me with this I have saved video links in Sharepoint list , connecting sharepoint to power apps, i wanted to play the videos in Power apps, i'm able to play itin power app browser but not in mobile device it is showing black screen

[@RezaDorrani]

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

[@ahujakran]

I am facing delegation warning, though I have increased the row count from 500 to 2000 but still I have the warning. I have around 450 records

[@ahujakran]

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

[@RezaDorrani]

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.

[@RezaDorrani]

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

[@ahujakran]

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.

[@RezaDorrani]

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

[@saanisirajulhaque375]

Hi Sir Your videos are very good I have one issue with my one of the query I am getting delegation warning for it I tried with other way also but still I am getting delegation warning below are the query I wrote please help me how can I over come it Filter( 'ABC', IsBlank('PP'.Email) && (Status.Value "Rejected"), IsBlank('PP'.Email) && (Status.Value "Hold"), IsBlank('PP'.Email) && (Status.Value "Terminated") ) 2nd Method Filter( 'ABC', 'PP'.Email=Blank() && (Status.Value "Rejected"), 'PP'.Email =Blank() && (Status.Value "Hold"), 'PP'.Email =Blank() && (Status.Value "Terminated") ) Thanks

[@RezaDorrani]

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

[@HUYNHTANTAI-PTB]

SortByColumns(Filter([@DSVT]; StartsWith(MVT; TextSearchBox1.Text)|| StartsWith(CHITIET; TextSearchBox1.Text)) ; "ID"; If(SortDescending1; SortOrder.Descending; SortOrder.Ascending)) . I have more than 2000 lines of data, how can I search the text in the dark search for the following characters, for example: Name: John Wiliams, I filter the word "Wiliams" and still search Okay.

[@RezaDorrani]

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