Delegation: communication requested

[mr-dang]

I posted this thread about a year ago and much has changed with PowerApps since then. CDS has allowed more complicated conditions for filtering a datasource. It's expanded security to record level in preview. In that time, I had created a solution for pulling in all records in a datasource. Were it not for the limitations, I don't think I would have taken the time to figure out a work-around, and I'm happy that I had learned much since then. Knowing what I know now, I understand that the restriction has achieved performance gains.

That said, a year has passed, but the 500 record limitation is still in effect. For Dropbox and OneDrive, you can only read from the first 500, but you can write more to it that cannot be read. For CDS, you can read from all records, but you can only pull in 500 at a time with filters.

I ran some numbers and the effect of the 500 record limitation is significant enough to the PowerApps forum community:

• As of posting, there are about 3300 discussions in the PowerApps forum
• 228 of the discussions contained either "500," "delegation," or "delegate"
• This effectively means at least 6.8% of the discussions started on the forum are questions related to the 500 record limitation.
• By default, the forum shows 30 discussions on each page. So 6.8% means that of the 30 discussions on each page, readers can expect 2 of the discussions to be about delegation on average.

I'm bringing up the forum stats because Stack Overflow just had a blog post about how their users had just sought the answer to "How do I exit Vim" for the millionth time: "In the last year, How to exit the Vim editor has made up about .005% of question traffic." The question was so infamous that it had become a meme in good internet fun. The scope of engagement on Stack Overflow is far broader than the PowerApps forum, so a few decimals can be large. But since the PowerApps forum is primarily about connecting to data, creating apps, using apps, and flows, 6.8% is a fairly significant percentage for inquiry. It has become "a thing" since there is a new discussion started about it every day.

Obviously the forum community doesn't represent all PowerApps users. There's statistics that the PA team might know (maybe not, due to privacy) that could be more informative than the stats I presented:

• What percent of users of CDS regularly make calls to entities with more than 500 records? Sharepoint users?
• What's the average amount of records in a CDS entity?
• What's the percentage use of OneDrive/Dropbox/Google Drive vs. connections that have better delegation like CDS?
• Has 500 been enough for the PowerApps user target demographic?
• What data has/had informed the decision making to choose 500?
• How many pageviews has the reference page for delegation received compared to others?

Since it's been a year, and since there is a new discussion thread started each day about the 500 record limitation, can we get a blog post or a a stickied thread that communicates the current progress or future roadmap for delegation or accessing more than 500 records? I think that should clear up that question so it doesn't come up again and again.

@Meneghino @AmitLoh-Powerap @hpkeong

@GregLi @jonoluk

[hpkeong]

Hi PA & @mr-dang

I am deeply impressed with Br. Dang's statistical approach to share with PA to seriously look into the % of users (though maynot represent all users) in delegation and 500 data limitation.

For my real life experience, I have to stop developing ERP for customers as 500 data limitation is just ridiculous esp during experimental stage of using Dropbox/OneD/GoogleD with Excel and PA Community Plan before actually engaged into Plan 2. This is somekind of confidence in testing our reliability of PowerApps as update is still going on.

I have the feedback from @desai_darshan about the plan to expand >500 for PA Community Plan, and meantime, in the Tutorials, Excel is on "coming soon" plan for quite sometime.

Until then, I will have to temporarily resort to other programs with less data consumption.

Appreciate Mr. Dang's sharing.

TQ

[mr-dang]

The percentage of discussions about delegation that I had calculated is a conservative estimate. It is likely higher if I included more terms in my advanced search. The string "500" alone brings up 178 discussions, and the addition of "delegate" and "delegation" brings it to 228. "500" is a reliable search term because it only means one thing in the context of PowerApps.

The problem with the 500 record limitation is that users discover it unwillingly--they don't know about it until it affects them. One moment they're having a great time building their app and seeing the capability of PA; the next moment they realize they're not seeing all of their data. It hits hard--I imagine users quit. You can sense the despair in some forum posts about the topic.

I remember that I was not the one who discovered that something was wrong with my apps. My students had repeatedly reported to me that all of their progress was erased after every session. I halted all use of PowerApps for a few weeks so I could figure out what had happened. We didn't need to use the apps so it didin't hurt our productivity to put it on hold. But the targeted business user demographic, who relies on PowerApps for day to day operations, can encounter an expensive problem when they discover the 500 record limitation and their app ceases to function for the first time.

