cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luka84
Helper V
Helper V

How to Overcome Delegation with SUM function

Good day @RezaDorrani ,

I've seen your Youtube videos with regards to overcoming delegation - very useful thank you especially with the advice on setting indexes for SharePoint to allow lists larger than 5000 items !!

I couldn't however find a solution to my problem, perhaps you could help?

I am trying to find the sum of a sharepoint column in another list on my Powerapps form, and run into the delegation issue.

My formula below for reference:

Sum(Filter('01. CASH APPROVAL', CASHRECEIVEREMAIL = EMPEMAILLABEL.Text, REQUESTSTATUS = "05. PAID - PENDING PROOF" || REQUESTSTATUS = "07. SUBMISSION OVERDUE" || REQUESTSTATUS = "09. FLOAT OPEN" ), AMOUNTREQUESTED)

Thanks in advance

19 REPLIES 19

@v-xida-msft 

 

Thank you 

 

 

 

 

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.
SpeeD1972
Frequent Visitor

Hi.

 

When i enterd your formula for collections (onStart), i still receive an delegation warning telling me that:

"The highlighted part of the formula might not work correctly with column ".ID" with large data sets.

 

The formulas is this:

 

Concurrent(
ClearCollect(col1, ShowColumns( Filter(Tbl_Costs_Global, ID >= 1 && ID <= 2000),"Title","ID_Global")),
ClearCollect(col2, ShowColumns( Filter(Tbl_Costs_Global, ID >= 2001 && ID <= 3000),"Title","ID_Global")))
;
ClearCollect(Colection_Costs_Global, col1, col2)

 

thanks a lot

JM

 

Hi @SpeeD1972

The way I fixed this issue was by doing the following:

  • Make sure you set the Data Row Limit of your PowerApps to 2000 
  • Sharepoint:
    • Create another column called "IDCounter" with type Number
  • Power Automate:
    • Create a new flow with type "When an Item is created or modified"
    • Insert an action of type "Update Item"
      • Select your SharePoint site and List
      • On the field "IDCounter", click and select the field "ID"

Now, whenever a new item is created, the IDCounter will be updated with the same value of ID but in Number format.

Refresh your list data source in PowerApps and in your Concurrent collection code, you need to replace the word "ID" with "IDCounter"

 

Concurrent(
ClearCollect(col1, ShowColumns( Filter(Tbl_Costs_Global, IDCounter >= 1 && IDCounter <= 2000),"Title","ID_Global")),
ClearCollect(col2, ShowColumns( Filter(Tbl_Costs_Global, IDCounter >= 2001 && IDCounter <= 4000),"Title","ID_Global")))
;
ClearCollect(Colection_Costs_Global, col1, col2)

 

 

 Note: For the existing items - you can create another instant flow in power automate to go through your list and update all the existing items' IDCounter with ID

 

I hope the above helps

Regards

Luka 

SpeeD1972
Frequent Visitor

Hi Luca

 

I will try this right away.

thanks a  lot for the swift reply!!

 

PS: this means that the delegation is triggered only by the id filed in a SP list?

 

cheers

JM

I meant  ID Field... 😄

Hi @SpeeD1972 ,

No worries at all, I hapenned to be on the community portal when I got the ping 😃

Field wise, delegation is applicable on ID in this case because it's a default item in SharePoint and is not really a number value. For me, the IDCounter solved the issue but not sure if there are other solutions out there.

Having said that, there are many other delegation warnings when working with large data especially in SharePoint such as using Sum and Count, also when Filtering with Text vs filtering with Numbers.

Here's a reference to understand the delegations rules in PowerApps for multiple sources including SharePoint:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

 

All the best 😃

SpeeD1972
Frequent Visitor

Hi Luca

 

Tried your approach but im stuck atvthe beggining.

I can managed to put the ID into a new field "ID_Copia" that is formated as number...

 

what am i doing wrong???

 

Cheers

JM

Hi JM, where exactly are you stuck - at which step?

Give me some more details and I'll do my best to help out 😃

SpeeD1972
Frequent Visitor

Hi Luca

 

Forget about it! It´s done with no Delegation warning!! 😄

thanks a lot!!

 

Cheers

JM

Most welcome JM - all the best with your development 😃

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,816)