cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anthony_Dob
Responsive Resident
Responsive Resident

Sum delegation on lookup table column

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

1 ACCEPTED SOLUTION

Accepted Solutions
RusselThomas
Microsoft
Microsoft

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

View solution in original post

5 REPLIES 5
RusselThomas
Microsoft
Microsoft

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

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

RusselThomas
Microsoft
Microsoft

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 

@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

LukeShive
Helper I
Helper I

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.

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (3,033)