Getting Around Delegation Error from summing a column from SharePoint List

[Anonymous]

Hi all,

I am working on an application, in which I want to track the costs associated with a project. These gets stored in the PROJECT COSTS list (SharePoint List).

The User of the application will manually add consultants, which are pulled from the EMPLOYEE LIST through a lookup column, which also takes the given employees "Daily Price" and "Daily Cost". These daily cost/price will be multiplied by the total days a given employee works. 

As a project can span over many weeks and we need to track the days worked in a week, I have added ANOTHER list called PROJECT COSTS CONSULTANTS WEEKLY WORK. I put the table INSIDE the editform of the PROJECT COSTS and filter the table to only show the consultants where the ProjectID matches the given PROJECT. Here is a picture of how it looks:

As you can see, I add a consultant and the it drags in the "Daily Cost" and "Daily Price". if I press the PEN ICON I can edit the weeks numbers and it multiplies by the total work days to get total costs.

MY PROBLEM IS THIS:

In order to get all the costs of the consultants connected to the project I am currently editing in the PROJECT COSTS editform, I am using the ClearCollect():

ClearCollect(colEmployeeCosts,

{ID: 1, 
Title: "Employee Costs", 
Amount: Sum(Filter('Project Costs Consultants Weekly Work', ProjectID = IDDataCard.Text),'Employee Total Costs')})

But this is not delegateable with SharePoint.

My question is therefore: Is there any way I can take the total costs of all employees connected to the project and add them up without getting a delegation error?

[mdevaney]

@Anonymous 

Are you wanting to calculate the total of all costs (from the the Project Costs lists) for the Project (Project list)?

Is this for the lifetime of the project or is it only for certain dates?

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Pstork1]

As long as the inner filter, the records for an individual project, will be less than 500 records you can ignore the delegation warning on the sum().  You can also change the data row limit to 2,000 if the number of records for an individual project are more than 500.  Using a delegable inner function to buffer an outer non-delegable filter is the only real work around.

[Anonymous]

Hi@mdevaney !

So each project spans over a predefined number of weeks. All of these weeks we want to track how many days each of the consultants worked, so that we can calculate the total costs of that project.

So in my above example there are 2 consultants connected to the project and the worked x days in week 1, x days in week 2, etc.

Once the project is finished, this project is finished and will not be edited again. So I guess this means the answer to your second question is no?

A given consultant can be connected to more than one project but I ONLY want to see that consultants costs related to the given project I am currently editing.

[Anonymous]

@Pstork1 

Thank you for you comment.

Yes exactly, this I am aware of. But I am trying to figure out whether there is a way to work around this problem? For each project there might be up to 30 consultants connected and if we do not go bankrupt, this list will therefore exceed 2000 within a foreseeable future, so I think we need to be able to delegate above 2000.

Would you know of any ways to sum this up in an alternative way?

[Pstork1]

If you will exceed 2,000 records there are two possibilities.  One is to pull the records for each consultant into a local collection and then sum the collection.  But that will be time consuming. 

Another approach that I think would be better would be to build a Power Automate flow to do the sum using multiple loops. Then return the total to Power Apps.

[Anonymous]

@Pstork1 

Now we are talking! 🙂

This idea sounds promising.

So, my Power Automate knowledge is limited (but we are here to learn!). How I understand it I would make an action in which a button will be pressed and it sums all the rows that are equal to the ProjectID and then returns it a bit like what I am doing with the ClearCollect()?

[Pstork1]

That's exactly what I'm suggesting.  You'll still have to deal with breaking things down to avoid delegation.  But Power Automate is procedural while Power Apps is declarative.  So its much easier to do a loop.  Here's a psuedo code list of what I suggest.

Call the flow and wait for the total using code like this in PowerApps.

Set(Total, flowname.run(projectIDToSum))

 Then the flow would look something like this

• Power Apps Trigger
• Initialize variable to hold total
• Get the list of user's to process
• Apply to Each Loop based on Users to process
  • Get Items for Project filtered by a particular user
  • Apply to Each Loop based on Get Items
    • Add item to Total Variable
• Once all loops are complete respond to Power Apps with Total

[mdevaney]

@Anonymous 
My preferred method is to create a 'Total Costs' column in the Projects List.

Then each time a new cost is added to the Project Costs List also PATCH the total in the Projects List like this.

Set(currentProject, LookUp('Projects List', ID = Value(DataCardValue_ProjectID.Text)));
Patch(
   'Projects List',
   currentProject,
   {'Total Costs': currentProject.'Total Costs' + Value(DataCardValue_ItemCost.Text)} 
)

In addition to being a workaround for delegation performance is improved when viewing the data entered because no calculations are taking place.

If you need more info let me know.  You'll have to decide whether Flow or this method works best for you 🙂 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

@mdevaney 

Allow me to understand how you would do this. As you know I am not most experienced with PowerApps, but it is getting better 🙂

When you say "Project Costs", do you mean the list where the employee costs (the consultant costs as can be seen in my screenshot) or do you mean the actual Project Costs SharePoint List, where we track a OVERARCHING project. In that case I already have a column like that (I named it "Actual Costs"), it currently contains: Sum(colEmployeeCosts.Amount)