Calculate Totals for Rows in a SharePoint List Without Triggering Delegation Warnings!
Overview:
When working with Power Apps, one may encounter the SharePoint Delegation Warning. This article provides a practical workaround for this common issue, ensuring accurate data handling even with large SharePoint lists. Using the SUM function in Power Apps connected to SharePoint can sometimes yield delegation warnings, especially when the list surpasses 2,000 rows. Let’s dive into the Power Apps SharePoint Delegation Warning Workaround.
Expense Tracker for Travel
In this example, we will construct an Expense Tracker application designed to handle employee reimbursement requests, similar to the illustration below. Expense items will be presented within a gallery, accompanied by their present status. Above the gallery, you will find the total sum of expenses categorized by status: Open, Pending, and Approved.
We will connect two SharePoint lists with the app
Expense Tracker (Status and Issue are of the single-line text type, Amount is a numerical field)
Expense Totals (Status is a single-line text field, Total is a numerical field)
The sum of expenses for each status can be computed using the SUM function. Nevertheless, it’s important to note that when the number of rows surpasses 2,000 for any status, the calculated value may become unreliable. We will create variable on the onvisible property of the screen.
// Calculate the sum of 'Amount' for records with 'Status' equal to "Open"
Set(opentotal_var, Sum(Filter('Travel Expenses', Status = "Open"), Amount))
// Calculate the sum of 'Amount' for records with 'Status' equal to "Pending"
Set(Pendingtotal_var, Sum(Filter('Travel Expenses', Status = "Pending"), Amount))
// Calculate the sum of 'Amount' for records with 'Status' equal to "Approved"
Set(Approvedtotal_var, Sum(Filter('Travel Expenses', Status = "Approved"), Amount))
And on the text property of the text labels we will use the name of the variables respectively; like for Open ‘’Opentotal_var”, for Pending “Pendingtotal_Var” and for Approved “Approvedtotal_var”
Add a new Expenses and increase the total
To update the “Open” total when an employee enters a new travel expense, create an Edit Form on a screen called ‘Data Entry Screen’ using the ‘Travel Expenses’ table as the data source in PowerApps. When an expense is submitted through the form, use a formula to calculate the updated “Open” total.
In the form, replace the text input of the data card for the “Status” field with a dropdown. Set the dropdown’s Items property to the following array: [“Open”, “Pending”, “Approved”]. This change allows users to select the status from the provided options when entering a new travel expense.
You may encounter an error on the form, indicating that the name is not valid, and the identifier is not recognized. This occurs because we haven’t created a variable named CurrentRecord_var yet.
Navigate to the summary screen and incorporate the following code into the OnSelect property of the ‘+ New Ticket’ button. This action will switch the form to a new mode and provide it with a blank record variable, resolving the error.
Give the following code to the form
DeafultMode: FormMode.New
Item: CurrentRecord_var
On the onselect fof the ‘new Ticket’ Button write this code:
NewForm(Form1);
Navigate(DataEntryScreen,ScreenTransition.None,{CurrentRecord_var:Blank()})
On the ‘DataEntryScreen’ on the Button write this code
//Submit The form when the button is clicked
SubmitForm(Form1)
Now write this code on the onsuccess Property of the form
// Change the form to View Mode.
ViewForm(Form1);
// Store the record of the last Submit of the form in a Variable
UpdateContext({CurrentRecord_var:Form1.LastSubmit});
// Store the total record for ‘Open’ Status in a variable
UpdateContext({TotalRecord_var: LookUp(' Travel Expenses’,Status=CurrentRecord_var.Status)});
// save the total record in the sharepoint for open status
Patch(' Travel Expenses ',TotalRecord_var,{Total:TotalRecord_var.Total+ CurrentRecord_var.Amount})
That’s all the necessary setup for now. Please enter your expense details and click the “Submit” button on the form.
CHANGE THE DETAILS OF THE EXPENSES
To trigger a recalculation of totals when the status of an expense item changes, place the following code in the OnSelect property of the edit icon within the gallery. Clicking the icon will now direct the user to the ‘Data Entry Screen.’
This code ensures that when the user clicks the edit icon, they are taken to the ‘Data Entry Screen’ for editing while also recalculating the totals based on any status changes.
EditForm(Form1);
Navigate(DataEntryScreen,ScreenTransition.None,{CurrentRecord_var:LookUp('Expense Totals',ID=Gallery2_3.Selected.ID)})
On the ‘Data Entry Screen,’ update the status from “Open” to “Pending.”
Replace the code on the onselect property of the button with this code
// Check if the form mode is 'Edit'
If(FormMode = FormMode.Edit,
// If in edit mode, update the 'EditRecord_var' variable with the record in 'Expense Totals'
// where the 'Status' matches the 'CurrentRecord_var.Status'
UpdateContext({EditRecord_var: LookUp('Expense Totals', Status = CurrentRecord_var.Status}))
);
// Submit the form (Form1)
SubmitForm(Form1)
Reference: If and Switch functions in Power Apps
Reference: UpdateContext function in Power Apps
And also replace the code in the onsuccess property of the form with this code
Certainly, I’ve added comments to explain the provided PowerApps code:
// View the 'Form1' form
ViewForm(Form1);
// Update the 'CurrentRecord_var' variable with the last submitted record
UpdateContext({CurrentRecord_var: Form1.LastSubmit});
// Find the corresponding record in 'Expense Totals' based on the 'Status' of the current record
UpdateContext({TotalRecord_var: LookUp('Expense Totals', Status = CurrentRecord_var.Status)});
// Update the 'Total' field in 'Expense Totals' by adding the 'Amount' of the current record
Patch('Expense Totals', TotalRecord_var, {Total: TotalRecord_var.Total + CurrentRecord_var.Amount});
// Check if the form mode is 'Edit'
If(FormMode = FormMode.Edit,
// If in edit mode, update 'Expense Totals' by subtracting the 'Amount' of the current record
Patch('Expense Totals', EditRecord_var, {Total: EditRecord_var.Total - CurrentRecord_var.Amount})
);
// Navigate back to the 'Summary Screen'
Navigate('Summary Screen');
// Reset 'Form1' to its initial state
ResetForm(Form1);
Reference: Patch function in Power Apps
This code performs various actions, including viewing the form, updating variables, patching data into ‘Expense Totals’ based on the ‘Status,’ and navigating to the ‘Summary Screen’ while resetting ‘Form1’ to its initial state.
We have completed the setup. Please click the “Submit” button to test it out.
Conclusion:
Mastering the Power Apps SharePoint Delegation Warning Workaround is crucial for those regularly working with extensive SharePoint lists.
Successfully sidestepping the Power Apps SharePoint delegation warning ensures the accuracy of our SUM calculations, crucial for large data sets. By leveraging the outlined approach with the Expense Tracker application, developers can confidently manage and sum extensive SharePoint lists without being hindered by delegation warnings.
