Sum delegation on lookup table column

(0) Share

Report

Posted on by AnthonyD

559

Hi Experts,

I am looking for a way to Sum a lookup table column inside a gallery.

Relation: 1:N - Company : Cases

Gallery Datasource: Company

Label in Gallery to show : Sum(ThisItem.'Cases','Amount Owed')

This ends up having delegation. How can this be achieved to have a label in the gallery which sums related lookup column.

Best Regards,

Anthony

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Verified answer

RusselThomas 4,014 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anthony_Dob ,
If for example the lookup column is in SPO and it's linked to a Cases list and you've included an 'AmountOwed' numerical column in the lookup which also lives in the Cases list, (and if multiple selections are allowed), then your list should have two columns displayed;
"Cases" and "Cases: AmountOwed"

So your formula should look something like;
Sum(ThisItem.'Cases: AmountOwed', Value)
But even though you might not get a delegation warning, that doesn't mean your specific source understands the Sum() function - so I'd still test it to make sure. I haven't tested with SPO and Sum() is not listed as a delegable function in the SPO connector reference, so perhaps worth confirming.

If the above doesn't fit with your scenario, some more info on how your lookup column is configured and how selections are made by the user might help.

Kind regards,
RT

Was this reply helpful? Yes No
AnthonyD 559 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @RusselThomas ,

This works fine for me. My datasource is Dataverse. The issue I am having is that I want to be able to filter the Datasource 'Company' on this Sum value. I can do this:

ShowColumns( AddColumns( 'Company - 67ters' As F, "AmountOwed", Sum( ForAll( Filter( '67ter Cases', Company67ter.'Company - 67ter' = F.'Company - 67ter' ), 'Amount Owed' ), Value ), "AmountOfFines", CountRows( Filter( '67ter Cases', Company67ter.'Company - 67ter' = F.'Company - 67ter' ) ) ), "rr_kbonumber", "rr_fulladdress", "rr_company67terid", "AmountOfFines", "AmountOwed", "rr_language", "rr_name" )

However this takes so much time to load in and on filtering the gallery it has to recalculate the whole thing which makes it impossible to use it this way.

Then I tried using a collection but this restricts me to 2000 records which does not meet my requirements.

How would you go on with this?

Best Regards,
Anthony

Was this reply helpful? Yes No
RusselThomas 4,014 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anthony_Dob ,
Seems like a bit of catch 22. You're aggregating as one would expect - but the ForAll() company filter on each row of your source is a major handbrake - even if you wanted to group your reference table by company first and include the owing amounts in a sub-table and then iterate through that collection with the Sum() function, the crux would still be delegation on the group function - which would probably still force you to use a ForAll() company filter while grouping meaning we haven't actually solved anything...
I'm not sure you can avoid it.

A bit of a shot in the dark, but perhaps you can try create a Dataverse View with the info you need and connect to that instead? [Edit]Perhaps the view can group the Cases table by Company and aggregate Amount Owed?[/Edit]

I haven't done this before so no clue if it's viable, but the answer here would probably lie in the source data construct....

Kind regards,
RT

Was this reply helpful? Yes No
AnthonyD 559 on at

Like
a
(1)

Report
Copy link

Link copied!

@RusselThomas ,

Because Cases and Payments are updated on a fixed time each day I wrote a flow that runs each day after Case and Payments updates were made. It makes the updated calculations and adds this inside the Company table.

It is not the "real time data" result I would want but as we only update the Cases and Payments once/day it will work just fine.

Best Regards,
Anthony

Was this reply helpful? Yes No
LukeShive 66 on at

Like
a
(0)

Report
Copy link

Link copied!

It's hard to say without knowing how Your datasource is constructed, but I would agree with @RusselThomas that Your solution is laying in source data construction. Generally to have fast working application thinks like the one that You want to achieve should be processed by Dataverse in Your case. Maybe solution for You is to transform Your data with Dataflow to a point where You can referee to it directly.

Was this reply helpful? Yes No