SUM Rows In A SharePoint List With No Delegation Warnings

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/sum-rows-in-a-sharepoint-list-with-no-delegation-warnings/. Author: Matthew Devaney.

SUM Rows In A SharePoint List With No Delegation Warnings Posted by - Matthew Devaney on - July 20, 2020 8 Comments

Power Apps SUM function will cause a delegation warning when using SharePoint as the datasource. This means the SUM could be incorrectly calculated when the SharePoint list exceeds 2,000 rows. I will show you a workaround to avoid the warning and ensure the SUM is always correct. Travel Expense App

In this example we will build a Travel Expense app to manage employee’s requests for repayment as shown in the image below. Expense items are displayed in a gallery along with their current status. The SUM of all expenses for each status is shown above the gallery: Open, Pending and Approved.

There are two SharePoint lists connected to this app.

Travel Expenses (Status and Problem are type single-line text, Amount is number) Status Problem Amount Open Flight – United Airlines 1200 Open Hotel – Country Inn 450 Pending Computer – MicroCenter 900 Pending Flight – United Airlines 725 Pending Hotel – Mariott 650 Approved Car Rental – Enterprise 700

Expense Totals (Status is type single-line text, Total is number) Status Total Open 1650 Pending 2325 Approved 700

The sum of expenses for each status could be calculated using the SUM function. However, when the number of rows exceeds 2,000 rows for any status the value will be inaccurate.

Instead of SUM put a LOOKUP function in each label’s Text property to avoid a delegation warning. Now the total is simply being retrieved from a SharePoint list rather than being calculated.

LookUp( 'Expense Totals' , Status = "Open" , Total) Code language: JavaScript ( javascript )

LookUp( 'Expense Totals' , Status = "Pending" , Total) Code language: JavaScript ( javascript )

LookUp( 'Expense Totals' , Status = "Approved" , Total) Code language: JavaScript ( javascript )

Add A New Expense Item And Increase Totals

When an employee enters a new travel expense the “Open” total should be updated to display the new value. On a new screen named ‘Data Entry Screen’, create an Edit Form called frm_TravelExpense using the Travel Expenses table as the datasource.

Give the Edit Form the following properties. DefaultMode : FormMode.New Item : locCurrentRecord Code language: HTTP ( http )

You will get an error on the form saying ‘Name isn’t valid. Identifier isn’t recognized.’ This is because we have not yet created any variable called locCurrentRecord.

Go to the summary screen and add this code to the OnSelect property of the ‘+ New Ticket’ button. It will change the form to new mode and pass a blank record variable into the form. Now there will no longer be any error. NewForm(frm_TravelExpense);

//Navigate('Data Entry Screen', None, {locCurrentRecord: Blank()}); Code language: JavaScript ( javascript )

On the ‘Data Entry Screen’ use this code in the OnSelect property of the Submit button

// submit the form when button is clicked SubmitForm(frm_TravelExpense); Code language: JavaScript ( javascript )

Then write this code in the OnSuccess property of the form. // change the form to view mode ViewForm(frm_TravelExpense);

// store the last submitted record in a variable

UpdateContext({ locCurrentRecord : frm_TravelExpense.LastSubmit});

// store the current total for 'open' status in a variable

UpdateContext({ locTotalAddRecord : LookUp( 'Expense Totals' , Status=locCurrentRecord.Status)}); // increase the total for open status

Patch( 'Expense Totals' , locTotalAddRecord, { Total : locTotalAddRecord.Total + locCurrentRecord.Amount}); Code language: JavaScript ( javascript )

That’s all the code needed for now. Fill-in an expense and click the submit button for the form.

When we return to the ‘Summary Screen’ we now see the total for Open has increased to reflect the new expense item.

Change Expense Item Status And Update Totals

A newly entered travel expense starts off with a status of Open and changes to Pending and Approved as it advances through the repayment process. When the status of an expense item changes the totals should be recalculated as well.

Put this code in the OnSelect property of the edit

icon in the gallery. Clicking the icon will now take the user to the ‘Data Entry Screen’.

EditForm(frm_TravelExpense); Navigate( 'Data Entry Screen' , None, { locCurrentRecord : LookUp( 'Travel Expenses' , ID=Gallery1.Selected.ID)}); Code language: JavaScript ( javascript )

On the ‘Data Entry Screen’ change the status from Open to Pending .

Replace any code in the OnSelect property of the Submit button with this instead.

// store the current record in a variable prior to submission

If( frm_TravelExpense.Mode=FormMode.Edit, UpdateContext({ locTotalMinusRecord : LookUp( 'Expense Totals' , Status=locCurrentRecord.Status)} ) ); // submit the form SubmitForm(frm_TravelExpense); Code language: JavaScript ( javascript )

Also replace any code in the OnSuccess property of the form with this code. // change the form to view mode ViewForm(frm_TravelExpense);

// store the last submitted record in a variable

UpdateContext({ locCurrentRecord : frm_TravelExpense.LastSubmit});

// store the current total for 'open' status in a variable

UpdateContext({ locTotalAddRecord : LookUp( 'Expense Totals' , Status=locCurrentRecord.Status)});

// increase the total for pending status

Patch( 'Expense Totals' , locTotalAddRecord, { Total : locTotalAddRecord.Total + locCurrentRecord.Amount}); // decrease the total for open status

If( frm_TravelExpense.Mode=FormMode.Edit, Patch(

'Expense Totals' , locTotalMinusRecord, { Total : locTotalMinusRecord.Total - locCurrentRecord.Amount} ) ); Code language: JavaScript ( javascript )

We’re done! Now click the Submit button to give it a try.

The total for Pending status has now increased and the total for Open status has now decreased. You can also try changing the status of the expense item from Pending to Approved and it will work with no additional code needed. Final Thoughts

Storing totals in a SharePoint list and retrieving them with LOOKUP offers better performance than SUM because no calculations are taking place. The trade-off is you must plan-ahead and think of what totals will be needed before creating your app.

COUNTROWS is another formula that cannot be delegated in SharePoint. With a few small changes to my method you could perform a count instead.

The app we built together can only record the travel expenses of a single employee because we did not include any usernames in our SharePoint lists. This is definitely possible. However, my intention was to introduce as few elements as possible into the app while focusing on teaching the delegation technique. Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up Questions?

If you have any questions or feedback about SUM The Rows In A SharePoint List With No Delegation warnings please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion. COUNTROWS function Delegation SharePoint SUM function Matthew Devaney M365 Copilot Power Apps

How To Create Copilot Custom UI Widgets In Power Apps