DELEGATION in Power Apps | Must Know to build efficient Apps

[@nickc655]

Yet another awesome video Reza. Thank you so much!

[@RezaDorrani]

You’re most welcome

[@lpm76]

The advice to stich together collections to load large data sets is probably the most shared (bad) advice in the community. Thank you for making such an excellent video that highlights this.

[@RezaDorrani]

I couldn't agree more. What surprises me is that folks are using those techniques to avoid delegation.

[@lpm76]

​@@RezaDorrani I think it is because delegation is sometimes explained as the maximum number of rows PowerApps is able to work with, which is not true at all! A better explanation would be: It is the maximum number of rows it SHOULD work with, but LOCALLY and not server side. The example where you scrolled to the second page of your dataset showed this so beautifully together with the row count. It showed that when you respect delegation, PowerApps will actually help you paginate your dataset and ensure you still get the correct result from your queries. I mean how nice is that?! You don't even have to do anything; Power Apps just handles it for you. I stitched together collections in the beginning because I read bad advice and the worst thing about it, is that it kinda works; it performs badly, but it gets the job done. It was only later until I learned about delegation from gurus like you and Shane, that I understood my approach was severely flawed, and that by understanding delegation I realized that it makes my life much easier. I simply didn't understand that PowerApps could filter 1.000.000 rows server side trough delegable queries, but still be limited to only show 500/2000 rows locally. But it makes sense when you think about as you said, that the whole system is in place to urge you to minimize the amount of traffic between the app and the server. I still have many apps where the delegation is set to 2000 rows, but when you think about, how often do you really need to present more than 500 records to the user at the same time? Probably never. And how many apps have tables with more than 500 records? Probably all of them. Delegation works brilliantly when you understand it, and that's why I wanted to show my appreciation for your work Reza, because your video can improve the whole Power Apps community in a place where it is most needed.

[@SunniesUser5]

Made multiple PowerApps 🎉 Super thankful to your videos Reza! More power to you 🎉🎉

[@RezaDorrani]

Fantastic!

[@mikemorrison1931]

That testing tip for setting return query to "1" row was awesome!

[@RezaDorrani]

👍

[@JohnHall]

1) Don't trust the warnings for delegation (either that they do or don't show as delegable) 2) Delegation ONLY becomes an issue for your apps if the dataset gets large (>500 or >2000 once you change the defaults). 3) You can test to see if you have some delegation issues by setting an abnormally low query limit for your app (down from 500 to a smaller #) 4) All Delegation issues can be solved through better queries, better data organization, using Power Automate & ParseJSON, etc. (which all mean Dataverse isn't required to solve delegation issues in most scenarios) 5) Microsoft is likely to push better delegation features within Dataverse first (but that doesn't mean you should use Dataverse unless your app requires it or your organization is focused on supporting Dataverse more widely)

[@RezaDorrani]

Thanks for sharing

[@KaiWeissmann]

Thanks for this great overview of Delegation!

[@RezaDorrani]

Most welcome

[@nirjasmuhammed]

if the function is non delegable then the intellisense should be showing the delegation warning,..... surprised to know the issue with ' with ' function ... thanks for this amazing video sir............

[@RezaDorrani]

I agree that the warning must be shown. I will take that feedback to the team.

[@MuFeR.]

"With" not having a warning is expected in the example shown at ~10:40, it would be the same as if you did 1) ClearCollect(colStudentData, Students), 2) Filter(colStudentData, Region.Value "South") (no delegation)

[@adedaporh]

​@@MuFeR. I agree. Delegation warnings should only be for functions that is expected to work in the data source. With is a purely Power Apps function that can not be delegated. So a warning is misleading.

[@sinaa7562]

Thank you for this very good explanation! 🙂

[@RezaDorrani]

Most welcome!

[@dougydoe]

A really good refresher course on this all important topic. Hopefully, a few more functions will become delegable soon. Thanks for sharing. Appreciated.

[@RezaDorrani]

Glad it was helpful!

[@bestyapper]

Awesome video Reza. In regards to collection, one of my client keeps insisting that we load 6k to 8k into a collection and I have to use concurrent function. Takes about 30 secs and the app's performance is slightly worse, not App breaking but not as nice at all.

[@RezaDorrani]

Loading > 2k records is not advisable. Performance will take a hit.

[@shubhamkoolwal139]

informative and well explained with example...

