Unanswered

SUM Delegation SharePoint List

(0) Share

Report

Posted on by Borme

Hi

I have 3 SharePoint lists:
‘Products’
‘Orders’
‘Deliveries’

I want to display the ‘Products’ table in a vertical gallery and display the stock level for each product.

I am using the following formula:

Label_StockLevel.Text =
Sum( Filter( Orders, ProductID= ThisItem.ID), Quantity Ordered) - Sum( Filter( Deliveries, ProductID= ThisItem.ID), Quantity Delivered)

I can see why there is a delegation problem- for each item in the product table, every row of the Orders and Deliveries table needs to be checked to see if the ProductID matches.

Is there a way around this? Perhaps aggregating each table using GroupBy?

The workaround for bringing more than 2000 rows into the app using collections is not a viable option due to the performance issues that it would cause.

Thanks and please let me know if you require more information from me.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Hi @Borme

There is a sample database that can be downloaded that looks to me to be exactly on point to your question. Please check https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/northwind-orders-canvas-overview . I learned ALOT by going through the exercise of downloading it and building the app.

Was this reply helpful? Yes No
Borme 4 on at

Like (0)

Report

Thanks for the reply

This is exactly what I am looking for however this example uses Common Data Service whereas I am using SharePoint lists.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Hi @Borme

Unfortunately, Sum() is not delegatable with the SharePoint connector. The only workaround that I am familiar with is a sequential collection as suggested here. https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-Overcome-Delegation-with-SUM-function/m-p/365272 . As far as I know Sql is the only connector that delegates aggregate functions to the server. https://powerapps.microsoft.com/en-us/blog/latest-powerapps-update-adds-delegation-for-sum-average-min-and-max-functions-on-sql-server/ . The entities in Common Data Service also allow for "roll-up" columns. https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/define-rollup-fields . You can vote for it in the Ideas part of the forum but this was requested by @Meneghino in 2017 and it is still listed as planned. https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Extend-delegation-to-Sum-and-CountRows/idi-p/23193

Was this reply helpful? Yes No
RezaDorrani 12,143 on at

Like (0)

Report

Hi @Borme,

I came across a useful API in SharePoint to get aggregate function values like Sum.

Check it out:

https://youtu.be/qsIn0mytC_8

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

Was this reply helpful? Yes No