The 500 record limitation is not something that should be stumbled upon. Incoming developers should know about it up front. But if they know about it up front, they may not select PA as their platform for building apps. It's a catch 22, but I think transparency is always better. Where is delegation on the roadmap? What is its priority right now?

Note: I was incorrect in suggesting that there's a new discussion about delegation started each day as it's not supported by stats--that was anecdotal on my part. But if you want to look into it, perhaps calculate the frequency of how often a topic about delegation lingers in the first 10 discussions.

Below are some discussions about the topic from the past 12 hours:

Filter function become non delegable when using a lookup field

CDS Delegation not working on date field types

Delegation not working with Filter on Sharepoint List

[v-micsh-msft]

Hi @mr-dang and others,

Thanks for the great feedback.

I saw your another posts regarding the Bug Fix comments request also, I will send feedback from my side for those.

Appreciate your patience.

Regards,

Michael

[mr-dang]

@v-micsh-msft, Thank you for passing on the word.

If we can collect the entire contents of a datasource, we can operate on the temporary collection instead. This would allow for all aggregate functions and other non-delegable functions to work on the temporary collection. From time to time, we can reload the datasource to have the most updated data in the temporary collection.

Could you create a function to only pre-load and reload entity data to a temporary collection? It would do the same as this, but it could pull in everything:

Refresh(datasource);
ClearCollect(temp_datasource,datasource)

It would only import all records or none; so if you wanted to filter, you would have to do it after the temporary collection has been created.

Is that doable as a work around until all functions are fully delegable?

@GregLi @jonoluk

Brian

[mr-dang]

The latest blog post, titled "PowerApps Newest Release Hits Home with Top Requests," had a title that put the topic of delegation and the 500 record limit into a new perspective for me.

https://powerapps.microsoft.com/en-us/blog/powerapps-newest-release-hits-home-with-toprequests/

Delegation is not a top request from a statistical point of view on the forums. There's not too many discussions posted about delegation or the 500 record limit on the Ideas forum, and few thumbs up for each topic. So fixing the 500 record limitation is not in the "top requests." This is likely because nobody sees it as a new idea to implement, but rather a limitation to lift. We assume the PA team is already working on it, so it's not worth suggesting fixing it as an "idea." Resolving the 500 record limit is not something that should have to be an Idea though. Every app is inevitably going to hit a point where it receives too many records. 

I want fixing the 500 record limit to be a top request though, so I have now posted an idea about temporary solutions that can be put into effect while we await all functions to be delegable:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Fix-500-Record-Limitation/idi-p/38490#M2170

Can you throw us a bone with one of the following?:

• rationale: Why 500? Is this a common limitation for all companies? Can you give the computer science behind it? I can get behind it more if it's something that every app creator software deals with.
• progress: How are things going?
• timeline: If not a date on when all functions are delegable, then when can we expect a milestone along the way?
• yes/no: Will you implement a temporary solution until all functions are delegable?

[mr-dang]

@Meneghino

Wait a second, is this Idea about delegation?:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/PowerApps-must-also-see-SQL-Azure-Queries-not-on...

It's the second highest voted idea after offline access--and that's already been implemented. It also has a whopping 66 comments, which is relatively high.

[Meneghino]

Hi @mr-dang

Yes, the idea is indirectly about delegation.  Basically, if we can have access to views of SQL server, and particualrly parametrised views, then there is less need to extend delegation since you can almost always construct the view you need with less than 500 lines.

As an example, currently Sum is not delegated, so if you have 15,000 transactions for various products you cannot get the total by product (unless you are sure that there are <=500 transactions per product).  However, you can do a GROUP BY query in SQL server to create a view of the total by product.  Then you could just get the data from this query if queries were visible.

I am not sure I have expressed myself well, so please let me know if anything needs clarification.

[mr-dang · Accepted solution]

In a recent blog post, a video was shared where Greg described delegation and their odata connector:

https://youtu.be/PuePMMuj5ps?t=39m29s

It's good information--and it was announced that aggregation for SQL is about to come out, but not necessarily this month.

[Meneghino]

Hi @mr-dang, thanks for the update.  It gives me hope that something is going to happen soon in this area and that we will see progress over time.