SUM Rows In A SharePoint List With No Delegation Warnings

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)

Expense Totals (Status is type single-line text, Total is number)

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)

LookUp('Expense Totals', Status = "Pending", Total)

LookUp('Expense Totals', Status = "Approved", Total)

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

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()});

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);

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});

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)});

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);

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}
    )
);

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.

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.