Adding a column to a gallery which is the sum of a filtered list

(0) Share

Report

Posted on by yellowjacketjc

Hi All,

I have tried numerous times to get this to work and am out of ideas. I have a contact list with a column of "Name" and "OvertimeEligible". I also have an Overtime Tracking list with columns "Employee Name" and "Hours Worked".

I would like to create a gallery which displays all the OvertimeEligible employees and for each employee, looks up the sum of "Hours Worked" in the Overtime Tracking list. I have been using variations of the following formula in the Gallery "Items" with no success:

AddColumns(Filter('Contact List',OvertimeEligible=true),"SumOfHoursWorked",Sum(Filter('Overtime Tracking','Employee Name' = Name),'Hours Worked'))

I believe this may have something to do with record scope. When I add a label and use just the sum portion of the above formula, the label displays the proper values. When it is done inside a gallery, it displays a blank value (no error, just no value).

Thank you!

Categories:

Building Power Apps

All responses (1)

Answers (0)

LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @yellowjacketjc,

What might be worth trying it explicitly defining the outer loop record scope. Could you give the following code a try:

AddColumns( Filter( 'Contact List', OvertimeEligible //Boolean fields will return true if defined as true, no need for '= true' ) As Main, "SumOfHoursWorked", Sum( Filter( 'Overtime Tracking', 'Employee Name' = Main.Name ), 'Hours Worked' ) )

When nesting Looping formulas or queries (ForAll, Filter, Addcolumns...) I always explicitly define one of the record scopes. Generally I name the outer scope Main and the inner scope remains implicit.

If this solves your question, would you be so kind as to accept it as a solution.

Thanks!