cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Community Champion
Community Champion

Rollback delegation

Speeding up PowerApps is important, and that's what the PA team had in mind when they built in delegation.

 

However, the implementation of delegating is incomplete. For instance, when you use Sum, Average, or Sort with Filter, you'll only be accessing the first 500 records of the table. An app that relies on Sum(Filter()) will be inaccurate.

 

I am recommending that delegation is rolled back until all functions can work with Filter. If that is not possible, I am looking for an alternative way to Sum a column of a filtered table.

Microsoft Employee
@8bitclassroom
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Power Apps
Power Apps

Re: Rollback delegation

Thanks for your feedback.   Our current situation is clearly not where we want to be and the good news is that we won't be here long.

 

As we approached the Preview release, we had a choice to make: rollback the delegation or push ahead and make delegation work.  This was a hottly debated topic and we ultimately decided to push ahead as a top priority.  Rapid progress has been made since then and we'll start seeing better delegation support released over the next month.

 

Our first priority is to unblock the apps that we generate when, for example, you create an app from a SharePoint list.  These apps use Sort and Filter so that is the priority.  Since we don't generate apps that use aggregation (Sum, Average, etc), this will take a little longer to come out, but it is on the radar.

 

The only workaround I can offer is to Collect the result of a Filter (that could be delegated) into a local collection and Sum or Average over that.  Depending on the table size, it could be very slow to pull all the data into the collection.

 

 

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Rollback delegation

Ok, here's a temporary solution if anyone needs it. It's conditional: this only works if

  • you are okay with manually moving data
  • you are okay with removing and adding a datasource, then re-saving your app
  • you need your app to work and will do anything to make it work
  • you already use two tables that house the same data

Create two tables

Table1: this is where you will Patch or SubmitForm your live data

Table2: this is your archive of static data; it will have the same columns as Table1

 

Add Table1 from your online writeable datasource (Dropbox, OneDrive, GoogleDrive).

Add Table2 as static data

 

  1. When Table1 gets to 500 rows, manually copy+paste all of its data to Table2 using Excel.
  2. Remove all records from Table1.
  3. Remove Table2 from PA, then re-add it.
  4. Re-save the app.

This works because static data is fully loaded (500+ records). You will need to make all your formulas check both Table1 and Table2. This is not a problem if you already split your data into two tables.

Microsoft Employee
@8bitclassroom

View solution in original post

8 REPLIES 8
Highlighted
Power Apps
Power Apps

Re: Rollback delegation

Thanks for your feedback.   Our current situation is clearly not where we want to be and the good news is that we won't be here long.

 

As we approached the Preview release, we had a choice to make: rollback the delegation or push ahead and make delegation work.  This was a hottly debated topic and we ultimately decided to push ahead as a top priority.  Rapid progress has been made since then and we'll start seeing better delegation support released over the next month.

 

Our first priority is to unblock the apps that we generate when, for example, you create an app from a SharePoint list.  These apps use Sort and Filter so that is the priority.  Since we don't generate apps that use aggregation (Sum, Average, etc), this will take a little longer to come out, but it is on the radar.

 

The only workaround I can offer is to Collect the result of a Filter (that could be delegated) into a local collection and Sum or Average over that.  Depending on the table size, it could be very slow to pull all the data into the collection.

 

 

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Rollback delegation

Thank you for the workaround, Greg. It is taking time to rework formulas, but that is working for me so far.

Microsoft Employee
@8bitclassroom
Highlighted
Community Champion
Community Champion

Re: Rollback delegation

Wait a sec. I'm still at 500. 

 

If I set Button1.OnSelect to:

Collect(temp, Filter(datasource,expressions))

 

and

 

TextBox1 to:

Sum(temp,column)

 

Then CountRows(Filter(datasource,expressions)) still results in 500.

 

Can you clarify how I could use Filter to draw from all records? I'm using Excel.

Microsoft Employee
@8bitclassroom
Highlighted
Community Champion
Community Champion

Re: Rollback delegation

Ok, here's a temporary solution if anyone needs it. It's conditional: this only works if

  • you are okay with manually moving data
  • you are okay with removing and adding a datasource, then re-saving your app
  • you need your app to work and will do anything to make it work
  • you already use two tables that house the same data

Create two tables

Table1: this is where you will Patch or SubmitForm your live data

Table2: this is your archive of static data; it will have the same columns as Table1

 

Add Table1 from your online writeable datasource (Dropbox, OneDrive, GoogleDrive).

Add Table2 as static data

 

  1. When Table1 gets to 500 rows, manually copy+paste all of its data to Table2 using Excel.
  2. Remove all records from Table1.
  3. Remove Table2 from PA, then re-add it.
  4. Re-save the app.

This works because static data is fully loaded (500+ records). You will need to make all your formulas check both Table1 and Table2. This is not a problem if you already split your data into two tables.

Microsoft Employee
@8bitclassroom

View solution in original post

Highlighted
Power Participant
Power Participant

Re: Rollback delegation

Hi, I have tried delegation by simply doing a CountRows(Filter(MySource, MyColumn = FilterValue)), where FilterValue is a number or a string depending on type of MyColumn. This has not worked for Access Web App (AWA) tables or SharePoint lists or Excel tables. I only get a subset of the 500 records (or 256 in case of AWA) returned without the filter.

Am I doing something wrong?

If not, for which sources does delegation work today?

Highlighted
Community Champion
Community Champion

Re: Rollback delegation

Yes, I previously thought it worked out for me, but that was because I clicked my button for Collect(temp, datasource) twice and got 1000. 

 

From Greg's solution, I found that it is not possible to Collect an entire datasource to a temp collection. You will only get 500.

Another problem is that even if you set Gallery.Items to Filter(datasource, column=criteria), it will not delegate as described.

 

Will the next update resolve the delegation issues?

 

As an aside, does CountRows qualify as an "aggregate" function?

Microsoft Employee
@8bitclassroom
Highlighted
Power Participant
Power Participant

Re: Rollback delegation

Hello, I still cannot get delegation to work, even though the blog on the latest update says it is now working.

I have tried with Access Web App tables, SharePoint lists, and Excel on OneDrive.

Has delegation worked for anyone?  And if so, with what type of data source?

Highlighted
Community Champion
Community Champion

Re: Rollback delegation

Echo: There is no change in the number of records I can show in version 2.0.430. It still only operates on the first 500 records for:

  • Gallery.Items = datasource
  • Gallery.Items = Filter(datasource)
  • Button.OnSelect = ClearCollect(collection, datasource)
  • Button.OnSelect = ClearCollect(collection, Filter(datasource))

Context: I ran my tests using Excel files on OneDrive and Dropbox with these results.

 

My understanding is:

As the user scrolls, additional data will be brought down from the data source.

 

I don't see additional data being brought down as I scroll though.

 

If complete delegation of a formula is not possible, the authoring environment will flag 
the Filter or Sort formula as a warning.

I also do not see a warning flag for any instance of Filter or Sort, considering that things did not work out.

 

Requested: additional documentation on how to successfully achieve delegation. Please update the entry for Filter and LookUp:

https://powerapps.microsoft.com/en-us/tutorials/function-filter-lookup/

 

 

Microsoft Employee
@8bitclassroom

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,477)