[@RezaDorrani]

Thanks

[@mobi8138]

Reza, thank you for the video. I built an app connected to a SQL server that provides data that changes minute by minute. What is the best way to provide a count of this data based on different criteria like total number of calls, high priority calls, and pending calls? I do get the delegation warnings but the number of records displayed at one time is around 200 or less. This app also provides the users the ability to do a history check based on either an address or phone number. Is there a way to ensure that the users are getting the full list of records if this search does exceed the delegation limit?

[@RezaDorrani]

Count functions are non delegable with sql. You could call stored procedures to get counts. I have no video references on sql to help direct you to. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com

[@josephcarlos8463]

Dude you are amazing. I learn best from examples like you show. Thank you so much. The Superbowl is on right now and I'm learning from your video. I'm learning here to advance my personal power, otherwise I would be wasting time. Thanks :)

[@josephcarlos8463]

BTW what version are your currently using?

[@RezaDorrani]

Thanks so much! Enjoy the super bowl.

[@RezaDorrani]

version of?

[@KartheekReddy5]

Great Video as usual. Thanks for the Info

[@oluwatobiyusuf]

Thank you another amazing video, Reza

[@RezaDorrani]

My pleasure!

[@bwebmasta1]

Great video as usual, very informative!

[@tausifshaikh312]

It's been great Reza sir if you post on your experienced basis,scenario based interview questions its been really helpful for us You are Gem 💎💎 for us Regards

[@RezaDorrani]

That is not an area I am well aware of.

[@vigneswarikrishnakumar5895]

Well explained Reza. This really helps.

[@RezaDorrani]

Glad to hear that

[@raslaankhan5146]

Thank you for a refresher!!!

[@RezaDorrani]

Welcome!!!

[@michelelomonaco5118]

Good morning Reza, I have a sharepoint table with 2 columns, the first: ID_PARENT is numeric and the second PROJECT is a text and contains 8500 records. If I use the Filter(StartsWith(PROJECT,"PROJECT001")) function I get all the records of PROJECT001, if instead I use the Filter(StartsWith(ID_PARENT, 10)) function I get only the data that are in the first 2000 records. Maybe the problem is that you can't use StartsWith with numeric fields? Thank you.

[@RezaDorrani]

you can't use StartsWith with numeric fields

[@michelelomonaco5118]

@@RezaDorrani Thank you.

[@adityapadmawar8754]

Very well explained Reza sir! Thanks for creating this video. I have seen Power Apps slowing down with 1000 rows in SP list. What is the best or recommended source/backend for any Power App?

[@RezaDorrani]

Thanks! There is no single answer to best data source. Answer is "it depends". I deep dived into this concept during the LIVE training workshops.

[@adityapadmawar8754]

@@RezaDorrani Okay..thanks

[@CédricCHEVALIER-y1d]

Thank you for this useful reminder

[@RezaDorrani]

Always welcome

[@hamytube]

Thank you @Reza, for another excellent and informative video . Tons of thanks. 👍

[@王超-c9y]

hi reza, thanks for the great video!! i do have a question is about, i heard there's actually a 5000 items limitation for sharepoint.. i'm just worry about, if my sharepoint list has more than 10,000 items, when i using filter() function, will the data be impacted by that 5000 sharepoint limitation even filter is a delegate function?

[@RezaDorrani]

SharePoint list can store upto 20M records. The 5000 is the list view limit. Check documentation on index columns for SharePoint

[@王超-c9y]

@@RezaDorrani noted with great thanks reza!

[@udayreddy2605]

Hi Reza, Thanks for this video. Really Helpful. Quick Question: I am working with around 0.5 Million rows SQL table. On this table we need to perform a group by operation and create landing page and for slicers we need to perform a distinct on column and get all different values from that column. The challenge here now is Group by & Distinct are non delegable Power Fx functions when connected to a SQL connector. There is no way that I can found to do this with other delegable functions. How do we approach this problem now?

[@adarshmc8158]

Create sql views

[@RezaDorrani]

Table shaping functions are not delegable. Sql views may be an option

[@udayreddy2605]

​@@adarshmc8158 @RezaDorrani - Thanks for the idea, I can create a sql view for group by post which also I will end up with a table greater than 2000 rows, On this table if I am using distinct its still an issue as it picks up the values from only first 2000 rows and I end up not having all unique values from my column. Anny idea on how do I tackle this after creating Views. Thanks in Advance

[@ManojShukla31]

Hello Reza, It's again a nice video. I have question regarding delegation. I set delegation limit to 2000 (that is max limit) in this case if my search result crosses the limit i would get only 2000 records, how do i get rest data which couildn't come beacuase of limit.

[@RezaDorrani]

Welcome! Thats the limitation if your query is not delegable. Better not to use search function and use StartsWith in that scenario.

[@LatinoTropico]

This is fine when using a Sharepoint list, but with SQL server, there's a limit too surely. Do you know what it is? I tried your Filter() method with my SQL data source and it brought back only 500 records - tops! No more records beyond my filter could be brought back, even though I know there's thousands of more records in sql. So how can we control delegation for sql server databases? What's best practice?

[@RezaDorrani]

I will recommend checking the documentation for delegation to see SQL limits.

[@shaikhfirdos2723]

Amazing tips as always ❤❤ Thanks Reza

[@RezaDorrani]

Glad you like them!

[@victorconnan6282]

Great video as always! I saw in documentation the 'in' Fonction is delegable in dataverse but I try to filter a gallery on a multi choices column and get delegation warning. Does a workaround exists? I try addcolumn(concat to filter on string but performance is not great because the app needs to collect the whole table.

[@RezaDorrani]

in is delegable for certain data types not all. Check documentation.

[@tonya3308]

Great videos! Do you know how to turn the Comments off when using the Edit mode in PowerApps?

[@RezaDorrani]

I am not aware as I have not come across anyone wanting to turn it off. Check documentation and see if there is something provided for it.

[@kevindibb6534]

I have a SQL (Synapse) datasource with about 60,000 records. What's the best way to work with this?

[@RezaDorrani]

I have no clue about that

[@shubhamsatpathy6489]

If somehow we solve delegation . Powerapps can we used to make enterprise level application

[@RezaDorrani]

Delegation is not a problem. It is an inbuilt feature that's a benefit to app performance. Power Apps is used to make enterprise level applications.

[@StoneCold9898]

Hey Reza, can you pleaseeee let me know how to get the attachments through ms forms. Setting is disabled for external users, but i need it anyhow to put the documents as attachments in the saem list item created by form submitted. Please Help!!!

[@RezaDorrani]

I have done videos on it. Ms forms anonymous users - there is no option for attachments. If setting is disabled - there is no other option that Im aware of.

[@StoneCold9898]

Thanks for swift reply. But can you pleasee let me know any other options but ms forms to get the responses as well as attachments from external organisations into our sharepoint.

[@RezaDorrani]

@@StoneCold9898 Im not aware of other options.

[@kkb92-96x]

I have a really large list with about 90K records. Filter and StartsWith does not show any records. I don't see any delegation warning but no results either. To be precise, it is very errant in showing results. Sometimes I see results and sometimes I don't even when I know there are records that start with the term provided.

[@RezaDorrani]

Depends upon the query you have framed. I have not come across any issues with lists > 500k records as well. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com

[@Nj-hw2cv]

Consider I'm collecting a table from a backend custom connector API which has 10000 data rows in that case how can i optimise the gallery and show it. Please help reza. Is it possible we can achieve the same like when scrolling it loads?

[@RezaDorrani]

If everything is in a collection, then there is little scope for optimization as you have already loaded data in memory in the power app.

[@Nj-hw2cv]

​@@RezaDorrani I couldn't understand 😢

[@Nj-hw2cv]

What if i had 1M data then loading it in collection is not a best practice

[@Nj-hw2cv]

So what power app will do if anyone is using a custom connector and retrieving data The power app itself is not suggesting that ? So we have to use sql, dataverse, SharePoint?

[@Nj-hw2cv]

Please reply when you are seeing it reza😊 I'm a fresher working in a startup 😢 Love from India

[@ABCD-ABCDEFGH]

So it’s not a good idea to load sharpoint data in collections and use collections as data source? Should we connect to SharePoint data source directly if your records are more than 2k?

[@RezaDorrani]

Collection is not a data source. It is only a space to temporarily load data for the users session. Best to connect directly to data sources.

[@annisamegarestya8800]

Hi Reza, If I want to make an app that can connect Dataverse and Web Apps, Can we make a connection between them?

[@RezaDorrani]

Whats web apps? Power apps can connect to multiple data sources.

[@annisamegarestya8800]

@@RezaDorrani Can we input data from a web (HTML, CSS, JavaScript) to a dataverse?

[@RezaDorrani]

@@annisamegarestya8800 Not that I am aware of

[@ankithguptha5184]

How to upload multiple files to dataverse record and save it to share point doc library.And send notification email to user in that particular dataverse record with multiple documents attached in the email

[@RezaDorrani]

I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com

[@AlbertoAllegueVarela]

Thanks for this great video. Very Helpful!! May we use Power Automate to bring more than 2000 items to Power Apps and keep them into a collection? Using Get Items action?

[@RezaDorrani]

Welcome! Delegation over anything. Why do you need to load 2000 records in collection via flow?

[@AlbertoAllegueVarela]

To avoid the delegation issue. I understand we should not work with large local collections but, sometimes, it can help when you need to use non delegable queries as CountRows or Distinct and the number of records are, e.g., between 1 and 10000 (no more).

[@RezaDorrani]

@@AlbertoAllegueVarela I would not recommend doing that as it will have performance implications. Thats why one has to use better data sources like Dataverse that offers max flexibility in terms of delegation and more.

[@sohitthakur6660]

@@RezaDorrani How can we load more than 2k record in our gallery if I am using datasource is Dataverse .

[@TheKermit2110]

VITAL! Thanks Reza!

[@RezaDorrani]

Most welcome!

[@christianalvarado528]

Basically, you need to become very creative at the moment of writing the query of your choice. Which heavily relies on the data source used, being SharePoint one of the most common, and at the same time one of the most limited.

[@RezaDorrani]

Absolutely! From a delegation flexibility standpoint: Dataverse > SQL > SharePoint

[@malikhan0123]

CAN we show more than 2k line items in gallery, gallery is delegable or not

[@RezaDorrani]

Thats what the video explains. If query is delegable and you keep scrolling down, gallery will keep loading all the data.

[@malikhan0123]

@@RezaDorrani i mean can it load after 2k line items..it would be good if you shown that in video

[@RezaDorrani]

@@malikhan0123 Try it out and see. It will load as many as you need. I dont see any user doing that though as scrolling to get data to find is not ideal. Better to have good filters on screen.

[@jeevithaelango671]

Hi Reza, can you please help me to load more than 6000 records in powerapps

[@RezaDorrani]

If you keep scrolling in gallery it will load as many as you need

[@MrLuizclaudio09]

1:13 Very Good

[@NimbusBI]

how to handle countrows function under a filter query? like: CountRows(Filter(datasource,logic)) > 10

[@RezaDorrani]

Countrows is not a delegate function unless you use dataverse (limits are documented)

[@NimbusBI]

@@RezaDorrani On a text propert of a label, I write " CountRows(Filter(Temp_Item_Selection, Dealer_Code = "Cust0001")) " this code. And it shows delegation warning. Warning massage : The highlighted part of this formula might not work correctly on large data sets. The “CountRows” operation is not supported by this connector. We use sql server as connector

[@NimbusBI]

@@RezaDorrani I write a query like " CountRows(Filter(TestDB,VendId = "Vend001")) " on a text property of a label. But i found this warning massage. The highlighted part of this formula might not work correctly on large data sets. The “CountRows” operation is not supported by this connector. We use sql server as connector

[@RezaDorrani]

@@NimbusBI CountRows is not delegable with sql either. Check documentation to see what’s supported for delegation for your data source.

[@djpowerage]

Thanks!

[@RezaDorrani]

Welcome!

[@DirtNerds]

why does everyone show samples with a share point list instead of a MS SQL data source. on one in there right mind in a large organization is going to use a sharepoint list.

[@RezaDorrani]

Because setting up a SQL data source requires resources + cost implications + licensing needs. Because everyone has access to SharePoint and is easy to get started with.

[@nikitananaware8211]

how to retrieve more than 2000 item in gallery without delegation warning?

[@RezaDorrani]

That’s the whole idea of delegation. It loads more than 2000 items as the user scrolls down. One should NOT be loading all that data in one go.

[@KrishnaKumari-tr2vi]

Ko

[@RezaDorrani]

?

[@KrishnaKumari-tr2vi]

V। ❤❤❤❤❤😂😂

[@RezaDorrani]

?

[@KrishnaKumari-tr2vi]

😢😢😢😢😢

[@RezaDorrani]

Good or bad?